26.读书笔记收获不止Oracle之 位图索引即席查询

26.读书笔记收获不止Oracle之 位图索引即席查询

Drop table t purge;

create table t

    (name_id,

    gender not null,

    location not null,

    age_group not null,

    data)

    as

    selectrownum,decode(ceil(dbms_random.value(0,2)),

                          1,'m',

                           2,'f')gender,

                   ceil(dbms_random.value(1,50)) location,

                           decode(ceil(dbms_random.value(0,3)),

                           1,'child',

                           2,'young',

                           3,'middle_age',

                           4,'old'),

                   rpad('*',20,'*')

   from dual

   connect byrownum<=100000;

1.  无索引进行查询

Set linesize 1000

Set autotrace traceonly

SQL> select * from  t wheregender='m'  and location in (1,10,30) andage_group='child';

 

656 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |        | 1013 | 39507 |   171   (1)| 00:00:01 |

|*  1|  TABLE ACCESS FULL| T       |  1013 | 39507 |   171  (1)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1- filter("AGE_GROUP"='child' AND "GENDER"='m' AND("LOCATION"=1 OR

                 "LOCATION"=10 OR"LOCATION"=30))

 

 

Statistics

----------------------------------------------------------

             1 recursive calls

             0  dbblock gets

           652  consistent gets

             0 physical reads

             0  redosize

     16591  bytes sent via SQL*Net toclient

      1024  bytes received via SQL*Netfrom client

            45 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

           656  rows processed

 

2.  建立联合索引

建立三个列的联合索引

SQL> create index idx_union ont(gender,location,age_group);

Index created.

SQL>  select * from  t where gender='m'  and location in (1,10,30) andage_group='child';

 

656 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time        |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |        |  680 | 26520 |   171   (1)| 00:00:01 |

|*  1|  TABLE ACCESS FULL| T       |  680 | 26520 |   171   (1)| 00:00:01 |

--------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   1- filter("AGE_GROUP"='child' AND "GENDER"='m' AND("LOCATION"=1 OR

                 "LOCATION"=10 OR"LOCATION"=30))

 

 

Statistics

----------------------------------------------------------

             1 recursive calls

             0  dbblock gets

           652  consistent gets

             0 physical reads

             0  redosize

     16591  bytes sent via SQL*Net toclient

      1024  bytes received via SQL*Netfrom client

            45 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

           656  rows processed

还是走的全表扫描，进行强制走索引如下：

3.  强制走索引

SQL> select /*+index(t,idx_union)*/ * from  t where gender='m'  and location in (1,10,30) andage_group='child';

 

656 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 886844991

 

--------------------------------------------------------------------------------------------------

| Id | Operation                                | Name    | Rows  | Bytes | Cost (%CPU)| Time      |

--------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT                     |                 |   680 | 26520 |   525  (0)| 00:00:01 |

|   1|  INLIST ITERATOR                       |                 |      |      |           |  |

|   2|   TABLE ACCESS BY INDEX ROWID BATCHED|T  |  680 | 26520 |   525   (0)| 00:00:01 |

|*  3|    INDEX RANGE SCAN                        | IDX_UNION |   680 |  |     5  (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   3- access("GENDER"='m' AND ("LOCATION"=1 OR"LOCATION"=10 OR "LOCATION"=30) AND

                 "AGE_GROUP"='child')

 

 

Statistics

----------------------------------------------------------

             1 recursive calls

             0  dbblock gets

           581  consistent gets

             6 physical reads

             0  redosize

     36681  bytes sent via SQL*Net toclient

      1024  bytes received via SQL*Netfrom client

            45 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

           656  rows processed

强制走索引的代价比用全表扫描高很多的。主要集中在回表这个阶段。

 

4.  位图索引

看看走位图索引

SQL> create bitmap index gender_idx ont(gender);

Index created.

SQL> create bitmap index location_idx ont(location);

Index created.

SQL> create bitmap index age_group_idxon t(age_group);

Index created.

SQL> select * from  t wheregender='m'  and location in (1,10,30) andage_group='child';

656 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3416549716

 

-----------------------------------------------------------------------------------------------------

| Id | Operation                               | Name          | Rows | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT                    |                       |         680| 26520 |    118   (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED| T      |         680| 26520 |    118   (0)| 00:00:01 |

|   2|   BITMAP CONVERSION TO ROWIDS              |                       |             |             |                   |         |

|   3|    BITMAP AND                                   |                       |             |             |                   |         |

|   4|     BITMAP OR                                   |                       |             |             |                   |         |

|*  5|      BITMAP INDEX SINGLE VALUE        |LOCATION_IDX  |               |            |                   |         |

|*  6|      BITMAP INDEX SINGLE VALUE        |LOCATION_IDX  |               |            |                   |         |

|*  7|      BITMAP INDEX SINGLE VALUE        |LOCATION_IDX  |               |            |                   |         |

|*  8|     BITMAP INDEX SINGLE VALUE         | AGE_GROUP_IDX |               |            |                   |         |

|*  9|     BITMAP INDEX SINGLE VALUE         | GENDER_IDX    |              |             |                   |         |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   5- access("LOCATION"=1)

   6- access("LOCATION"=10)

   7- access("LOCATION"=30)

   8- access("AGE_GROUP"='child')

   9- access("GENDER"='m')

 

 

Statistics

----------------------------------------------------------

             1 recursive calls

             0  dbblock gets

           443  consistent gets

             5 physical reads

             0  redosize

     36681  bytes sent via SQL*Net toclient

      1024  bytes received via SQL*Netfrom client

            45 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

           656  rows processed

代价只有118，比全表扫描代价要小很多。