关于LEADING、ORDERED:
/*+ORDERED*/提示按照from后表的顺序执行。
/*+LEADING(table1 table2 ...)*/提示按照指定顺序执行。
通常在sql中出现了join的时候,可能需要用到这两个hint,会比较有用。这里引用下在Taobao DBA Team上看到的一个例子:
SQL> select *
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 31598426
----------------------------------------------------------
Operation | Name |Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------
SELECT STATEMENT | |784K|124M|2132(1)|00:00:26|
NESTED LOOPS | |784K|124M|2132(1)|00:00:26|
TABLE ACCESS FULL | table_detail|96 |15744| 11(0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
-------------------------------------------------------------
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 31598426
----------------------------------------------------------
Operation | Name |Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------
SELECT STATEMENT | |784K|124M|2132(1)|00:00:26|
NESTED LOOPS | |784K|124M|2132(1)|00:00:26|
TABLE ACCESS FULL | table_detail|96 |15744| 11(0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
-------------------------------------------------------------
t2是个虚拟的表,没有准确的统计信息,很容易出现执行计划错误,这种sql最好要用hint来固化其执行计划。
使用/*+ ORDERED use_nl(t,t2) */提示。
SQL> select /*+ ORDERED use_nl(t2,t) */*
2 from table(cast(str2varlist('123') as vartabletype)) t2,
3 table_detail t
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------
使用leading提示来指定驱动表
2 from table(cast(str2varlist('123') as vartabletype)) t2,
3 table_detail t
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------
SQL> select /*+ leading(t2) use_nl(t) */*
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------
2 from table_detail t,
3 table(cast(str2varlist('123') as vartabletype)) t2
4 where t.id = t2.column_value;
Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
Operation | Name Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT | |784K| 124M|25 (0)|00:00:01|
TABLE ACCESS BY INDEX ROWID | table_detail | 96 |15744| 1 (0)|00:00:01|
NESTED LOOPS | |784K| 124M|25 (0)|00:00:01|
COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | | | | |
INDEX RANGE SCAN | IND_deail_id | 1 | | 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/104446/viewspace-561456/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/104446/viewspace-561456/