高效的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- The Secrets of Oracle Bitmap IndexesOracleIndex
- Restrictions on Create Bitmap IndexesRESTIndex
- bitmap indexes 的結構分析Index
- Bitmap Indexes and Nulls (224)IndexNull
- Bitmap Indexes (220)Index
- Bitmap Indexes on Partitioned Tables (225)Index
- Bitmap Join Indexes (226)Index
- 有關Bitmap Join Indexes的精彩帖一Index
- 有關Bitmap Join Indexes的精彩帖二Index
- 高效的SQL【Composite Indexes(最佳前導列的選擇)】SQLIndex
- Bitmap Indexes on Index-Organized Tables (232)IndexZed
- 高效的SQL(Function-based Indexes 函式、運算優化思路)SQLFunctionIndex函式優化
- Bitmap Indexes 學習與測試_20091213Index
- 翻譯(九)——Clustered Indexes: Stairway to SQL Server Indexes Level 3IndexAISQLServer
- zt:Cardinality (SQL statements) 最好的解釋SQL
- B-tree Indexes on UROWID Columns for Heap- and Index-Organized Tables (235)IndexZed
- DtypeWarning: Columns () have mixed types. Specify dtype option on import or set low_memory=False.ImportFalse
- Java OptimizeJava
- Optimize Slow VBA Code
- 【MySQL】mysql optimize tableMySql
- 【sql調優】cardinality測試與簡析SQL
- Oracle 11g SQL效能的新特性(二)- Cardinality FeedbackOracleSQL
- CSS columnsCSS
- Cardinality
- Low-Code,一定“low”嗎?
- Cardinality的計算
- BITMAP索引的INLIST ITERATOR與BITMAP OR索引
- How to Optimize PostgreSQL Logical ReplicationSQL
- mysql之 OPTIMIZE TABLE整理碎片MySql
- oracle drop columnsOracle
- 7.40 CARDINALITY
- 詳細解析rand()%(high-low+1)+low
- 高效的SQL(清晰的邏輯重構業務SQL)SQL
- 【DBA】Oracle 11g 針對SQL效能的新特性(二)- Cardinality FeedbackOracleSQL
- oracle sql hint提示_BITMAP CONVERSION FROM ROWIDSOracleSQL
- 書寫高效sqlSQL
- Rebuild IndexesRebuildIndex
- ORACLE INDEXESOracleIndex