关于ORDERED 和 USE_NL() hint

参考和引用：http://blog.itpub.net/post/26/12950

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)
--------------------------------------------------------------------------------