使用索引的誤區(qū)之五：空值的妙用
2024-07-21 02:06:29
供稿：網(wǎng)友
使用索引的誤區(qū)之五：空值的妙用
并不是因為完全為空的條目不被記錄到索引中，就堅決不能使用空值，相反，有時候合理使用oracle的空值會為我們的查詢帶來幾倍甚至幾十倍的效率提高。
舉個例子，加入有一個表，里面有個字段是“處理時間”，如果沒有處理的事務(wù)，該列就為空，并且在大部分情況下，處理的事務(wù)總是在總記錄數(shù)的10％或者更少，而等待處理的記錄（“處理時間”這列為空）總是絕大多數(shù)的記錄，那么在“等待時間”這列上建立索引，索引中就總是會保存很少的記錄，我們希望的訪問方式是，當(dāng)訪問表中所有代處理的記錄（即10％或者更多的記錄數(shù)目）時，我們希望通過全表掃描的方式來檢索；然而，當(dāng)我們希望訪問已經(jīng)處理的事務(wù)（即5％或者更少的記錄數(shù)目）時，我們希望通過索引來訪問，因為索引中的記錄數(shù)目很少，請看下面的例子：
sql> create table tt as select * from sys.dba_objects;
table created
executed in 0.601 seconds
sql> alter table tt add (t int);
table altered
executed in 0.061 seconds
sql> select count(*) from tt;
count(*)
----------
6131c
executed in 0.01 seconds
sql> update tt set t=1 where owner='demo';
10 rows updated
executed in 0.03 seconds
sql> commit;
commit complete
executed in 0 seconds
sql> select count(*) from tt where owner='demo';
count(*)
----------
10 ――――――――――――――已經(jīng)處理的數(shù)目
executed in 0.08 seconds
s
sql> select count(*) from tt;
count(*)
----------
6131 ――――――――――――――總記錄數(shù)目
executed in 0.01 seconds
下面的查詢因為訪問表中的大多數(shù)記錄（代處理的記錄，即10％以上的記錄數(shù)目），可以看見，它如我們所希望的那樣使用了全表掃描：
select object_name from tt where t is null;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | tt | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("tt"."t" is null)
note: rule based optimization
14 rows selected
executed in 0.05 seconds
下面的查詢因為要訪問表中的少數(shù)記錄，我們希望通過索引來訪問：
select object_name from tt where t=1;
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| id | operation | name | rows | bytes | cost |
--------------------------------------------------------------------
| 0 | select statement | | | | |
|* 1 | table access full | tt | | | |
--------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("tt"."t"=1)
note: rule based optimization
14 rows selected
executed in 0.06 seconds
請注意，這里并沒有如我們所希望的那樣使用索引，而是使用了全表掃描，這里有一個結(jié)論：
建立了索引后，要想在cbo下合理的使用索引，一定要定期的更新統(tǒng)計信息
下面我們分析一下索引，看看有什么效果：
sql> analyze index tt_idx validate structure;
index analyzed
executed in 0 seconds
sql> select lf_rows from index_stats;
lf_rows
----------
10 ――――――――――索引中總共有10行
executed in 0.05 seconds
sql> exec dbms_stats.gather_index_stats('demo','tt_idx');
pl/sql procedure successfully completed
executed in 0.03 seconds
sql> select distinct_keys from user_indexes;
distinct_keys
-------------
1 ――――――――――只有一個鍵值
executed in 0.05 seconds
sql> select * from tt where t is null;
已選擇6121行。
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (full) of 'tt'
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
485 consistent gets
0 physical reads
0 redo size
355012 bytes sent via sql*net to client
4991 bytes received via sql*net from client
410 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6121 rows processed
sql> select * from tt where t=5;
未選定行
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (by index rowid) of 'tt'
2 1 index (range scan) of 'tt_idx' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
964 bytes sent via sql*net to client
372 bytes received via sql*net from client
1 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
sql> select * from tt where t=1;
已選擇10行。
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (by index rowid) of 'tt'
2 1 index (range scan) of 'tt_idx' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1639 bytes sent via sql*net to client
503 bytes received via sql*net from client
2 sql*net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
sql> update tt set t=2 where t=1;
已更新10行。
execution plan
----------------------------------------------------------
0 update statement optimizer=choose
1 0 update of 'tt'
2 1 index (range scan) of 'tt_idx' (non-unique)
statistics
----------------------------------------------------------
0 recursive calls
14 db block gets
1 consistent gets
0 physical reads
3216 redo size
616 bytes sent via sql*net to client
527 bytes received via sql*net from client
3 sql*net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
sql> set autotrace traceonly
sql> update tt set t=3 where t is null;
6121 rows updated.
execution plan
----------------------------------------------------------
0 update statement optimizer=choose
1 0 update of 'tt'
2 1 table access (full) of 'tt'
statistics
----------------------------------------------------------
0 recursive calls
18683 db block gets
80 consistent gets
0 physical reads
2583556 redo size
618 bytes sent via sql*net to client
533 bytes received via sql*net from client
3 sql*net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6121 rows processed
sql>
