玉面飞龙的BLOG

Combine index is usually good for equally(”ColA=…” and “ColB=..”… ) operations,but some times not good for in operations.( “ColA=…” and “ColB in (….) and …”).

And it needs some tricky to get best performance.Below is a typical example.Of course,Just Case by Case.You need to under standby your data.

SQL> create table items(item_name varchar2(255),contrycode number,price number);
Table created.

SQL> begin
2 for i in 1..100 loop
3 insert into items select object_name,i,100 from dba_objects;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL>create index item_idx on items (item_name,contrycode);
Index created.

SQL> analyze table items compute statistics;
Table analyzed.

SQL> select count(*) from items where item_name = ‘DBA_TABLES’ and contrycode in (8,18,38,58,68,88);

Execution Plan
———————————————————-
Plan hash value: 1904580557
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 19 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| ITEM_IDX | 8 | 152 | 8 (0)| 00:00:01 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
3 - access(”ITEM_NAME”=’DBA_TABLES’ AND (”CONTRYCODE”=8 OR
“CONTRYCODE”=18 OR “CONTRYCODE”=38 OR “CONTRYCODE”=58 OR
“CONTRYCODE”=68 OR “CONTRYCODE”=88))
Statistics
———————————————————-
0 db block gets
18 consistent gets
0 physical reads

SQL> select count(*) from items where item_name = ‘DBA_TABLES’ and contrycode+0 in (8,18,38,58,68,88);

Execution Plan
———————————————————-
Plan hash value: 2798938088
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | INDEX RANGE SCAN| ITEM_IDX | 8 | 152 | 3 (0)| 00:00:01 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 - access(”ITEM_NAME”=’DBA_TABLES’)
filter(”CONTRYCODE”+0=8 OR “CONTRYCODE”+0=18 OR
“CONTRYCODE”+0=38 OR “CONTRYCODE”+0=58 OR “CONTRYCODE”+0=68 OR
“CONTRYCODE”+0=88)
Statistics
———————————————————-
0 db block gets
4 consistent gets
0 physical reads

All right.Filter on combine index(ITEM_IDX) ’s second column bring lowest LIO.

By “plus 0″,CBO avoid access the second column for each in-inlist value and don’t need to scan index root block & branch block many times.

That’s why “plus 0″ take less LIO.

Good Example. And Smart tricky.