ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
上面提到的from后面表的顺序是从右向左解析执行没有问题,我们可以从执行计划中看到,但是把小表放在最右边似乎更慢了,不知道是什么原因?
SQL> desc t_t1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
SQL> desc t_t2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
SQL> select count(*) from t_t1;
COUNT(*)
----------
3170
已用时间: 00: 00: 00.00
SQL> select count(*) from t_t2;
COUNT(*)
----------
71212
已用时间: 00: 00: 00.01
SQL> select count(*) from t_t2 a , t_t1 b where a.table_name = b.table_name;
COUNT(*)
----------
58083
已用时间: 00: 00: 00.07
SQL> select count(*) from t_t1 a , t_t2 b where a.table_name = b.table_name;
COUNT(*)
----------
58083
已用时间: 00: 00: 00.05
SQL> set autotrace on
SQL> select count(*) from t_t2 a , t_t1 b where a.table_name = b.table_name;
COUNT(*)
----------
58083
已用时间: 00: 00: 00.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'T_T1'
5 2 SORT (JOIN)
6 5 TABLE ACCESS (FULL) OF 'T_T2'
Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
382 consistent gets
668 physical reads
0 redo size
382 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
SQL> select count(*) from t_t1 a , t_t2 b where a.table_name = b.table_name;
COUNT(*)
----------
58083
已用时间: 00: 00: 00.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'T_T2'
5 2 SORT (JOIN)
6 5 TABLE ACCESS (FULL) OF 'T_T1'
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
382 consistent gets
485 physical reads
0 redo size
382 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
===================================================]
SQL> select count(*) from tt;
COUNT(*)
----------
7
已用时间: 00: 00: 00.00
SQL> select count(*) from t_t2;
COUNT(*)
----------
71212
已用时间: 00: 00: 00.01
SQL> select count(*) from t_t2 , tt;
COUNT(*)
----------
498484
已用时间: 00: 00: 00.03
SQL> select count(*) from tt , t_t2;
COUNT(*)
----------
498484
已用时间: 00: 00: 00.02
SQL>