Oracle約束Constraint對於CBO優化器的作用

進入CBO優化器時代之後，成本計算值決定執行計劃的選取已經成為主流。一條效能良好的執行計劃建立在儘可能“貼切”的統計量基礎上。CBO內部又經歷了兩個時代——IO Cost和CPU Cost，兩者的區別就在於系統統計量（System Statistical）的應用。

RBO時代，執行計劃其實也是有評估的。RBO的執行計劃評定級別不會像CBO成本粒度那麼細，而是15個路徑等級評定。等級編號低的執行計劃比等級編號高的執行計劃更會被選擇到。

在這個過程中，我們其實還是忽略了影響執行計劃的因素，就是約束（Constraint）。Constraint對於資料庫物件很重要，所謂約束，就是建立在資料表、資料列上的規則限制。Constraint的存在目的就是將業務規則融入到資料表設計中。

Constraint確定描述了資料表的一些固有特性，比如非空、外來鍵，就從一個程度上給出了資料表特性的描述。經常性的將Constraint作為一種資料完整性約束的實現，但是對於CBO而言，約束也是搜尋“捷徑”執行計劃的重要資訊來源。從經驗上看，約束能夠給CBO帶來的高效執行計劃作用，是不可忽視的。

本篇介紹幾個常見的業務場景，說明在合理規劃約束的情況下，CBO能夠生成更好地執行計劃。

1、執行環境介紹

我們同時要使用CBO和RBO進行測試過程，選擇Oracle 11g進行測試。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

當前預設使用CBO優化器元件。

SQL> show parameter optimizer

NAME TYPE VALUE

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

optimizer_mode string ALL_ROWS

optimizer_use_sql_plan_baselines boolean TRUE

（篇幅原因，有省略……）

2、“null還是not null”大不一樣

我們在實際設計資料庫中，經常會忽略欄位非空設定。不少朋友和開發團隊對於這個細節不以為然，認為這個設定就是會影響到插入過程。一些朋友認為：在應用層面驗證一下就可以了。但是實際上，null與not null，大不一樣！有很多方面的差異和問題，純應用層面驗證是不能解決問題的。

筆者從效能優化器角度，介紹一下忽視not null效果的問題。我們首先建立實驗資料表T。

--資料表T

SQL> create table t as select * from dba_objects;

Table created

--其他用途索引

SQL> create index idx_t_id on t(object_id);

Index created

--統計量收集

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

還是我們經常設定的場景，就是沒有where條件的count動作。

SQL> explain plan for select count(*) from t;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | 294 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 75609 | 294 (1)| 00:00:04 |

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

9 rows selected

很正常的執行計劃，因為需要檢索所有的資料行記錄，檢索資料表所有的記錄是比較直觀的想法。這個FTS執行計劃成本值294。我們修改一下索引列object_id的屬性，將其從原先的null設定為not null。

SQL> alter table t modify object_id not null;

Table altered

SQL> explain plan for select count(*) from t;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3570898368

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

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

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

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 75609 | 48 (3)| 00:00:01 |

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

9 rows selected

SQL語句中沒有where條件，選取的count(*)也沒有直接object_id關係。但是執行計劃中出現了索引物件，最重要的是執行計劃從原來的294下降到48。

觀察這個執行計劃，路徑中出現了Index Fast Full Scan動作，而且沒有回表動作。Oracle選擇這樣的路徑思路是這樣的：object_id是索引列，所有object_id的取值均在葉子節點上。關鍵難點在於空置null，Oracle中null值不會進入單鍵值索引物件葉子節點。這也就是為什麼Oracle在object_id不設定為not null時不走索引路徑的原因。

Oracle在取巧！在CBO時代，對索引路徑的選擇是一個非常出巧的地方。RBO時代，十五種等級規則，實際就意味著十五種路徑方式。我們看RBO時代，有沒有這樣的策略。

SQL> explain plan for select /*+rule*/count(*) from t;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2966233522

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS FULL| T |

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

Note

-----

- rule based optimizer used (consider using cbo)

13 rows selected

從上面情況看，RBO時代沒有這樣的“取巧”過程。

約束not null和null帶給我們的不僅僅是資料約束的保證，從這個例子上，可以看到not null還可以帶來一些高效的執行計劃，實現效能上的提升。

3、主鍵列的Group By

單純使用Group By是沒有意義的，一般都是伴隨著如count、sum等聚合函式方法。另外一個關於Group By的特性是：空值null也會被進行Group By。

主鍵Primary Key的特性是唯一和非空，如果對其進行Group By，每個物件的運算元量就是1。這個過程其實也是不需要進行真正操作的。

下面我們進行測試。

SQL> explain plan for select empno, count(*) from scott.emp group by empno;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1749432681

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

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

| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |

| 1 | SORT GROUP BY NOSORT| | 14 | 56 | 1 (0)| 00:00:01 |

| 2 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |

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

9 rows selected

兩個特點，一個是執行計劃中首先進行了Index Full Scan對主鍵索引進行掃描，第二個是進行了Sort Group By Nosort。Sort Group是早期Hash Group出現之前的Group動作。而Sort Group By Nosort是做Sort Group By的動作，但是根本就不會進行排序、也不需要進行Group By。原因是什麼？就是由於操作Index Full Scan獲取到的結果集合是掃描葉子節點，本身就是有序的，不需要進行排序。不需要進行Group By的原因是每個葉子節點Group By的結果只有一個，就是1。

這種取巧的執行計劃，我們在RBO上是看不到的。

SQL> explain plan for select /*+rule*/empno, count(*) from scott.emp group by empno;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 15469362

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | SORT GROUP BY | |

| 2 | TABLE ACCESS FULL| EMP |

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

Note

-----

- rule based optimizer used (consider using cbo)

13 rows selected

我們使用RBO的時候，Oracle進行Group By主鍵和其他列操作是一樣的。

4、空值null Group

一般情況下，我們是不會遇到主鍵Group By的情況的。大多數的Group By都是有限Distinct值的。這種時候，在CBO情況下避免不了進行全表掃描。即使Group By列中建立了索引，索引也不會用到。

SQL> drop table t purge;

Table dropped

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_owner on t(owner);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

此時執行計劃如下：

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 47235625

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

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

| 0 | SELECT STATEMENT | | 32 | 192 | 298 (2)| 00:00:04 |

| 1 | HASH GROUP BY | | 32 | 192 | 298 (2)| 00:00:04 |

| 2 | TABLE ACCESS FULL| T | 75609 | 443K| 294 (1)| 00:00:04 |

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

9 rows selected

底層路徑沒有去走索引路徑，根本原因在於object_id是null，允許為空。Group By操作是允許空值進入的。如果系統業務上恰恰不允許object_id設定為非空，怎麼辦？

問題的策略在於將空值object_id也進入到索引結構。此處可以使用常陣列合索引方法。

SQL> create index idx_t_owner_cmp on t(owner,0);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> explain plan for select owner, count(*) from t group by owner;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 685106933

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

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

| 0 | SELECT STATEMENT | | 32 | 192 | 61 (9)| 0

| 1 | HASH GROUP BY | | 32 | 192 | 61 (9)| 0

| 2 | INDEX FAST FULL SCAN| IDX_T_OWNER_CMP | 75609 | 443K| 57 (2)| 0

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

9 rows selected

執行成本值從原有的近300，下降到當前的61。

5、外來鍵“想當然”

外來鍵Foreign Key是維持參照完整性的重要手段。對CBO而言，Foreign Key意味著取值規則上的差異，也會影響落實到執行計劃中。

首先建立實驗環境。

SQL> create table t_master (id number, mas_name varchar2(100));

Table created

SQL> create table t_child (cid number, id number, chi_name varchar2(100));

Table created

SQL> alter table t_master add constraint pk_t_master primary key (id);

Table altered

SQL> alter table t_child add constraint pk_t_child primary key (cid);

Table altered

SQL> alter table t_child add constraint fk_t_child_master foreign key (id) references t_master(id);

Table altered

--收集統計量

SQL> exec dbms_stats.gather_table_stats(user, 'T_MASTER', cascade => true);

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user, 'T_CHILD', cascade => true);

PL/SQL procedure successfully completed

我們研究一個SQL語句，討論查詢不存在主表中的子表記錄。

SQL> explain plan for select * from t_child where id not in (select id from t_master);

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1412281931

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

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

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

|* 1 | HASH JOIN ANTI SNA| | 1 | 91 | 3 (34)| 00:00:01

| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01

| 3 | INDEX FULL SCAN | PK_T_MASTER | 1 | 13 | 0 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

1 - access("ID"="ID")

15 rows selected

Not in的標準過程是使用反連線動作，上面的執行計劃也證明了這點。但是，這個執行計劃沒有考慮外來鍵因素。在外來鍵約束中，外來鍵列上是不可能出現不是空值的非主鍵值。Not in最大的警惕點在於是否有空。

我們調節外來鍵列的屬性約束，取得新的執行計劃。

SQL> alter table t_child modify id not null;

Table altered

SQL> explain plan for select * from t_child where id not in (select id from t_master);

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2639974602

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

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

| 0 | SELECT STATEMENT | | 1 | 78 | 0 (0)| |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(NULL IS NOT NULL)

14 rows selected

成本值是0！執行計劃中的確有對子表的FTS動作，但是上面的那個filter，引用了一個條件：null is not null——永假式。

SQL> select 1 from dual where NULL IS NOT NULL;

1

----------

Oracle在承認子表id非空之後，直接判定這個SQL是不會有任何結果的。索性執行給一個永假式，返回0行記錄。

這種取巧路徑在RBO時代，也是不存在的。

SQL> explain plan for select /*+rule*/* from t_child where id not in (select id from t_master);

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2659151261

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

|* 1 | FILTER | |

| 2 | TABLE ACCESS FULL| T_CHILD |

|* 3 | INDEX UNIQUE SCAN| PK_T_MASTER |

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

Predicate Information (identified by operation id):

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

1 - filter( NOT EXISTS (SELECT 0 FROM "T_MASTER" "T_MASTER" WHERE

"ID"=:B1))

3 - access("ID"=:B1)

Note

-----

PLAN_TABLE_OUTPUT

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

- rule based optimizer used (consider using cbo)

21 rows selected

6、結論

執行計劃是SQL語句執行效率的命脈。如何生成更快速的執行計劃，不僅是Oracle，更是所有優化器追求的方向目標。在CBO時代，Oracle放棄了RBO時候簡單15個等級的策略，應用成本Cost度量執行計劃策略，統計量+核心公式構成了優化器比較基礎。

除此之外，我們說資料庫物件的描述，特別是約束起到更加重要的作用。本篇中，我們介紹了幾個關鍵場景下，約束對於執行計劃的作用。從直觀上看，約束描述可以幫助CBO找到各種“取巧”路徑。這實際上就反映了約束對於資料庫的描述作用，生成高效執行計劃。

對於開發設計人員，這個結論很重要。實際工作中，不少開發人員忽視約束的作用。不願意進行欄位非空討論，不進行適度外來鍵設計，這些都是需要我們借鑑的方面。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-1063998/，如需轉載，請註明出處，否則將追究法律責任。

Oracle約束Constraint對於CBO優化器的作用

相關文章