select 中强制使用 嵌套循环连接( Nested Loop Join NL)

使用哈希连接 (Hash Join) 适合大表，返回大结果集

SQL> select * from test1, test2 where test1.object_id = test2.object_id and rown

um < 2;

执行计划

----------------------------------------------------------

Plan hash value: 3552420324

--------------------------------------------------------------------------------

-----

| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |     1 |   480 |       |   415   (2)| 00:00:05 |

|*  1 |  COUNT STOPKEY      |       |       |       |       |            |    |

|*  2 |   HASH JOIN         |       | 20281 |  9506K|  3408K|   415   (2)| 00:00:05 |

|   3 |    TABLE ACCESS FULL| TEST2 | 20280 |  3168K|       |    69   (3)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST1 | 21604 |  3375K|       |     3  (34)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<2)

   2 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

          3  recursive calls

          0  db block gets

        307  consistent gets

        259  physical reads

          0  redo size

       1923  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

使用嵌套循环连接 ， 适合小表返回小结果集

SQL> select /*+ use_nl( test1, test2) */ * from test1, test2 where test1.object_

id = test2.object_id and rownum < 2;

执行计划

----------------------------------------------------------

Plan hash value: 2978587790

-----------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |     1 |   320 |   781K  (4)| 02:36:19 |

|*  1 |  COUNT STOPKEY      |       |       |       |            |          |

|   2 |   NESTED LOOPS      |       | 20281 |  6337K|   781K  (4)| 02:36:19 |

|   3 |    TABLE ACCESS FULL| TEST1 | 21604 |  3375K|     3  (34)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| TEST2 |     1 |   160 |    36   (3)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<2)

   4 - filter("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")

Note

-----

   - dynamic sampling used for this statement

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

       1923  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

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

转载于:http://blog.itpub.net/98157/viewspace-662220/