语句优化问题

Question

tab1
id1    id2
 2      3
 4      1
 2      5
...    ...tab2
id     name
 1     aaa
 2     bbb
 3     ccc
 4     ddd
 5     eee
...    ...要求查询结果为
name1   name2
bbb     ccc
ddd     aaa
bbb     eee
...     ...以下两个语句，那个好些？
1：select (select a.name from tab2 a where a.id = tab1.id1) name1 
          (select b.name from tab2 b where b.id = tab1.id2) name2
   from tab12:select a.name name1 ,b.name name2 from tab1,tab2 a,tab2 b where a.id = tab1.id1 and b.id=tab1.id2另外，还有没有比较优化的查询方法可以达到题目所要求的目的。

Answer 1

表的顺序也能影响查询的速度
选择最有效率的表名顺序（只在基于规则的优化器中有效）
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
    例如：
      表 TAB1 16，384 条记录
      表 TAB2 1      条记录
      选择TAB2作为基础表 （最好的方法）
    select count（*） from tab1，tab2   执行时间0.96秒
      选择TAB2作为基础表 （不佳的方法）
    select count（*） from tab2，tab1   执行时间26.09秒
      如果有3个以上的表连接查询， 那就需要选择交叉表（intersection table）作为基础表， 交叉表是指那个被其他表所引用的表。

Answer 2

你所谓的交叉表在这个题目中指的的tab1吗？

Answer 3

--Oracle10g测试CREATE TABLE tab1(id1 number, id2 number);INSERT INTO tab1(id1, id2)
SELECT ROUND(dbms_random.value(1,10)), ROUND(dbms_random.value(1,10))
  FROM dual
 CONNECT BY rownum <= 1000000;CREATE TABLE tab2(id number, name varchar2(10));insert into tab2 values(1,'aaa');
insert into tab2 values(2,'bbb');
insert into tab2 values(3,'ccc');
insert into tab2 values(4,'ddd');
insert into tab2 values(5,'eee');
insert into tab2 values(6,'fff');
insert into tab2 values(7,'ggg');
insert into tab2 values(8,'hhh');
insert into tab2 values(9,'iii');
insert into tab2 values(10,'jjj');create index idx_tab1_id1 on tab1(id1) nologging;
create index idx_tab1_id2 on tab1(id2) nologging;create index idx_tab2_id on tab2(id) nologging;EXEC dbms_stats.gather_table_stats(USER,'TAB1');
EXEC dbms_stats.gather_table_stats(USER,'TAB2');set autot trace explain statselect (select a.name from tab2 a where a.id = tab1.id1) name1 ,
          (select b.name from tab2 b where b.id = tab1.id2) name2 
  from tab1 ;
select a.name name1 ,b.name name2 from tab1,tab2 a,tab2 b where a.id = tab1.id1 and b.id=tab1.id2 ;查询计划如下帖

Answer 4

set autot trace explain statselect (select a.name from tab2 a where a.id = tab1.id1) name1 ,
          (select b.name from tab2 b where b.id = tab1.id2) name2 
  from tab1 ;已用时间:  00: 00: 11.35执行计划
----------------------------------------------------------
Plan hash value: 2989540500-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1016K|  5955K|   376   (9)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB2        |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TAB2_ID |     1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| TAB2        |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN          | IDX_TAB2_ID |     1 |       |     1   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | TAB1        |  1016K|  5955K|   376   (9)| 00:00:05 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A"."ID"=:B1)
   4 - access("B"."ID"=:B1)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      68265  consistent gets
          0  physical reads
          0  redo size
   17667119  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

Answer 5

select a.name name1 ,b.name name2 from tab1,tab2 a,tab2 b where a.id = tab1.id1 and b.id=tab1.id2 ;已选择1000000行。已用时间:  00: 00: 10.59执行计划
----------------------------------------------------------
Plan hash value: 570800776------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  1016K|    19M|   410  (13)| 00:00:05 |
|*  1 |  HASH JOIN            |      |  1016K|    19M|   410  (13)| 00:00:05 |
|   2 |   MERGE JOIN CARTESIAN|      |   100 |  1400 |    16   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | TAB2 |    10 |    70 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |    10 |    70 |    13   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | TAB2 |    10 |    70 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | TAB1 |  1016K|  5955K|   376   (9)| 00:00:05 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("A"."ID"="TAB1"."ID1" AND "B"."ID"="TAB1"."ID2")
统计信息
----------------------------------------------------------
        145  recursive calls
          0  db block gets
      69098  consistent gets
          0  physical reads
          0  redo size
   17667119  bytes sent via SQL*Net to client
     733711  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed