資料列not null對索引影響一例

一些容易忽視的因素，往往是我們進行最佳化的方向。

 

Oracle資料列是有not null屬性的，標誌該列是否允許出現空值。如果插入、修改出現空值的情況，Oracle會拒絕當前的DML操作。

 

本質上看，資料列的not null是一種檢驗約束，其效果的時點是在資料列發生變化的時候，如果出現為空的資料，就拒絕操作。此外，not null約束還會影響到最佳化器產生執行計劃。

 

 

很多人都認為只有在where條件後面加入索引列，或者索引列的一部分（組合索引），SQL語句才會走索引。其實這是一種誤解。

 

索引是一種資料庫物件，在執行計劃的生成過程中，給Oracle CBO最佳化器更多的執行路徑選擇，用來找出更好更快的執行路徑。

 

我們構建一個實驗環境。

 

SQL> desc t;

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                         

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER     Y //注意：此時Object_id列是允許為空的，即使資料取值是沒有空值；

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                        

STATUS         VARCHAR2(7)   Y                         

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

 

//構造索引

 

SQL> create index IDX_T_ID on t(object_id);

 

//收集統計量

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

 

PL/SQL procedure successfully completed

 

 

 

首先，我們進行針對object_id的全值搜尋。

 

SQL> select object_id from t;

 

已選擇51355行。

 

已用時間:  00: 00: 00.46

 

執行計劃

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

Plan hash value: 1601196873

 

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

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

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

|   0 | SELECT STATEMENT  |      | 51355 |   250K|   160   (2)| 00:00:02 |

|   1 |  TABLE ACCESS FULL| T    | 51355 |   250K|   160   (2)| 00:00:02 |

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

 

 

統計資訊

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

        251  recursive calls

          0  db block gets

       4138  consistent gets

        712  physical reads

          0  redo size

     743413  bytes sent via SQL*Net to client

      38038  bytes received via SQL*Net from client

       3425  SQL*Net roundtrips to/from client

          9  sorts (memory)

          0  sorts (disk)

      51355  rows processed

 

查詢SQL沒有where條件語句，只是返回object_id列。很自然沒有使用索引，進行的操作是全表掃描。

 

評說：這似乎很正常，也很容易說服人。返回所有資料，where條件中沒有條件。

 

但是，這實際上是存在一定的最佳化空間的。筆者認為：對於二維資料表集合，當我們縱向最佳化（結果集數量）沒有著手點的時候，可以考慮橫向（列）上做文章。

 

思路：where條件中雖然沒有條件專案，但是select後的列表中卻只有一個object_id，而且是存在以object_id為索引列的索引的。

 

如果我們對索引B*樹結構熟悉的話，就知道索引列值實際上都在B*樹葉節點上順序排列，和對應的rowid在一起。SQL語句的條件雖然不具有搜尋索引的條件，但是如果只要求object_id的值，我們只要搜尋一遍樹的葉子節點，就可以獲取到所有的取值了。而不需要再去搜尋資料表了，變兩次搜尋（索引+表）為一次搜尋（索引）。

 

同時，Oracle讀取是以塊Block作為單位。讀取一個資料表塊獲取的object_id資訊肯定大大小於直接讀取一個索引塊獲取的object_id資訊。

 

 

想得很好，但是剛才的執行計劃確實是走了FTS（Full Table Scan），沒有按照我們理想中去執行。原因就在於null值。

 

Oracle是不知道你的資料列有沒有空值，或者以後有沒有空值。如果有空值，構建的索引樹中，就不會有空值對應的資料行資訊。按照我們剛才的思路，就會把取null值的object_id遺漏，Oracle是無論如何不會選取這樣的路徑的。

 

解決的方法：加入not null約束，明確非空資料。

 

看下面實驗。

 

alter table T modify OBJECT_ID not null;

 

SQL> desc t;

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER          //非空設定                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                         

STATUS         VARCHAR2(7)   Y                        

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y    

 

//重新整理buffer cache

SQL> alter system flush buffer_cache;

 

System altered

 

執行相同查詢。

 

SQL> select object_id from t ;

 

已選擇51355行。

 

已用時間:  00: 00: 00.46

 

執行計劃

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

Plan hash value: 1588161578

 

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

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

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

|   0 | SELECT STATEMENT     |          | 51355 |   250K|    28   (4)| 00:00:01

|   1 |  INDEX FAST FULL SCAN| IDX_T_ID | 51355 |   250K|    28   (4)| 00:00:01

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

統計資訊

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

         96  recursive calls

          0  db block gets

       3552  consistent gets

        124  physical reads

          0  redo size

     743413  bytes sent via SQL*Net to client

      38038  bytes received via SQL*Net from client

       3425  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

      51355  rows processed

 

結果，在修改not null屬性之後，果然Oracle選擇了索引搜尋路徑，採用了Index Fast Full Scan的操作。這種操作是可以並行的高效搜尋方法，而且是針對索引列值直接返回結果。

 

其他效能指標中，我們也容易看出這樣做的優勢。

 

 

#	FTS全表掃描	索引路徑
執行時間	00: 00: 00.46	00: 00: 00.46
CPU成本	160	28
邏輯讀consistent reads	4138	3552
物理讀physical reads	712	124
遞迴呼叫recursive call	251	96

 

透過對比，我們發現明顯的效能最佳化趨勢。

 

反思：這裡，最佳化器很明顯是參考了not null資訊，進入了決策因素。Oracle有選擇索引進行搜尋的備選，但是不能確定該列是否可能為null。當確定了not null之後，可以保證所有索引列值都會進入索引生成，在葉子節點儲存，於是就選擇了索引搜尋路徑。