SQL> select /**//*+use_nl(a,b)*/ *from test_2 a,test_3 b where a.object_name=b.table_name;

已选择1736行。


执行计划
----------------------------------------------------------
Plan hash value: 3977035679

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  2582 |  1462K|   239K  (3)| 00:47:59 |
|   1 |  NESTED LOOPS      |        |  2582 |  1462K|   239K  (3)| 00:47:59 |
|   2 |   TABLE ACCESS FULL| TEST_3 |  1529 |   727K|    13   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_2 |     2 |   186 |   157   (3)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
       1798  recursive calls
          0  db block gets
    1044861  consistent gets
          0  physical reads
          0  redo size
     155172  bytes sent via SQL*Net to client
       1650  bytes received via SQL*Net from client
        117  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       1736  rows processed

SQL> select * from test_2 a,test_3 b where a.object_name=b.table_name;

已选择1736行。

执行计划
----------------------------------------------------------
Plan hash value: 1527207201

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  2582 |  1462K|   173   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |        |  2582 |  1462K|   173   (4)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| TEST_3 |  1529 |   727K|    13   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_2 | 49334 |  4480K|   158   (3)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
       1798  recursive calls
          0  db block gets
       1125  consistent gets
          0  physical reads
          0  redo size
     165001  bytes sent via SQL*Net to client
       1650  bytes received via SQL*Net from client
        117  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       1736  rows processed
归并

SQL> select /*+use_merge(a,b)*/ *from test_2 a,test_3 b where a.object_name=b.table_name;

已选择1736行。

执行计划
----------------------------------------------------------
Plan hash value: 3054602342

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |  2582 |  1462K|       |  1399   (2)| 00:00:17 |
|   1 |  MERGE JOIN         |        |  2582 |  1462K|       |  1399   (2)| 00:00:17 |
|   2 |   SORT JOIN         |        |  1529 |   727K|  1896K|   176   (2)| 00:00:03 |
|   3 |    TABLE ACCESS FULL| TEST_3 |  1529 |   727K|       |    13   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |        | 49334 |  4480K|    11M|  1223   (2)| 00:00:15 |
|   5 |    TABLE ACCESS FULL| TEST_2 | 49334 |  4480K|       |   158   (3)| 00:00:02 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
       filter("A"."OBJECT_NAME"="B"."TABLE_NAME")

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
       1798  recursive calls
          0  db block gets
       1010  consistent gets
          0  physical reads
          0  redo size
     179175  bytes sent via SQL*Net to client
       1650  bytes received via SQL*Net from client
        117  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
       1736  rows processed