"Reading Notes" "Harvest, more than Oracle" bitmap index (2) ____oracle

This experiment discusses the great role of bitmap indexing in ad hoc queries.

Ad hoc query (Multidimensional report query: SELECT * from T where col1=xxx and col2=xxx and col3=xxx ...)

1. The table has a gender, age range, place of birth and other fields. Devote about 100,000 data to prepare for ad hoc queries.

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,  decode ( Ceil (Dbms_random.value (0,2)),  1, ' M ',  2, ' F ') gender,  ceil (dbms_ Random.value (1,50)) location,
  decode (ceil (Dbms_random.value  )),  1, ' child ',
  16  2, ' Young ',
  #  3, ' Middle_age ',  4, ' old ',  rpad (' * '
  , ' * ') 20 From  dual  connect by rownum<=100000;

Table created.

2. Full table scan in case of the query

sys@ orcl>set linesize 1000 sys@ orcl>set autotrace traceonly 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----------------------------------------------------------The 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 "Locat ION "=10 OR" LOCATION "=30") and "age_group" = ' child ')------ Dynamic sampling used for this statement Statistics----------------------------------------------------------  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 via received from client-sql*net
 ET roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 663 rows processed

3. Establish a three-column federated index and three columns are highly duplicate columns. Although a federated index was established, a full table scan was still taking place.

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= ' the child ';


663 rows selected. Execution Plan----------------------------------------------------------The 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 "Locat ION "=10 OR" LOCATION "=30" and "age_group" = ' Child') Note------The dynamic sampling used for this statement Statistics------------------------------------------------ ----------0 Recursive calls 0 db block gets 653 consistent gets 0 physical read 
         s 0 Redo size 13755 bytes sent via sql*net to client 865 bytes received via sql*net from client Sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 663 rows PR
 Ocessed

4. Enforce the federated index, finally understand why the full table scan.

sys@ orcl>select/*+index (t,idx_union) */* 2 from T 3 where gender= ' M ' 4 and location in (1,10,30) 5 and


_group= ' child ';


663 rows selected. Execution Plan----------------------------------------------------------The 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 Sam Pling used for this statement Statistics----------------------------------------------------------0 Recursi
      ve 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-sql*net
 s To/from client 0 sorts (memory) 0 sorts (disk) 663 rows processed

5. This time the protagonist: Bitmap index. Create a bitmap index for each of the three fields in 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= ';


663 rows selected. Execution Plan----------------------------------------------------------The 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 F
          Or this statement Statistics----------------------------------------------------------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 sql*net roundtrips cl
 Ient 0 Sorts (memory) 0 sorts (disk) 663 rows processed

Summarize:

Full table Scan: Cost 138

Combined index: Cost 38152 (TABLE ACCESS by index ROWID), cost 151 (index RANGE SCAN). Even taking an index scan is also higher than full table scanning.

Bitmap index: Cost 9 (full table scan is 15 times times, combined index is 4,239 times times.) If more than one field difference is more obvious. ）