oracle 10i :多表链接（from A, B, C）的执行顺序

[物润声无]

没事琢磨一下，表连接时sql的优化器是怎么工作的。

创建table1，table2，table3 三张表，分别插入4，3，2条数据， 如下。

CREATE TABLE table1(
A VARCHAR(2),
B VARCHAR(2),
C VARCHAR(2)
)
/
CREATE TABLE table2(
M VARCHAR(2),
N VARCHAR(2)
)
/
CREATE TABLE table3(
X VARCHAR(2),
Y VARCHAR(2)
)
/
INSERT INTO table1 VALUES('A1','B1','C1')
/
INSERT INTO table1 VALUES('A2','B2','C2')
/
INSERT INTO table1 VALUES('A3','B3','C3')
/
INSERT INTO table1 VALUES('A4','B4','C4')
/
INSERT INTO table2 VALUES('M1','N1')
/
INSERT INTO table2 VALUES('M2','N2')
/
INSERT INTO table2 VALUES('M3','N3')
/
INSERT INTO table3 VALUES('X1','Y1')
/
INSERT INTO table3 VALUES('X2','Y2')
/


--执行: select * from table1, table2, table3;
--result:

A B C M N X Y
-- -- -- -- -- -- --
A1 B1 C1 M1 N1 X1 Y1
A2 B2 C2 M1 N1 X1 Y1
A3 B3 C3 M1 N1 X1 Y1
A4 B4 C4 M1 N1 X1 Y1
A1 B1 C1 M2 N2 X1 Y1
A2 B2 C2 M2 N2 X1 Y1
A3 B3 C3 M2 N2 X1 Y1
A4 B4 C4 M2 N2 X1 Y1
A1 B1 C1 M3 N3 X1 Y1
A2 B2 C2 M3 N3 X1 Y1
A3 B3 C3 M3 N3 X1 Y1
A4 B4 C4 M3 N3 X1 Y1
A1 B1 C1 M1 N1 X2 Y2
A2 B2 C2 M1 N1 X2 Y2
A3 B3 C3 M1 N1 X2 Y2
A4 B4 C4 M1 N1 X2 Y2
A1 B1 C1 M2 N2 X2 Y2
A2 B2 C2 M2 N2 X2 Y2
A3 B3 C3 M2 N2 X2 Y2
A4 B4 C4 M2 N2 X2 Y2
A1 B1 C1 M3 N3 X2 Y2
A2 B2 C2 M3 N3 X2 Y2
A3 B3 C3 M3 N3 X2 Y2
A4 B4 C4 M3 N3 X2 Y2

如果在连接是用的是循环嵌套思想，从执行结果中可以看出层次由外到内分别是：table3, table2, table1。
这个层次的顺序是否和from table1, table2, table3的顺序有关？
验证一下，执行：select * from table3, table2, table1;
--result：
X Y M N A B C
-- -- -- -- -- -- --
X1 Y1 M1 N1 A1 B1 C1
X1 Y1 M1 N1 A2 B2 C2
X1 Y1 M1 N1 A3 B3 C3
X1 Y1 M1 N1 A4 B4 C4
X1 Y1 M2 N2 A1 B1 C1
X1 Y1 M2 N2 A2 B2 C2
X1 Y1 M2 N2 A3 B3 C3
X1 Y1 M2 N2 A4 B4 C4
X1 Y1 M3 N3 A1 B1 C1
X1 Y1 M3 N3 A2 B2 C2
X1 Y1 M3 N3 A3 B3 C3
X1 Y1 M3 N3 A4 B4 C4
X2 Y2 M1 N1 A1 B1 C1
X2 Y2 M1 N1 A2 B2 C2
X2 Y2 M1 N1 A3 B3 C3
X2 Y2 M1 N1 A4 B4 C4
X2 Y2 M2 N2 A1 B1 C1
X2 Y2 M2 N2 A2 B2 C2
X2 Y2 M2 N2 A3 B3 C3
X2 Y2 M2 N2 A4 B4 C4
X2 Y2 M3 N3 A1 B1 C1
X2 Y2 M3 N3 A2 B2 C2
X2 Y2 M3 N3 A3 B3 C3
X2 Y2 M3 N3 A4 B4 C4

比较两次结果可以发现，除了列的顺序不同，循环嵌套的最外层仍然是table3, table2, table1。
循环嵌套的层次和什么有关？我们将table2中的记录数目去掉一条，在table3中加1条，各表中的条数现在是：
table1 4条，table2 2条，table3 3条。
在执行select * from table3, table2, table1;
看看结果。

X Y M N A B C
-- -- -- -- -- -- --
X1 Y1 M1 N1 A1 B1 C1
X1 Y1 M1 N1 A2 B2 C2
X1 Y1 M1 N1 A3 B3 C3
X1 Y1 M1 N1 A4 B4 C4
X2 Y2 M1 N1 A1 B1 C1
X2 Y2 M1 N1 A2 B2 C2
X2 Y2 M1 N1 A3 B3 C3
X2 Y2 M1 N1 A4 B4 C4
X3 Y4 M1 N1 A1 B1 C1
X3 Y4 M1 N1 A2 B2 C2
X3 Y4 M1 N1 A3 B3 C3
X3 Y4 M1 N1 A4 B4 C4
X1 Y1 M2 N2 A1 B1 C1
X1 Y1 M2 N2 A2 B2 C2
X1 Y1 M2 N2 A3 B3 C3
X1 Y1 M2 N2 A4 B4 C4
X2 Y2 M2 N2 A1 B1 C1
X2 Y2 M2 N2 A2 B2 C2
X2 Y2 M2 N2 A3 B3 C3
X2 Y2 M2 N2 A4 B4 C4
X3 Y4 M2 N2 A1 B1 C1
X3 Y4 M2 N2 A2 B2 C2
X3 Y4 M2 N2 A3 B3 C3
X3 Y4 M2 N2 A4 B4 C4
循环层次变成了：table2， table3， table1。

改变了表的大小，循环嵌套的层次也发生了变化，似乎是sql的优化器将最小的表放在了循环嵌套的最外层，
这一步是怎么实现的呢？
是优化器根据from后面的表的大小对select语进行了调整，再把调整后的sql语句送到编译器执行？还是优化器直接告诉编译器执行？如果是后面的那优化器有怎么和编译器通讯？

sql语句的执行步骤到底是怎样的呢？ 还是sql本来就不应该用这中面向过程（一步一步的）的思想去分析？
表的大小是指什么？列数？记录数？实际占用的空间？还是其他？
一塌糊涂中。

[真的不知道取什么名]

select * from table1, table2, table3;
/*oracle一般都是从右往左解析的，这个就是将table3和table2做笛卡尔积得到的结果再和table1做笛卡尔积*/
SQL> select * from emp where empno1=2343 and ename1='sdfds';
select * from emp where empno1=2343 and ename1='sdfds'
*
第 1 行出现错误:
ORA-00904: "ENAME1": 标识符无效
/*
从这个例子就可以看出 其实emp里empno1和ename1这两列都是错了
但是oracle是从右往左解析的，先发现了没有ename1了 就不再解析了 直接返回错误提示
*/

[真的不知道取什么名]

select * from table1, table2, table3;
/*oracle一般都是从右往左解析的，这个就是将table3和table2做笛卡尔积得到的结果再和table1做笛卡尔积*/
SQL> select * from emp where empno1=2343 and ename1='sdfds';
select * from emp where empno1=2343 and ename1='sdfds'
  *
第 1 行出现错误:
ORA-00904: "ENAME1": 标识符无效
/*
从这个例子就可以看出 其实emp里empno1和ename1这两列都是错了
但是oracle是从右往左解析的，先发现了没有ename1了 就不再解析了 直接返回错误提示
*/

[虫洞]

10g以上默认采用CBO
在执行前会先进行分析
sql的优化器将最小的表放在了循环嵌套的最外层

[物润声无]

受益，谢谢楼上两位。

[tangren]

--建立测试表
create table tab1 as select t.object_id,t.object_name from dba_objects t where rownum<10001;
create table tab2 as select t.object_id,t.object_type from dba_objects t where rownum<5;
create index idx_tab1_id on tab1(object_id); 
create index idx_tab2_id on tab2(object_id);


SQL> set autotrace traceonly;
--设置优化器为规则优化模式
SQL> alter session set optimizer_mode=rule;

会话已更改。

SQL> select * from tab2 a,tab1 b where a.object_id=b.object_id;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB2'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TAB1'
   4    2       INDEX (RANGE SCAN) OF 'IDX_TAB2_ID' (NON-UNIQUE)


SQL> select * from tab1 b,tab2 a where a.object_id=b.object_id;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TAB2'
   4    2       INDEX (RANGE SCAN) OF 'IDX_TAB1_ID' (NON-UNIQUE)


--设置优化器为CHOOSE模式（在有统计数据情况下实际上为COST）
SQL> alter session set optimizer_mode=choose;

会话已更改。

SQL> exec dbms_stats.gather_table_stats('test','tab1',cascade => true);

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('test','tab2',cascade => true);

PL/SQL 过程已成功完成。

SQL> select * from tab2 a,tab1 b where a.object_id=b.object_id;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=4 Bytes=168)
   1    0   HASH JOIN (Cost=9 Card=4 Bytes=168)
   2    1     TABLE ACCESS (FULL) OF 'TAB2' (Cost=2 Card=4 Bytes=60)
   3    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=6 Card=10000 Bytes=2
          70000)


SQL> select * from tab1 b,tab2 a where a.object_id=b.object_id;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=4 Bytes=168)
   1    0   HASH JOIN (Cost=9 Card=4 Bytes=168)
   2    1     TABLE ACCESS (FULL) OF 'TAB2' (Cost=2 Card=4 Bytes=60)
   3    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=6 Card=10000 Bytes=2
          70000)


SQL>