【讀書筆記】【收穫，不止Oracle】位元影像索引（2）____Oracle

本次實驗討論位元影像索引在即席查詢中發揮的巨大作用。

即席查詢（多維度報表查詢：select * from t where col1=XXX and col2=XXX and col3=XXX...)

1.構造表，該表有性別，年齡範圍，出生地等欄位。投入約10萬條的資料，為即席查詢做準備。

SYS@ orcl>drop table t purge;Table dropped.SYS@ orcl>create table t (  2  name_id,  3  gender not null,  4  location not null,  5  age_group not null,  6  data  7  )  8  as   9  select rownum,  10  decode(ceil(dbms_random.value(0,2)),  11  1,'M',  12  2,'F')gender,  13  ceil(dbms_random.value(1,50)) location,  14  decode(ceil(dbms_random.value(0,3)),  15  1,'child',  16  2,'young',  17  3,'middle_age',  18  4,'old'),  19  rpad('*',20,'*')  20  from dual  21  connect by rownum<=100000;Table created.

2.全表掃描的情況下的查詢

SYS@ orcl>set linesize 1000SYS@ orcl>set autotrace traceonlySYS@ orcl>select *   2  from t  3  where gender='M'  4  and location in (1,10,30)  5  and age_group='child';663 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |   575 | 27025 |   138   (3)| 00:00:02 ||*  1 |  TABLE ACCESS FULL| T    |   575 | 27025 |   138   (3)| 00:00:02 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR              "LOCATION"=30) AND "AGE_GROUP"='child')Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets        653  consistent gets          0  physical reads          0  redo size      13755  bytes sent via SQL*Net to client        865  bytes received via SQL*Net from client         46  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        663  rows processed

3.建立三個列的聯合索引，三個列都為高度重複的列。雖然建立了聯合索引，但仍然走的是全表掃描。

SYS@ orcl>create index idx_union on t(gender,location,age_group);Index created.SYS@ orcl>select *   2  from t  3  where gender='M'  4  and location in (1,10,30)  5  and age_group='child';663 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |   575 | 27025 |   138   (3)| 00:00:02 ||*  1 |  TABLE ACCESS FULL| T    |   575 | 27025 |   138   (3)| 00:00:02 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR              "LOCATION"=30) AND "AGE_GROUP"='child')Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets        653  consistent gets          0  physical reads          0  redo size      13755  bytes sent via SQL*Net to client        865  bytes received via SQL*Net from client         46  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        663  rows processed

4.強制執行聯合索引，終於明白為什麼走全表掃描了。

SYS@ orcl>select /*+index(t,idx_union)*/*  2  from t  3  where gender='M'  4  and location in (1,10,30)  5  and age_group='child';663 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 306189815------------------------------------------------------------------------------------------| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |           |   575 | 27025 | 38152   (1)| 00:07:38 ||   1 |  INLIST ITERATOR             |           |       |       |            |          ||   2 |   TABLE ACCESS BY INDEX ROWID| T         |   575 | 27025 | 38152   (1)| 00:07:38 ||*  3 |    INDEX RANGE SCAN          | IDX_UNION | 49691 |       |   151   (2)| 00:00:02 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("GENDER"='M' AND ("LOCATION"=1 OR "LOCATION"=10 OR "LOCATION"=30)              AND "AGE_GROUP"='child')Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets        564  consistent gets          0  physical reads          0  redo size      34169  bytes sent via SQL*Net to client        865  bytes received via SQL*Net from client         46  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        663  rows processed

5.本次的主角：位元影像索引。在gender,location,age_group三個欄位分別建位元影像索引。

SYS@ orcl>create bitmap index gender_idx on t(gender);Index created.SYS@ orcl>create bitmap index location_idx on t(location);Index created.SYS@ orcl>create bitmap index age_group_idx on t(age_group);Index created.SYS@ orcl>select *   2  from t   3  where gender='M'  4  and location in (1,10,30)  5  and age_group='41 and over'; 663 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 687389132-----------------------------------------------------------------------------------------------| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |               |   575 | 27025 |     9   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID  | T             |   575 | 27025 |     9   (0)| 00:00:01 ||   2 |   BITMAP CONVERSION TO ROWIDS |               |       |       |            |          ||   3 |    BITMAP AND                 |               |       |       |            |          ||*  4 |     BITMAP INDEX SINGLE VALUE | GENDER_IDX    |       |       |            |          ||   5 |     BITMAP OR                 |               |       |       |            |          ||*  6 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          ||*  7 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          ||*  8 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |            |          ||*  9 |     BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX |       |       |            |          |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("GENDER"='M')   6 - access("LOCATION"=1)   7 - access("LOCATION"=10)   8 - access("LOCATION"=30)   9 - access("AGE_GROUP"='child')Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets        426  consistent gets          0  physical reads          0  redo size      34169  bytes sent via SQL*Net to client        865  bytes received via SQL*Net from client         46  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        663  rows processed

總結：

全表掃描：花費138

複合式索引：花費38152（TABLE ACCESS BY INDEX ROWID），花費151（INDEX RANGE SCAN）。即使走索引掃描也比全表掃描高。

位元影像索引：花費9（全表掃描是其15倍，複合式索引是其4239倍。如果多個欄位差別更加明顯。）