17,382
社区成员




--建立测试表
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>
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了 就不再解析了 直接返回错误提示
*/