數據庫調優：如何優化in操作

2024-07-21 02:32:43

字體：大中小

來源：轉載

供稿：網友

　　今天開發人員讓我優化一個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

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列的索引要受到很多因素的制約了。

上一篇：如何更快地批量數據處理？

下一篇：關于自動PGA管理的進一步探討