如何優化in操作

今天開發人員讓我優化一個sql：

Select Item.*
From Item Item
Where Item.Item_Id in (15056, 14697);

這個item表很大，sql執行起來很慢。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12795 Card=2 Bytes=1
316)

1 0 TABLE ACCESS (FULL) OF 'ITEM' (Cost=12795 Card=2 Bytes=131
6)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
134234 consistent gets
26568 physical reads
2184 redo size
3336 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)
2 rows processed

在這個例子中，item_id是item表的主鍵。這裡慢的原因是in操作不能用到索引，做全表掃描，但為什麼不能用索引呢？
實際上，oracle在處理in的時候是轉換成or進行處理的，如：
a in (1,2)相當於 a=1 or a=2
而對於b*tree索引來說，對於or操作是不會用到索引的。知道原因，下面就要找出解決方法了。

方法一、從b*tree索引著手
這種方法就是想辦法讓查詢可以走b*tree索引。要走btree索引，就要去掉or操作，所以把查詢改成一個等價的sql：

SQL> Select Item.* From Item Item Where Item.Item_Id in (14697)
2 union all
3 Select Item.* From Item Item Where Item.Item_Id in (15056);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=1316)
1 0 UNION-ALL
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=3 Card=1 B
ytes=658)

3 2 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=2 Card
=1)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=3 Card=1 B
ytes=658)

5 4 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=2 Card
=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
3334 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)
2 rows processed

從輸出結果可以看出，sql已經可以有索引了，效率也得到大幅提高。但這種辦法有缺陷：
如果in list的個數不確定，且如果in list較多的話，拼sql都是一個問題。我這個應用就是這種情況。

方法二、從點陣圖方式著手
我們知道，點陣圖索引是可以被or操作使用的，我們可以根據這一特點先用btree索引查詢資料，然後進行點陣圖轉換、聯合。
oracle給我們提供了一個hint來解決這個問題：use_concat。原sql修改為：

SQL> Select /*+ use_concat */ Item.* From Item Item Where Item.Item_Id in (15056,14697);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2 Bytes=1316)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=6 Card=2 Byt
es=1316)

2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=2)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
3336 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)
2 rows processed

從結果可以看到，oracle先通過主鍵索引分別對兩個id進行查詢，然後轉換成點陣圖進行合併，這樣效率就得到提供，對原sql的改動也很小，基本滿足了要求。
本文所有結果都是基於linux as 3+ oracle9204平臺。

這個轉換受_b_tree_bitmap_plans決定。如果該引數為true，則可進行轉換，否則不進行轉換。而8i裡，該引數預設為false，所以只有存在bitmap索引時才會考慮將B樹索引轉換為bitmap; 而到了9i裡，該引數預設為true了，則對任何索引都有可能進行bitmap轉換。

順便提一下，今天在itpub有帖子講到in能否用到btree索引問題，我這裡總結一下：
1、如果in list是具體的term，如id in (1,1,2)之類的話，是不會用到b*tree的
2、如果in list是有一個子查詢得到的，如id in (select id from t2)，那麼這個查詢實際兩表關聯。那麼能否用到id列的索引要受到很多因素的制約了。

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