Combine Index Tricky Example

June 5th, 2007 | Categories: Boring | Tags:
Leave a comment | Trackback

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.

  1. SQL> create table items(item_name varchar2(255),contrycode number,price number);
  2. Table created.
  3.  
  4. SQL> begin
  5.   2  for i in 1..100 loop
  6.   3  insert into items select object_name,i,100 from dba_objects;
  7.   4  commit;
  8.   5  end loop;
  9.   6  end;
  10.   7  /
  11. PL/SQL procedure successfully completed.
  12.  
  13. SQL>create index item_idx on items (item_name,contrycode);
  14. Index created.
  15.  
  16. SQL>  analyze table items compute statistics;
  17. Table analyzed.
  18.  
  19. SQL> select count(*) from items where item_name = 'DBA_TABLES' and contrycode in (8,18,38,58,68,88);
  20.  
  21. Execution Plan
  22. ----------------------------------------------------------
  23. Plan hash value: 1904580557
  24. -------------------------------------------------------------------------------
  25. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  26. -------------------------------------------------------------------------------
  27. |   0 | SELECT STATEMENT   |          |     1 |    19 |     8   (0)| 00:00:01 |
  28. |   1 |  SORT AGGREGATE    |          |     1 |    19 |            |          |
  29. |   2 |   INLIST ITERATOR  |          |       |       |            |          |
  30. |*  3 |    INDEX RANGE SCAN| ITEM_IDX |     8 |   152 |     8   (0)| 00:00:01 |
  31. -------------------------------------------------------------------------------
  32. Predicate Information (identified by operation id):
  33. ---------------------------------------------------
  34.    3 - access("ITEM_NAME"='DBA_TABLES' AND ("CONTRYCODE"=8 OR
  35.               "CONTRYCODE"=18 OR "CONTRYCODE"=38 OR "CONTRYCODE"=58 OR
  36.               "CONTRYCODE"=68 OR "CONTRYCODE"=88))
  37. Statistics
  38. ----------------------------------------------------------
  39.           0  db block gets
  40.          18  consistent gets
  41.           0  physical reads
  42.  
  43.  
  44. SQL>  select count(*) from items where item_name = 'DBA_TABLES' and contrycode+0  in (8,18,38,58,68,88);
  45.  
  46. Execution Plan
  47. ----------------------------------------------------------
  48. Plan hash value: 2798938088
  49. ------------------------------------------------------------------------------
  50. | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  51. ------------------------------------------------------------------------------
  52. |   0 | SELECT STATEMENT  |          |     1 |    19 |     3   (0)| 00:00:01 |
  53. |   1 |  SORT AGGREGATE   |          |     1 |    19 |            |          |
  54. |*  2 |   INDEX RANGE SCAN| ITEM_IDX |     8 |   152 |     3   (0)| 00:00:01 |
  55. ------------------------------------------------------------------------------
  56. Predicate Information (identified by operation id):
  57. ---------------------------------------------------
  58.    2 - access("ITEM_NAME"='DBA_TABLES')
  59.        filter("CONTRYCODE"+0=8 OR "CONTRYCODE"+0=18 OR
  60.               "CONTRYCODE"+0=38 OR "CONTRYCODE"+0=58 OR "CONTRYCODE"+0=68 OR
  61.               "CONTRYCODE"+0=88)
  62. Statistics
  63. ----------------------------------------------------------
  64.           0  db block gets
  65.           4  consistent gets
  66.           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.

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
No comments yet.