查询Oracle数据库中极其快速的位图块查询（oracle 位图块）
查询Oracle数据库中极其快速的位图块查询
在Oracle数据库中，位图索引是查询大型表中数据极快的一种索引方式。特别在数据仓库的应用中，位图索引以其单独优势成为了大数据表中数据的索引方式。其中的位图块查询 (Bitmapped Block Index Scan)技术，能够为我们带来意料之外的查询性能，而且实现起来也相对容易。
废话不多说，下面来看一下Oracle中使用位图块查询需要关注的地方。
1.准备基础材料
测试数据库：Oracle 11g
测试数据表：大型数据仓库表tb_warehouse，含有5000W条记录
索引类型：位图索引
字段名：age和sales
2.快速理解位图块查询原理
我们都知道，Oracle中的索引主要分为B树索引和位图索引两种，其中位图索引按照列的值划分为不同的块，每个块代表某个列值的占用情况，一个块中记录的是某一列的多个取值中的其中一个，块内记录值都是相同的，每个块都对应一个取值。
图示：
在查询某一列某个取值时，Oracle需要先找到该取值对应的块（block），然后针对该块做一次整块扫描（Bitmapped Block Index Scan），扫描该块的方法是使用位操作（AND/BIT AND OR/BIT OR）将块记录的对应位取出来，做统计计算，统计的内容可能和该块对应的列没有任何关系，得到的结果会进行分组（Group By），分组条件与索引建立时指定的分组条件相同。
图示：
为了更好的理解位图块查询，我们可以通过以下的案例进行实操。
3.实操位图块查询案例
重点：验证bitmapped block index scan的查询效率
1）创建测试表
CREATE TABLE tb_warehouse (id NUMBER, age NUMBER, sales NUMBER, description VARCHAR2(500));
2）对tb_warehouse的age和sales字段建立位图索引
CREATE BITMAP INDEX BITMAP_AGE_INDEX ON tb_warehouse(age);
CREATE BITMAP INDEX BITMAP_SALES_INDEX ON tb_warehouse(sales);
说明：一定要将age和sales字段建立位图索引，这样我们才能够针对age和sales做位图块查询。
3）往tb_warehouse中插入5000W条记录
DECLARE
BEGIN
FOR i IN 1..50000000 LOOP
IF i
INSERT INTO tb_warehouse VALUES(i, TRUNC(DBMS_RANDOM.value(1, 101)), TRUNC(DBMS_RANDOM.value(5, 301)), ‘TEST’);
ELSIF i
INSERT INTO tb_warehouse VALUES(i, TRUNC(DBMS_RANDOM.value(10, 201)), TRUNC(DBMS_RANDOM.value(30, 450)), ‘TEST’);
ELSE
INSERT INTO tb_warehouse VALUES(i, TRUNC(DBMS_RANDOM.value(20, 151)), TRUNC(DBMS_RANDOM.value(60, 600)), ‘TEST’);
END IF;
END LOOP;
COMMIT;
END;
4）验证
SQL> SELECT /*+gather_plan_statistics*/ COUNT(1) FROM tb_warehouse WHERE age = 25 AND sales = 60;
Execution Plan
Plan hash value: 742047386
——————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Cost (%CPU)| A-Time | OMem | 1Mem | Used-Mem |
——————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 11 (0)| 00:00:00.01 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 2 | 11 (0)| 00:00:00.01 | | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | 366K|00:00:00.79 | 2 | 11 (0)| 00:00:00.01 | | | |
| 3 | BITMAP AND | | 1 | | 366K|00:00:00.76 | 2 | 11 (0)| 00:00:00.01 | | | |
| 4 | BITMAP MERGE | BITMAP_SALES_INDEX | 1 | | 4M|00:00:00.56 | 1 | 5 (0)| 00:00:00.01 | | | |
| 5 | BITMAP KEY ITERATION | BITMAP_AGE_INDEX | 1 | | 4M|00:00:00.40 | 1 | 4 (0)| 00:00:00.01 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | tb_warehouse | 526K| 2 | 526K|00:00:00.10 | 1 | 2 (0)| 00:00:00.01 | | | |
——————————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
Plan hash value: 68958809
———————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
———————————————————————————————————————
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 4 | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| tb_warehouse | 313K| 1229K| 2 (0)| 00:00:01 | ROWID | ROWID |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 4 | BITMAP AND | | | | | | | |
| 5 | BITMAP MERGE | BITMAP_SALES_INDEX | | | | | | |
| 6 | BITMAP KEY ITERATION | BITMAP_AGE_INDEX | | | | | | |
———————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(“AGE”=25 AND “SALES”=60)
6 rows selected.
说明：以上查询的作用是根据age=25和sales=60的条件进行查询，查询结果为316,161行。
5）查询分析报告
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’ALLSTATS LAST’));
Plan hash value: 742047386
—————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Cost (%CPU)| A-Time | OMem | 1Mem | Used-Mem |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 316K|00:00:01.68 | 17K| 11 (0)| 00:00:01.68 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.68 | 17K| | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| tb_warehouse | 316K| 1234K| 316K|00:00:01.62 | 17K| 5 (0)| 00:00:01.57 | | | |
| 3 | BITMAP CONVERSION TO ROWIDS | | 1 | | 316K|00:00:00.60 | 2 | 7 (0)| 00:00:01.11 | | | |
| 4 | BITMAP AND | | 1 | | 316K|00:00:00.58 | 2 | 7 (0)| 00:00:01.11 | | | |
| 5 | BITMAP MERGE | BITMAP_SALES_INDEX | 1 | | 1M|00:00:00.14 | 1 | 5 (0)| 00:00:00.14 | | | |
| 6 | BITMAP KEY ITERATION | BITMAP_AGE_INDEX | 1 | | 1M|00:00:00.13 | 1 | 4 (0)| 00:00:00.13 | | | |
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – filter(“AGE”=25 AND “SALES”=60)
统计：
15 recursive calls
0 db block gets
187154 consistent gets
0 physical reads
0 redo size
292 bytes sent via SQL*Net to client
720 bytes received via SQL*Net from client
2 SQL*Net roundtrips to
