ORDERED 和 USE_NL() hint (zt)的用法
来源:互联网 发布:镇江java培训学费 编辑:程序博客网 时间:2024/06/06 08:25
ORDERED好理解,就是表示根据 from 后面表的顺序join,从左到右,左边的表做驱动表
USE_NL(),先看看oracle doc怎么说:
In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table:
SELECT /*+ ORDERED USE_NL(customers) to get first row faster */
accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.customer_id = customers.customer_id;
customers 作为inner table,也就是说作为被驱动表。驱动表称为outer table。
也就是说use_nl如果只带了一个表名作为参数,则该表为被驱动表。
如果带了2个以上的参数,oracle 并没有指出 use_nl(a b) 中 哪个是驱动表,所以常使用 ordered 或者 full() 或者 index() 来强化我们的目标
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
试验模拟:
t2: 27325 rows, object_id 上有索引in1
t3: 7326 rows,无索引
两表都已经分析
1.有ordered,按照t3驱动t2的顺序join;
并且USE_NL(t2)也表示t2作为inner table,也就是被驱动表;
没有矛盾
代码:
--------------------------------------------------------------------------------
SQL> select /*+ ordered USE_NL(t2) */ count(*)
2 from t3,t2
3 where t2.object_id=t3.object_id;
COUNT(*)
----------
7325
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7365 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=7365 Card=7325 Bytes=58600)
3 2 TABLE ACCESS (FULL) OF 'T3' (Cost=39 Card=7326 Bytes=29304)
4 2 INDEX (RANGE SCAN) OF 'IN1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=4)
--------------------------------------------------------------------------------
2.有ordered,按照t3驱动t2的顺序join;
但是USE_NL(t3)却表示t3作为inner table,也就是被驱动表;
有矛盾,所以oracle 忽视这个hint,执行hash join
代码:
--------------------------------------------------------------------------------
SQL> select /*+ ordered USE_NL(t3) */ count(*)
2 from t3,t2
3 where t2.object_id=t3.object_id;
COUNT(*)
----------
7325
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=48 Card=7325 Bytes=58600)
3 2 TABLE ACCESS (FULL) OF 'T3' (Cost=39 Card=7326 Bytes=29304)
4 2 INDEX (FAST FULL SCAN) OF 'IN1' (NON-UNIQUE) (Cost=4 Card=27325 Bytes=109300)
--------------------------------------------------------------------------------
3.单独使用ordered是执行hash join,也用来选择驱动表(join顺序)
代码:
--------------------------------------------------------------------------------
SQL> select /*+ ordered */ count(*)
2 from t3,t2
3 where t2.object_id=t3.object_id;
COUNT(*)
----------
7325
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=48 Card=7325 Bytes=58600)
3 2 TABLE ACCESS (FULL) OF 'T3' (Cost=39 Card=7326 Bytes=29304)
4 2 INDEX (FAST FULL SCAN) OF 'IN1' (NON-UNIQUE) (Cost=4 Card=27325 Bytes=109300)
SQL> select /*+ ordered */ count(*)
2 from t2,t3
3 where t2.object_id=t3.object_id;
COUNT(*)
----------
7325
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=52 Card=7325 Bytes=58600)
3 2 INDEX (FAST FULL SCAN) OF 'IN1' (NON-UNIQUE) (Cost=4 Card=27325 Bytes=109300)
4 2 TABLE ACCESS (FULL) OF 'T3' (Cost=39 Card=7326 Bytes=29304)
--------------------------------------------------------------------------------
4。第2个例子中,如果t3.object_id也建索引,仍然走hash join,只不过都变成FAST FULL SCAN
原因同2。当然,变成FAST FULL SCAN是因为select count(*),如果是select *的话就会是FTS了。
代码:
--------------------------------------------------------------------------------
SQL> create index in3 on t3(object_id);
索引已创建。
SQL> set autot on
SQL> select /*+ ordered USE_NL(t3) */ count(*)
2 from t3,t2
3 where t2.object_id=t3.object_id;
COUNT(*)
----------
7325
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=13 Card=7325 Bytes=58600)
3 2 INDEX (FAST FULL SCAN) OF 'IN3' (NON-UNIQUE) (Cost=4 Card=7326 Bytes=29304)
4 2 INDEX (FAST FULL SCAN) OF 'IN1' (NON-UNIQUE) (Cost=4 Card=27325 Bytes=109300)
-------------------------------
之前对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 | | | | |
-------------------------------------------------------------
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 | | | | |
----------------------------------------------------------------------------
执行计划是错误的,还是取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 | | | | |
----------------------------------------------------------------------------
执行计划还是错误的,这到底是怎么回事?
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|
-------------------------------------------------- ----------------------------
这回执行计划是正确的,原来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|
-------------------------------------------------- ----------------------------
1./*+ use_nl(t2,t) */提示走nest Loop,但是没有提示t2还是t为驱动表
2./*+ ordered user_nl(t2,t) */提示走 Nest Loop,order提示的是from 后面的第一个表为驱动表.
3./*+ leading(t2) use_nl(t) */直接提示t2为驱动表。
结论:use_NL不能让优化器确定谁是驱动表谁是被驱动表。use_nl(t,t2)也没有指出哪个是驱动表,这时候我们就需要使用Ordered ,Leading来强制指定驱动表,以达到我们的目的。
- ORDERED 和 USE_NL() hint (zt)的用法
- ORDERED 和 USE_NL() hint (zt)的用法
- Hint&ordered&leading&use_nl
- oracle hint中ordered 和leading原理很好的帖子
- oracle hint中ordered 和leading原理很好的帖子
- oracle hint中ordered 和leading原理很好的帖子
- hints ordered leading use_nl
- 深入理解Oracle表(1):ORDERED和USE_NL
- 深入理解Oracle表(1):ORDERED和USE_NL
- Oracle Hint:USE_NL、USE_MERGE、USE_HASH
- Oracle Hint:USE_NL、USE_MERGE、UESE_HASH
- oracle里use_nl的简单用法
- SQL hint中正确使用use_nl提示
- ORACLE HINT的用法
- Oracle Hint的用法
- Oracle HINT的用法
- Oracle HINT的用法
- Oracle Hint的用法
- 假数据自我添加测试--NSArray object
- C++拷贝构造函数详解
- POJ2513Colored Sticks一笔画问题
- register_chrdev 深入解析
- 成员变量
- ORDERED 和 USE_NL() hint (zt)的用法
- Android中Paint字体的使用
- Android 发送短息
- HDU 2825 AC自动机+DP
- MFC环境osgEarth开发程序内存泄露的解决办法
- Linux系统脚本的三种执行方式
- 学习C语言的点滴(1)
- static和final
- javascript--hasOwnProperty()+isPrototypeof()+in方法