首页 > 代码库 > Oracle 优化——位图、等索引介绍
Oracle 优化——位图、等索引介绍
一、位图索引
我将使用一个例子，来描述位图索引的存储，并分析它的优点。
Table :Loans 放贷信息
| ID | userId | 行业投向 | 币种 | 证件类型 | 还本付息方式 | 状态 |
| 1 | 1 | 农业 | 人民币 | 身份证 | 等额本息还款法 | 已上报 |
| 2 | 2 | 农业 | 人民币 | 身份证 | 等本还款法 | 未上报 |
| 3 | 1 | 工业 | 人民币 | 护照 | 按季计息到期还本法 | 已上报 |
| 4 | 2 | 个体 | 人民币 | 身份证 | 等本还款法 | 已上报 |
| 5 | 5 | 其他 | 人民币 | 身份证 | 按月计息到期还本法 | 未上报 |
我对行业投向，和还本付息方式添加了位图索引
create bitmap index index_投向 on loans(行业投向);
create bitmap index index_还本付息方式 on loans(还本付息方式);
那么它会这么对位图索引进行存储：当前列的每一种值，存放在一个块中，通过0和1来标示改rownumber是否存在改值。
行业投向位图索引/还本付息方式
| 值/行 | 第一行 | 第二行 | 第三行 | 第四行 |
| 农业 | 1 | 1 | 0 | 0 |
| 工业 | 0 | 0 | 1 | 0 |
| 个体 | 0 | 0 | 0 | 1 |
| 其他 | 0 | 0 | 0 | 0 |
| 值/行 | 第一行 | 第二行 | 第三行 | 第四行 |
| 等额本息还款法 | 1 | 0 | 0 | 0 |
| 等本还款法 | 0 | 1 | 0 | 0 |
| 按季计息到期还本法 | 0 | 0 | 1 | 0 |
| 按月计息到期还本法 | 0 | 0 | 0 | 1 |
有图可以看出， 农业、工业、个体都各以一个块来存放 所有列“自己是否为真”。
所以暂时可以得出：
1、位图索引，必须创建在“仅仅几种值的情况”。
如果在低重复度的列上创建位图索引是很恐怖的，他将创建N多个块来存储。不论创建，还是查询，都是不聪明的。
2、位图索引，不适合放在常修改的字段列（如状态列）容易发生死锁。
位图索引死锁情况举例
--SESSION 1（持有者）DELETE FROM LOANS WHERE 行业投向=‘农业‘ AND status=1;---SESSION 2(其他会话) 插入带‘农业‘的记录就立即被阻挡，以下三条语句都会被阻止insert into loans(Id,投向.....) values (1,‘农业‘,....);update t set 投向=‘工业‘ WHERE id=25;delete from loans WHERE 行业投向=‘农业‘;--以下是可以进行不受阻碍的insert into loans(Id,投向.....) values (1,‘工业‘,....);delete from t where gender=‘工业‘ ;UPDATE T SET status=‘aa‘ WHERE ROWID NOT IN ( SELECT ROWID FROM T WHERE 投向=‘工业‘ ) ; --update只要不更新位图索引所在的列即可
3、索引通过 比特位 存储01，来标示真假，占用内存很小，检索效率极高。
count(*) where 行业投向 = 农业，效率是很高的，
当采集平台完成这些金融数据采集后，金融监管部门要对信息进行分析、统计，形成报表。有位图索引效率是很好的。
具体案例
1 /* 2 总结：本质原因：其实就是位图索引存放的是0,1的比特位，占字节数特别少。 3 */ 4 5 --位图索引跟踪前准备 6 drop table t purge; 7 set autotrace off 8 create table t as select * from dba_objects; 9 insert into t select * from t; 10 insert into t select * from t; 11 insert into t select * from t; 12 insert into t select * from t; 13 insert into t select * from t; 14 insert into t select * from t; 15 update t set object_id=rownum; 16 commit; 17 18 --观察COUNT(*)全表扫描的代价 19 set autotrace on 20 set linesize 1000 21 select count(*) from t; 22 23 24 25 ------------------------------------------ 26 COUNT(*) 27 ---------- 28 4684992 29 执行计划 30 ---------------------------------------------------------- 31 Plan hash value: 2966233522 32 33 ------------------------------------------------------------------- 34 | Id | Operation | Name | Rows | Cost (%CPU)| Time | 35 ------------------------------------------------------------------- 36 | 0 | SELECT STATEMENT | | 1 | 20420 (11)| 00:04:06 | 37 | 1 | SORT AGGREGATE | | 1 | | | 38 | 2 | TABLE ACCESS FULL| T | 294M| 20420 (11)| 00:04:06 | 39 ------------------------------------------------------------------- 40 统计信息 41 ---------------------------------------------------------- 42 0 recursive calls 43 0 db block gets 44 66731 consistent gets 45 0 physical reads 46 0 redo size 47 426 bytes sent via SQL*Net to client 48 415 bytes received via SQL*Net from client 49 2 SQL*Net roundtrips to/from client 50 0 sorts (memory) 51 0 sorts (disk) 52 1 rows processed 53 54 55 56 57 58 --观察COUNT(*)用普通索引的代价 59 create index idx_t_obj on t(object_id); 60 alter table T modify object_id not null; 61 set autotrace on 62 select count(*) from t; 63 64 65 66 67 68 COUNT(*) 69 ---------- 70 4684992 71 普通索引的执行计划 72 --------------------------------------------------------------------------- 73 | Id | Operation | Name | Rows | Cost (%CPU)| Time | 74 --------------------------------------------------------------------------- 75 | 0 | SELECT STATEMENT | | 1 | 3047 (2)| 00:00:37 | 76 | 1 | SORT AGGREGATE | | 1 | | | 77 | 2 | INDEX FAST FULL SCAN| IDX_T_OBJ | 4620K| 3047 (2)| 00:00:37 | 78 --------------------------------------------------------------------------- 79 普通索引的统计信息 80 ---------------------------------------------------------- 81 0 recursive calls 82 0 db block gets 83 10998 consistent gets 84 0 physical reads 85 0 redo size 86 426 bytes sent via SQL*Net to client 87 415 bytes received via SQL*Net from client 88 2 SQL*Net roundtrips to/from client 89 0 sorts (memory) 90 0 sorts (disk) 91 1 rows processed 92 93 94 95 96 --观察COUNT(*)用位图索引的代价（注意，这里我们特意取了status这个重复度很高的列做索引） 97 create bitmap index idx_bitm_t_status on t(status); 98 select count(*) from t; 99 100 SQL> select count(*) from t;101 102 103 104 105 106 107 COUNT(*)108 ----------109 4684992110 111 位图索引的执行计划112 -------------------------------------------------------------------------------------------113 | Id | Operation | Name | Rows | Cost (%CPU)| Time |114 -------------------------------------------------------------------------------------------115 | 0 | SELECT STATEMENT | | 1 | 115 (0)| 00:00:02 |116 | 1 | SORT AGGREGATE | | 1 | | |117 | 2 | BITMAP CONVERSION COUNT | | 4620K| 115 (0)| 00:00:02 |118 | 3 | BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS | | | |119 -------------------------------------------------------------------------------------------120 位图索引的统计信息121 ----------------------------------------------------------122 0 recursive calls123 0 db block gets124 125 consistent gets125 0 physical reads126 0 redo size127 426 bytes sent via SQL*Net to client128 415 bytes received via SQL*Net from client129 2 SQL*Net roundtrips to/from client130 0 sorts (memory)131 0 sorts (disk)132 1 rows processed133 134
4、
二、反向索引
三、函数索引
四、全文检索
Oracle 优化——位图、等索引介绍
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。