RULE模式下是否FROM列表順序決定驅動表

前兩天在ITPUB看到這個問題，很多人有這個錯誤的概念，在RULE最佳化模式下，FROM語句中表的順序連線的驅動表，但是事實上並不是這麼簡單。

 

 

其實RULE最佳化模式已經基本上退出了歷史舞臺，不過既然有人提起了，就把概念描述清楚，對RULE最佳化模式不感興趣的可以無視這篇文章。

RULE方式是基於規則的，根據FROM列表中的順序判斷驅動表是規則之一，但是這個規則的優先順序並不高，如果有其他優先順序規則存在，則執行計劃會發生改變：

SQL> CREATE TABLE T1            
  2  (ID NUMBER,
  3  NAME VARCHAR2(30));

Table created.

SQL> CREATE TABLE T2
  2  (ID NUMBER,
  3  NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T1
  2  SELECT ROWNUM, TNAME     
  3  FROM TAB;

95 rows created.

SQL> INSERT INTO T2
  2  SELECT ROWNUM, TABLE_NAME
  3  FROM DBA_TABLES;

8923 rows created.

SQL> COMMIT;

Commit complete.

下面將最佳化模式改為RULE模式，看看是否驅動表的選擇與FROM列表中的位置有關：

SQL> ALTER SESSION SET OPTIMIZER_MODE = RULE;

Session altered.

SQL> SET AUTOT TRACE
SQL> SELECT *
  2  FROM T1, T2
  3  WHERE T1.ID = T2.ID;

95 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1792967693

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  MERGE JOIN         |      |
|   2 |   SORT JOIN         |      |
|   3 |    TABLE ACCESS FULL| T2   |
|*  4 |   SORT JOIN         |      |
|   5 |    TABLE ACCESS FULL| T1   |
------------------------------------

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

   4 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       4545  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         95  rows processed

SQL> SELECT *
  2  FROM T2, T1
  3  WHERE T1.ID = T2.ID;

95 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 412793182

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  MERGE JOIN         |      |
|   2 |   SORT JOIN         |      |
|   3 |    TABLE ACCESS FULL| T1   |
|*  4 |   SORT JOIN         |      |
|   5 |    TABLE ACCESS FULL| T2   |
------------------------------------

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

   4 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       4545  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         95  rows processed

可以看到，RULE最佳化器確實根據FROM列表中的位置來選擇驅動表，FROM列表中最後一個表被作為驅動表。

但是這只是沒有其他優先順序更高的規則存在下的情況，只要在表中建立索引，就會導致優先順序發生變化，從而導致上面的驅動表選擇發生變化：

SQL> CREATE INDEX IND_T1_ID ON T1(ID);

Index created.

SQL> SELECT * 
  2  FROM T2, T1
  3  WHERE T1.ID = T2.ID;

95 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3280696534

-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |
|   2 |   NESTED LOOPS              |           |
|   3 |    TABLE ACCESS FULL        | T2        |
|*  4 |    INDEX RANGE SCAN         | IND_T1_ID |
-------------------------------------------------

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

   4 - access("T1"."ID"="T2"."ID")

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
       4545  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         95  rows processed

根據規則T1應該是驅動表，但是建立索引後，索引掃描的優先順序遠高於全表掃描，因此Oracle選擇T2作為驅動表，將T1的執行計劃變成了索引掃描。

T2上也存在索引，高優先順序的規則對於驅動表的選擇上條件等同，這時RULE最佳化器則又會根據FROM列表來判斷驅動表：

SQL> CREATE INDEX IND_T2_ID ON T2(ID);

Index created.

SQL> SELECT *
  2  FROM T2, T1
  3  WHERE T1.ID = T2.ID;

95 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3621112097

-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2        |
|   2 |   NESTED LOOPS              |           |
|   3 |    TABLE ACCESS FULL        | T1        |
|*  4 |    INDEX RANGE SCAN         | IND_T2_ID |
-------------------------------------------------

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

   4 - access("T1"."ID"="T2"."ID")

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        132  consistent gets
          1  physical reads
          0  redo size
       4545  bytes sent via SQL*Net to client
        558  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         95  rows processed