HINT篇---表关联顺序相关

三：表关联顺序相关的Hint
1、/*+ leading(TABLE) */  指定哪个表为驱动表
2、/*+ ordered */    指定按照from后面的顺序来选择谁做驱动表
3、/*+ use_hash(t,t1) */   表明采用hash方式连接
4、/*+ use_nl(t,t1) */   表明采用nested loop方式连接
5、/*+ use_merge(t,t1) */  表明采用merge方式连接
6、 NO_USE_HASH,NO_USE_NL,NO_USE_MERGE HINT  禁用这些链接方式
实验一：使用/*+ leading(TABLE) */
SQL> select  t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   162K|    14M|       |  1030   (1)| 00:00:13 |
|*  1 |  HASH JOIN            |           |   162K|    14M|  3976K|  1030   (1)| 00:00:13 |
|   2 |   INDEX FAST FULL SCAN| IND_T4_ID |   162K|  2066K|       |   106   (1)| 00:00:02 |--默认情况下from后第一个表为驱动表
|   3 |   TABLE ACCESS FULL   | T5        | 97980 |  7559K|       |   304   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
         14  recursive calls
          3  db block gets
      11652  consistent gets
          0  physical reads
        952  redo size
    4259624  bytes sent via SQL*Net to client
     111833  bytes received via SQL*Net from client
      10121  SQL*Net roundtrips to/from client

SQL> select  /*+ leading(t4) */t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   162K|    14M|       |  1030   (1)| 00:00:13 |
|*  1 |  HASH JOIN            |           |   162K|    14M|  3976K|  1030   (1)| 00:00:13 |--将t4作为驱动表，和不用没有变化。
|   2 |   INDEX FAST FULL SCAN| IND_T4_ID |   162K|  2066K|       |   106   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL   | T5        | 97980 |  7559K|       |   304   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
          7  recursive calls
          3  db block gets
      11637  consistent gets
          0  physical reads
          0  redo size
    4259624  bytes sent via SQL*Net to client
     111833  bytes received via SQL*Net from client
      10121  SQL*Net roundtrips to/from client
SQL> select  /*+ leading(t5) */t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   162K|    14M|       |  1030   (1)| 00:00:13 |
|*  1 |  HASH JOIN            |           |   162K|    14M|  8712K|  1030   (1)| 00:00:13 |将t5作为驱动表，t5的值进入内存通过hash运算得到
|   2 |   TABLE ACCESS FULL   | T5        | 97980 |  7559K|       |   304   (1)| 00:00:04 |的hash值放入hash表中，然后去t4的值通过hash
|   3 |   INDEX FAST FULL SCAN| IND_T4_ID |   162K|  2066K|       |   106   (1)| 00:00:02 |运算得到hash值去和t5的hash值匹配
-------------------------------------------------------------------------------------------
          7  recursive calls
          3  db block gets
      11761  consistent gets
          0  physical reads
          0  redo size
    4263381  bytes sent via SQL*Net to client
     111833  bytes received via SQL*Net from client
      10121  SQL*Net roundtrips to/from client
备注：这里t5一直全表扫描，如果让其走索引的话，那cost值就比全表扫描要高，可以通过加hint来解决。
实验二：使用/*+ ordered */
SQL> select  /*+ ordered */t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;

151788 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2752026631

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   162K|    14M|       |  1030   (1)| 00:00:13 |
|*  1 |  HASH JOIN            |           |   162K|    14M|  3976K|  1030   (1)| 00:00:13 |
|   2 |   INDEX FAST FULL SCAN| IND_T4_ID |   162K|  2066K|       |   106   (1)| 00:00:02 |这里from后的t4作为驱动表
|   3 |   TABLE ACCESS FULL   | T5        | 97980 |  7559K|       |   304   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
实验三：使用/*+ use_hash(t,t1) */
SQL> select  /*+ use_hash(t4,t5) */ t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;

151788 rows selected.

Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
Plan hash value: 2752026631

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |   162K|    14M|       |  1030   (1)| 00:00:13 |
|*  1 |  HASH JOIN            |           |   162K|    14M|  3976K|  1030   (1)| 00:00:13 |
|   2 |   INDEX FAST FULL SCAN| IND_T4_ID |   162K|  2066K|       |   106   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL   | T5        | 97980 |  7559K|       |   304   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------
          7  recursive calls
          3  db block gets
      11637  consistent gets
          0  physical reads
          0  redo size
    4259624  bytes sent via SQL*Net to client
     111833  bytes received via SQL*Net from client
      10121  SQL*Net roundtrips to/from client
实验四：使用/*+ use_nl(t4,t5) */
SQL> select  /*+ use_nl(t4,t5) */ t4.object_id,t5.object_name from t4 ,t5 where t4.object_id=t5.object_id;
151788 rows selected.
Elapsed: 00:00:01.23--时间比使用hash join用的多
Execution Plan
----------------------------------------------------------
Plan hash value: 3143514191

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   162K|    14M| 98348   (1)| 00:19:41 |--代价比hash join大很多
|   1 |  NESTED LOOPS      |           |   162K|    14M| 98348   (1)| 00:19:41 |
|   2 |   TABLE ACCESS FULL| T5        | 97980 |  7559K|   304   (1)| 00:00:04 |
|*  3 |   INDEX RANGE SCAN | IND_T4_ID |     2 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
          7  recursive calls
          1  db block gets
      34410  consistent gets
          0  physical reads
          0  redo size
    4259624  bytes sent via SQL*Net to client
     111833  bytes received via SQL*Net from client
      10121  SQL*Net roundtrips to/from client

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1064690/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1064690/