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

 

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

| 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-752236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-752236/