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