SQL优化（4）——表的三种连接方式

测试以下三种常用的表之间连接方式：

• 排序-合并连接（sort-merge join, SMJ）
• 嵌套循环（nested loop, NL）
• 哈希连接（hash join, HASH）

1. 创建测试表

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> show parameter optimizer_mod

NAME                                 TYPE        VALUE

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

optimizer_mode                       string      ALL_ROWS

 

SQL> create table test1 as select * from dba_objects;

 

表已创建。

 

SQL> create table test2 as select * from dba_objects;

 

表已创建。

 

1. CBO下，默认使用hash join

 

SQL> explain plan for

  2  select * from test1, test2 where test1.object_id = test2.object_id;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3916441650

 

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

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

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

|   0 | SELECT STATEMENT   |       | 56449 |    19M|       |  1366   (2)| 00:00:17 |

|*  1 |  HASH JOIN         |       | 56449 |    19M|    10M|  1366   (2)| 00:00:17 |

|   2 |   TABLE ACCESS FULL| TEST2 | 56449 |  9757K|       |   162   (4)| 00:00:02 |

|   3 |   TABLE ACCESS FULL| TEST1 | 59528 |    10M|       |   162   (4)| 00:00:02 |

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

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择19行。

 

2. RBO下，默认使用sort-merge

 

SQL> explain plan for

  2  select/*+rule*/ * from test1, test2 where test1.object_id = test2.object_id;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 568142516

 

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

| Id  | Operation           | Name  |

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

|   0 | SELECT STATEMENT    |       |

|   1 |  MERGE JOIN         |       |

|   2 |   SORT JOIN         |       |

|   3 |    TABLE ACCESS FULL| TEST2 |

|*  4 |   SORT JOIN         |       |

|   5 |    TABLE ACCESS FULL| TEST1 |

PLAN_TABLE_OUTPUT

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

 

Predicate Information (identified by operation id):

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

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

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

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

已选择22行。

 

3. 可以通过hints指定nl，此时使用test2作为驱动行源

 

SQL> explain plan for

  2  select/*+use_nl(test1,test2)*/ * 

  3  from test1, test2 where test1.object_id = test2.object_id;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3190742687

 

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

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

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

|   0 | SELECT STATEMENT   |       | 56449 |    19M|  9016K  (4)| 30:03:22 |

|   1 |  NESTED LOOPS      |       | 56449 |    19M|  9016K  (4)| 30:03:22 |

|   2 |   TABLE ACCESS FULL| TEST2 | 56449 |  9757K|   162   (4)| 00:00:02 |

|*  3 |   TABLE ACCESS FULL| TEST1 |     1 |   177 |   160   (4)| 00:00:02 |

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

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择19行。

 

 

4. 通过hints指定驱动行源为test1

 

SQL> explain plan for

  2  select/*+ordered use_nl(test1,test2)*/ * 

  3  from test1, test2 where test1.object_id = test2.object_id;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2336902100

 

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

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

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

|   0 | SELECT STATEMENT   |       | 56449 |    19M|  9496K  (3)| 31:39:16 |

|   1 |  NESTED LOOPS      |       | 56449 |    19M|  9496K  (3)| 31:39:16 |

|   2 |   TABLE ACCESS FULL| TEST1 | 59528 |    10M|   162   (4)| 00:00:02 |

|*  3 |   TABLE ACCESS FULL| TEST2 |     1 |   177 |   160   (4)| 00:00:02 |

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

 

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

 

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

 

Note

-----

   - dynamic sampling used for this statement

 

已选择19行。