日期:2014-05-16 浏览次数:20351 次
参考和引用:http://blog.itpub.net/post/26/12950 ORDERED好理解,就是表示根据 from 后面表的顺序join,从左到右,左边的表做驱动表 USE_NL(),先看看oracle doc怎么说: SELECT /*+ ORDERED USE_NL(customers) to get first row faster */ customers 作为inner table,也就是说作为被驱动表。驱动表称为outer table。 也就是说use_nl如果只带了一个表名作为参数,则该表为被驱动表。 如果带了2个以上的参数,oracle 并没有指出 use_nl(a b) 中 哪个是驱动表,所以常使用 ordered 或者 full() 或者 index() 来强化我们的目标 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 试验模拟: 1.有ordered,按照t3驱动t2的顺序join; ? COUNT(*) ? 2.有ordered,按照t3驱动t2的顺序join; ? COUNT(*) ? 3.单独使用ordered是执行hash join,也用来选择驱动表(join顺序) 代码: ? COUNT(*) SQL> select /*+ ordered */ count(*) ? COUNT(*) ? 4。第2个例子中,如果t3.object_id也建索引,仍然走hash join,只不过都变成FAST FULL SCAN
In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table:
accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.customer_id = customers.customer_id;
t2: 27325 rows, object_id 上有索引in1
t3: 7326 rows,无索引
两表都已经分析
并且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;
----------
????? 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)
--------------------------------------------
但是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;
----------
????? 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 t3,t2
? 3?? where t2.object_id=t3.object_id;
----------
????? 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)
? 2?? from t2,t3
? 3?? where t2.object_id=t3.object_id;
----------
????? 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)
--------------------------------------------
原因同2。当然,变成FAST FULL SCAN是因为select count(*),如果是select *的话就会是FTS了。
代码:
------------------------------------