深入理解Oracle表(1)：ORDERED和USE_NL

 

 

深入理解Oracle表(1)：ORDERED和USE_NL

 

http://blog.csdn.net/linwaterbin/article/details/8544436

        O RDERED好理解，就是表示根据 from 后面表的顺序join，从左到右，左边的表做驱动表
       use_nl（t1,t2）：表示对表t1、t2关联时采用嵌套循环连接,其并不能让优化器确定谁是驱动表或谁是被驱动的表
       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
       如果指定的表是outer table（驱动表），则优化器会忽略这个hint
       如果非要强制它作为inner table，可以配上ordered参数
       oradered 表示根据from 后面表的顺序，从左到右join，左表做驱动表，3个或3个以上最有用
       也就是说use_nl如果只带了一个表名作为参数，则该表为被驱动表
       如果带了2个以上的参数，Oracle并没有指出use_nl(a,b)中哪个是驱动表，所以常使用ordered或者full()或者index()来强化我们的目标
       

       以下是测试：

[sql] view plain copy print ?

1. hr@ORCL> select  first_name,departments.department_id from employees,departments where employees.department_id=departments.department_id;  
2.   
3. Execution Plan  
4. ----------------------------------------------------------  
5. Plan hash value: 169719308  
6.   
7. ---------------------------------------------------------------------------------  
8. | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
9. ---------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |            |   106 |  1484 |     3   (0)| 00:00:01 |  
11. |   1 |  NESTED LOOPS      |            |   106 |  1484 |     3   (0)| 00:00:01 |  
12. |   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1070 |     3   (0)| 00:00:01 |  
13. |*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |  
14. ---------------------------------------------------------------------------------  

hr@ORCL> select  first_name,departments.department_id from employees,departments where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 169719308

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   106 |  1484 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |            |   106 |  1484 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1070 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

       此处优化器选择employees作为驱动表，因为departments上有索引，而且索引正好建立在连接列上

[sql] view plain copy print ?

1. hr@ORCL> select /*+ use_nl(employees) */ first_name,departments.department_id from employees,departments where employees.department_id=departments.department_id;  
2.   
3. Execution Plan  
4. ----------------------------------------------------------  
5. Plan hash value: 169719308  
6.   
7. ---------------------------------------------------------------------------------  
8. | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
9. ---------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |            |   106 |  1484 |     3   (0)| 00:00:01 |  
11. |   1 |  NESTED LOOPS      |            |   106 |  1484 |     3   (0)| 00:00:01 |  
12. |   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1070 |     3   (0)| 00:00:01 |  
13. |*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |  
14. ---------------------------------------------------------------------------------  

hr@ORCL> select /*+ use_nl(employees) */ first_name,departments.department_id from employees,departments where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 169719308

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   106 |  1484 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |            |   106 |  1484 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  1070 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

       由于employees是作为驱动表，优化器会忽略hint提示

[sql] view plain copy print ?

1. hr@ORCL> select /*+ ordered use_nl(employees) */ first_name,departments.department_id from departments,employees where employees.department_id=departments.department_id;  
2.   
3. Execution Plan  
4. ----------------------------------------------------------  
5. Plan hash value: 2677871237  
6.   
7. -------------------------------------------------------------------------------------------------  
8. | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT            |                   |   106 |  1484 |     8   (0)| 00:00:01 |  
11. |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     4 |    40 |     1   (0)| 00:00:01 |  
12. |   2 |   NESTED LOOPS              |                   |   106 |  1484 |     8   (0)| 00:00:01 |  
13. |   3 |    INDEX FULL SCAN          | DEPT_ID_PK        |    27 |   108 |     1   (0)| 00:00:01 |  
14. |*  4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |  
15. -------------------------------------------------------------------------------------------------  

hr@ORCL> select /*+ ordered use_nl(employees) */ first_name,departments.department_id from departments,employees where employees.department_id=departments.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2677871237

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   106 |  1484 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     4 |    40 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                   |   106 |  1484 |     8   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN          | DEPT_ID_PK        |    27 |   108 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

       现在是departments作为驱动表了