null與index

今天在測試過程中遇到一問題, SQL該走Index的,沒走. 加index hint也不行. 描述如下:


1. 建立測試表
create table t1
as
select object_id, object_name from dba_objects;




2. 在object_name列上建立b-tree index
create index idx_t1_name on t1(object_name);




3. 如果我是select object_name from t1, 按理說CBO應該會選擇走Index scan. 但奇怪的是結果走的full table scan.
SQL> set autotrace trace exp
SQL> select object_name from t1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50934 | 3282K| 57 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 50934 | 3282K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------


Note
-----
- dynamic sampling used for this statement


[@more@]
3. 使用index hint想強行走Index, 結果還是full table scan. 我就奇怪了. hint咋個不起做用呢? 鬱悶.
SQL> select /*+ index(t1, idx_t1_name) */ object_name from t1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50934 | 3282K| 57 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 50934 | 3282K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------


Note
-----
- dynamic sampling used for this statement




4. 偶然看了下表結構
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)




NULL列引起我的注意. OBJECT_NAME可以為null !! 而在oracle中單個列上建b-tree Index, null是不會存進Index的( 複合索引可以, 只要整個Index columns不為null ). 那就是說如果有些行的object_name是null, 那走Index取值不是會丟掉object_name為null的行. 那如果我讓object_name not null 呢?


SQL> alter table t1 modify object_name not null;


Table altered.


SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME NOT NULL VARCHAR2(128)




再試一試
SQL> select object_name from t1;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50934 | 3282K| 57 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 50934 | 3282K| 57 (2)| 00:00:01 |
--------------------------------------------------------------------------


結果還是full table scan : (


試試用hint
SQL> select /*+ index(t1, idx_t1_name) */ object_name from t1;


Execution Plan
---------------------------------------------------------- 
Plan hash value: 1352742509 


--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50934 | 3282K| 264 (1)| 00:00:04 |
| 1 | INDEX FULL SCAN | IDX_T1_NAME | 50934 | 3282K| 264 (1)| 00:00:04 |




這回hint 起作用了. 這說明並不是Hint失效, 只是滿足走Index的條件一開始沒有具備. 看來null是個潛在殺手, 得小心防範. 
現在強走index是ok了. 但, 是什麼東西會影響CBO的判斷不走Index呢? 想到統計資訊可能會是原因之一, 於是檢視了一下.


SQL> select index_name, LAST_ANALYZED from user_indexes;
INDEX_NAME LAST_ANALYZED 
------------------------------------------------------------------------------------------ --------------- 
IDX_T1_NAME 01-MAR-18




SQL> select table_name, LAST_ANALYZED from user_tables;


TABLE_NAME LAST_ANALYZED 
------------------------------------------------------------------------------------------ --------------- 
T1




看到剛建的表沒有做過統計. 於是 go to analyze table, 結果如下:


SQL> exec dbms_stats.gather_table_stats('TEST','T1');


PL/SQL procedure successfully completed.


SQL> select table_name, LAST_ANALYZED from user_tables;


TABLE_NAME LAST_ANALYZED 
------------------------------------------------------------------------------------------ --------------- 
T1 01-MAR-18




再來看看執行結果有沒有變化:
SQL> select object_name from t1;


Execution Plan
---------------------------------------------------------- 
Plan hash value: 222950081




------------------------------------------------------------------------------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------------ 
| 0 | SELECT STATEMENT | | 49917 | 1218K| 57 (2)| 00:00:01 | 
| 1 | INDEX FAST FULL SCAN| IDX_T1_NAME | 49917 | 1218K| 57 (2)| 00:00:01 | 
------------------------------------------------------------------------------------


這下終於走Index這條路老 : ) 在Index 中, key value是排序存放的. Index Fast full scan 它是按照block的儲存順序來讀取資料, 並可以一次I/O多塊讀取提高效率( 引數 readdb_file_multiblock_read_count), 但返回的值是沒有排序的. 而
Index full scan會按照Key value順序讀取值, 返回排了序的結果. 所以, 做個order by會是走Index full scan.


SQL> select object_name from t1 order by object_name;


Execution Plan
---------------------------------------------------------- 
Plan hash value: 1352742509




-------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 49917 | 1218K| 249 (1)| 00:00:03 | 
| 1 | INDEX FULL SCAN | IDX_T1_NAME | 49917 | 1218K| 249 (1)| 00:00:03 | 
--------------------------------------------------------------------------------


對於定義為NULL的列，建立點陣圖索引可走索引