SQL hint中正确使用use_nl提示
来源:互联网 发布:igv软件使用 编辑:程序博客网 时间:2024/05/22 04:57
之前对use_nl的理解一直很模糊,看下面的案例。
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来固化其执行计划。
1.使用/*+ ORDERED use_nl(t2,t) */提示
SQL> select /*+ ORDERED use_nl(t2,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: 3775534098
----------------------------------------------------------------------------
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: 3775534098
----------------------------------------------------------------------------
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 | | | | |
----------------------------------------------------------------------------
执行计划是错误的,还是取table_detail为驱动表。
错误理解:使用order提示,执行计划会去取use_nl(t2,t)中的t2作为驱动表。
2.使用/*+ ORDERED use_nl(t,t2) */提示
SQL> select /*+ ORDERED use_nl(t,t2) */*
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: 3775534098
----------------------------------------------------------------------------
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: 3775534098
----------------------------------------------------------------------------
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 | | | | |
----------------------------------------------------------------------------
执行计划还是错误的,这到底是怎么回事?
3.使用/*+ 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|
-------------------------------------------------- ----------------------------
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|
-------------------------------------------------- ----------------------------
这回执行计划是正确的,原来order是根据from表顺序来决定驱动表,而不是use_nl(t,t2)中表的先后顺序。
调整from后面的表顺序是很老土的调优,仿佛回到了rbo的年代,采用leading指定驱动表看是否可行。
4.使用leading提示来指定驱动表
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|
-------------------------------------------------- ----------------------------
1./*+use_nl(t2,t) */ 提示走nest loop,但没有提示t2还是t为驱动表 。
2./*+ ordered use_nl(t2,t) */提示走nest loop,order提示的是from后面的第一个表为驱动表。
3./*+ leading(t2) use_nl(t) */ 直接提示t2为驱动表。
结论:use_nl不能让优化器确定谁是驱动表谁是被驱动的表,use_nl(t,t2)也没有指出哪个是驱动表,这时候我们需要使用ordered,leading来强制指定驱动表,以达到我们的目的。
- SQL hint中正确使用use_nl提示
- Hint&ordered&leading&use_nl
- Oracle如何在有视图的SQL中使用hint(提示)来设置视图内的
- Oracle Hint:USE_NL、USE_MERGE、USE_HASH
- Oracle Hint:USE_NL、USE_MERGE、UESE_HASH
- Oracle中hint提示
- OPEN SQL中使用 ORACLE HINT
- SQL语句优化过程中,使用Hint
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- ORACLE hint提示优化SQL
- oracle hint提示优化SQL
- ORACLE hint提示优化SQL
- 优化SQL HINT使用
- ORDERED 和 USE_NL() hint (zt)的用法
- ORDERED 和 USE_NL() hint (zt)的用法
- SQL优化:一篇文章说清楚Oracle Hint的正确使用姿势
- Excel常用VBA操作命令
- 1.2 Oracle表空间的操作
- matlab实现冒泡法排序
- asp+access 按时派+暗藏测试上
- SQL 数据库开发中的一些精典代码
- SQL hint中正确使用use_nl提示
- 调用约定(Calling convention)
- 关于eclipse 内存不足的问题总结
- 直面经典:重温KMP(不着一图,尽得精髓)
- MySQL中修改密码及访问限制设置详解
- 内存使用习惯
- Shell编程基础
- Jquery掩码插件
- 学习《Java SE 6 新特性: JMX 与系统管理》