Oracle最佳化器經歷RBO到CBO的演進過程，其能力已經遠遠超過“條條大路通羅馬”的程度，而進入到智慧化程度。那麼，我們怎麼樣規劃應用架構、設計資料表和寫好SQL，就是我們一直關注的問題。在筆者之前的文章中，一直強調一個概念就是“描述”。我們不僅僅要描述好我們的SQL，更要描述好我們的資料表，以期利於CBO的工作。

約束是資料物件的一個重要特徵，常見的約束有非空、主外來鍵和唯一性約束。約束本質上就是業務邏輯在資料庫層面的一種體現。對Oracle資料庫而言，約束還意味著“資料內部規律的承諾”。在SQL Optimizer工作的時候，約束會讓最佳化器能夠獲取到更好的執行計劃。

筆者過去常常用“not null下count(*)”的案例。本篇介紹主鍵和外來鍵約束對執行計劃影響。

1、環境介紹

我們選擇Oracle 11g進行試驗。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

2、問題提出

在scott使用者下面，有常見的三個資料表bonse、dept和emp。我們希望進行直接連線結果。

SQL> explain plan for select count(*) from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2843340944

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 6 (17)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | HASH JOIN | | 1 | 16 | 6 (17)| 00:00:01 |

| 3 | TABLE ACCESS FULL| BONUS | 1 | 7 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ENAME"="C"."ENAME")

4 - filter("C"."DEPTNO" IS NOT NULL)

17 rows selected

詭異的情況出現了。在SQL裡面，我們明明寫了三個資料表連線。但是執行計劃裡面只出現了兩個資料表連線動作。第三個連線表dept就變成了一個filter謂詞。

更進一步，如果我們就當dept和emp連線的時候，也看到了詭異的情況。

SQL> explain plan for select count(*) from scott.dept b, scott.emp c where b.deptno=c.deptno;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2083865914

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

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

| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("C"."DEPTNO" IS NOT NULL)

14 rows selected

3、使用10046分析過程

兩個表連線，也被踢掉dept了。那麼是什麼原因呢？我們使用10053進行分析，看看Oracle的決策過程。

在開始過程，Oracle是承認裡面包括三個查詢塊。

Registered qb: SEL$1 0x9b35d4 (PARSER)

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$1 nbfros=3 flg=0

fro(0): flg=4 bjn=73183 hint_alias="A"@"SEL$1"

fro(1): flg=4 bjn=73179 hint_alias="B"@"SEL$1"

fro(2): flg=4 bjn=73181 hint_alias="C"@"SEL$1"

其中objn表示的正是我們的三個資料表。

--物件對映資訊

SQL> select object_name from dba_objects where object_id=73181;

OBJECT_NAME

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

EMP

SQL> select object_name from dba_objects where object_id=73179;

OBJECT_NAME

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

DEPT

SQL> select object_name from dba_objects where object_id=73183;

OBJECT_NAME

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

BONUS

在CBO工作之前，SQL語句要進行一系列的變換過程，目的是為了能夠生成更好的執行計劃。這個過程叫做Query Transformation。在生成的Trace檔案中，我們發現Dept是在QT過程中的一個叫JE（Join Elimination）子環節被刪除掉的。

JE: Considering Join Elimination on query block SEL$1 (#0)

*************************

Join Elimination (JE)

*************************

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."DEPT" "B","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "B"."DEPTNO"="C"."DEPTNO"

JE: cfro: EMP objn:73179 col#:8 dfro:DEPT dcol#:8

Query block (0x9b35d4) before join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."DEPT" "B","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "C"."DEPTNO"="B"."DEPTNO"

JE: eliminate table: DEPT (B)

Registered qb: SEL$739CAFA2 0x9b35d4 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "B"@"SEL$1")

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$739CAFA2 nbfros=2 flg=0

fro(0): flg=0 bjn=73183 hint_alias="A"@"SEL$1"

fro(1): flg=0 bjn=73181 hint_alias="C"@"SEL$1"

JE（Join Elimination）是Oracle最佳化器進行QT過程的一個環節。JE的初衷是消除SQL中一些不必要的連線條件。我們在SQL語句中經常想當然的將一些如order by、join新增上去，但是其實都是沒有必要的。JE就是用於消除不必要的連線。

從Trace檔案中，JE過程Oracle認為不需要對B表，也就是dept進行連線。雖然它被寫入在SQL語句裡面。剔除dept之後，最佳化器重新給SQL進行了register過程，定義了新的qb（Query Block）為SEL$739CAFA2。

新的改寫語句為：

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "C"."DEPTNO" IS NOT NULL

在新的qb和SQL語句中，我們就只能看到了A和C表了。也就不難理解Oracle執行計劃裡面沒有dept，是因為在QT過程中進行剔除。這部分內容被替換為deptno is not null。

4、約束

那麼，是不是Oracle錯了呢？

QT（Query Transformation）的動作種類很多，但是每種變換都要遵循一個基本原則就是不改變SQL語句的原意。那麼，Oracle為什麼認為dept不需要連線？

問題還是處在連線條件上。

"B"."DEPTNO"="C"."DEPTNO"

dept表中deptno為資料表的主鍵。而emp中的deptno與dept對應是一個典型的多對一的關係，作為主鍵的dept表中的deptno，唯一性和非空性是有主鍵約束作為保證。

SQL> select constraint_type, table_name, status from dba_constraints where wner='SCOTT' and constraint_name='PK_DEPT';

CONSTRAINT_TYPE TABLE_NAME STATUS

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

P DEPT ENABLED

最佳化器的邏輯是這樣：既然連線鍵deptno在dept資料表中是主鍵，與emp之間是1對n的關係。而且，我們可以看到emp上面的deptno為外來鍵，關聯到dept。

SQL> select constraint_type, table_name, status, constraint_name from dba_constraints where wner='SCOTT' and table_name='EMP';

CONSTRAINT_TYPE TABLE_NAME STATUS CONSTRAINT_NAME

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

R EMP ENABLED FK_DEPTNO

P EMP ENABLED PK_EMP

那麼，Oracle的線索就構成了：emp中每一個deptno要麼為空，要麼肯定和dept中的deptno對應。那麼，SQL語句中要求的Join，只要保證非空就是滿足的了。必然就不需要連線了。

5、驗證

那麼，如果兩個約束被禁用或者不可用。是不是也就消除了關係呢？我們進行試驗即可。

SQL> alter table scott.emp modify constraint fk_deptno disable;

Table altered

SQL> select constraint_type, table_name, status, constraint_name from dba_constraints where wner='SCOTT' and table_name='EMP';

CONSTRAINT_TYPE TABLE_NAME STATUS CONSTRAINT_NAME

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

R EMP DISABLED FK_DEPTNO

P EMP ENABLED PK_EMP

重新生成執行計劃。

SQL> explain plan for select count(*) from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1922630903

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

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

| 0 | SELECT STATEMENT | | 1 | 19 | 6 (17)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 19 | | |

| 2 | NESTED LOOPS | | 1 | 19 | 6 (17)| 00:00:01 |

|* 3 | HASH JOIN | | 1 | 16 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS FULL| BONUS | 1 | 7 | 2 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("A"."ENAME"="C"."ENAME")

6 - access("B"."DEPTNO"="C"."DEPTNO")

19 rows selected

執行計劃已經發生的變化。證明我們的推論正確。

6、結論

進入CBO之後，SQL執行計劃生成過程極其複雜、涉及因素眾多。其中，描述資料表的約束在很多時候，都是SQL巧妙執行計劃的源頭。作為我們開發人員和設計人員，不要小看約束、輕視約束。精巧的描述我們的資料，一定可以獲得好的回報。

約束Constraint引起CBO執行計劃變化一例

相關文章