高效的SQL（bitmap indexes optimize low cardinality columns）

高效的SQL（bitmap indexes optimize low cardinality columns）

①Bitmap indexes situations

1、 indexed columns have low cardinality

2、 redo-only or not subject to significant modification by DML

3、data warehousing

4、bitmap indexes can include keys that consist entirely of null values

②Experiment (optimizing low cardinality columns)

1、索引列的資料是低基數的（M為20307，F為20308）

2、統計表tab_bitmap、統計為M、統計為F、統計為NULL資料量均走索引

doudou@TEST> create table tab_bitmap as select decode(mod(rownum,2),0,'M','F') gender , all_objects.object_id from all_objects;

Table created.

doudou@TEST> select count(*) from tab_bitmap;

COUNT(*)

----------

40615

doudou@TEST> create bitmap index idx_bitmap on tab_bitmap(gender);

Index created.

doudou@TEST> select index_name,index_type,table_name from user_indexes where table_name=upper('tab_bit');

INDEX_NAME INDEX_TYPE TABLE_NAME

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

IDX_BIT BITMAP TAB_BIT

doudou@TEST> set autot on

統計表tab_bitmap資料量

doudou@TEST> select count(*) from tab_bitmap;

COUNT(*)

----------

40615

Execution Plan

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

Plan hash value: 3693982118

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

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

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | BITMAP CONVERSION COUNT | | 41205 | 5 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

Statistics

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

5 recursive calls

0 db block gets

68 consistent gets

2 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

統計為M資料量

doudou@TEST> select count(*) from tab_bitmap where gender=upper('m');

COUNT(*)

----------

20307

Execution Plan

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

Plan hash value: 61088094

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

| 2 | BITMAP CONVERSION COUNT | | 20603 | 41206 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("GENDER"='M')

Note

-----

- dynamic sampling used for this statement

Statistics

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

7 recursive calls

0 db block gets

67 consistent gets

0 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

統計為F資料量

doudou@TEST> select count(*) from tab_bitmap where gender=upper('f');

COUNT(*)

----------

20308

Execution Plan

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

Plan hash value: 61088094

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

| 2 | BITMAP CONVERSION COUNT | | 20603 | 41206 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("GENDER"='F')

Note

-----

- dynamic sampling used for this statement

Statistics

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

7 recursive calls

0 db block gets

68 consistent gets

0 physical reads

0 redo size

413 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

統計為NULL資料量

doudou@TEST> select count(*) from tab_bitmap where gender is null;

COUNT(*)

----------

0

Execution Plan

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

Plan hash value: 61088094

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

| 2 | BITMAP CONVERSION COUNT | | 1 | 2 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("GENDER" IS NULL)

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

67 consistent gets

0 physical reads

0 redo size

410 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-752236/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（bitmap indexes optimize low cardinality columns）

相關文章