高效的 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

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

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

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

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

|    1 |   SORT AGGREGATE                |             |      1 |             |           |

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

|    3 |     BITMAP INDEX FAST FULL SCAN| IDX_BITMAP |        |             |           |

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

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

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

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

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

|    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 |

|*   3 |     BITMAP INDEX SINGLE VALUE| IDX_BITMAP |        |        |             |           |

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

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

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

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

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

|    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 |

|*   3 |     BITMAP INDEX SINGLE VALUE| IDX_BITMAP |        |        |             |           |

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

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

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

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

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

|    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 |

|*   3 |     BITMAP INDEX SINGLE VALUE| IDX_BITMAP |        |        |             |           |

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

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