高效的SQL(bitmap indexes optimize low cardinality columns)

lovehewenyu發表於2013-01-07

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

3data warehousing

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

 

 

②Experiment (optimizing low cardinality columns)

1、  索引列的資料是低基數的(M20307F20308

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章