一、位图索引

我将使用一个例子，来描述位图索引的存储，并分析它的优点。

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 行业投向 = 农业，效率是很高的，

当采集平台完成这些金融数据采集后，金融监管部门要对信息进行分析、统计，形成报表。有位图索引效率是很好的。

具体案例

 /*

 总结：本质原因：其实就是位图索引存放的是0,1的比特位，占字节数特别少。

 */

 --位图索引跟踪前准备

 drop table t purge;

 set autotrace off

 create table t as select * from dba_objects;

 insert into t select * from t;

 insert into t select * from t;

 insert into t select * from t;

 insert into t select * from t;

 insert into t select * from t;

 insert into t select * from t;

 update t set object_id=rownum;

 commit;

 --观察COUNT(*)全表扫描的代价

 set autotrace on

 set linesize 1000

 select count(*) from t;

 ------------------------------------------

   COUNT(*)

 ----------

   4684992

 执行计划

 ----------------------------------------------------------

 Plan hash value: 2966233522

 -------------------------------------------------------------------

 | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

 -------------------------------------------------------------------

 |   0 | SELECT STATEMENT   |      |     1 | 20420  (11)| 00:04:06 |

 |   1 |  SORT AGGREGATE    |      |     1 |            |          |

 |   2 |   TABLE ACCESS FULL| T    |   294M| 20420  (11)| 00:04:06 |

 -------------------------------------------------------------------

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

       66731  consistent gets

           0  physical reads

           0  redo size

         426  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed

 --观察COUNT(*)用普通索引的代价

 create index idx_t_obj on t(object_id);

 alter table T modify object_id not null;

 set autotrace on

 select count(*) from t;

   COUNT(*)

 ----------

   4684992

 普通索引的执行计划

 ---------------------------------------------------------------------------

 | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |

 ---------------------------------------------------------------------------

 |   0 | SELECT STATEMENT      |           |     1 |  3047   (2)| 00:00:37 |

 |   1 |  SORT AGGREGATE       |           |     1 |            |          |

 |   2 |   INDEX FAST FULL SCAN| IDX_T_OBJ |  4620K|  3047   (2)| 00:00:37 |

 ---------------------------------------------------------------------------

 普通索引的统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

       10998  consistent gets

           0  physical reads

           0  redo size

         426  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed

 --观察COUNT(*)用位图索引的代价（注意，这里我们特意取了status这个重复度很高的列做索引）

 create bitmap index idx_bitm_t_status on t(status);

 select count(*) from t;

 SQL> select count(*) from t;

   COUNT(*)

 ----------

   4684992

 位图索引的执行计划

 -------------------------------------------------------------------------------------------

 | Id  | Operation                     | Name              | Rows  | Cost (%CPU)| Time     |

 -------------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT              |                   |     1 |   115   (0)| 00:00:02 |

 |   1 |  SORT AGGREGATE               |                   |     1 |            |          |

 |   2 |   BITMAP CONVERSION COUNT     |                   |  4620K|   115   (0)| 00:00:02 |

 |   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS |       |            |          |

 -------------------------------------------------------------------------------------------

 位图索引的统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

         125  consistent gets

           0  physical reads

           0  redo size

         426  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed

位图索引与普通索引比较以及执行计划

二、反向索引

假如我现在有些ID 100001,100002,100003,100004,100005 ，那么反向索引，他的索引创建的就是 100001,200001,300001,400001,500001。由于序列本身有序，会根据范围放在不同的叶子块中

详见：索引，组合索引篇那么索引就被放在不同的快中，有效的减少了热快争用。

Oracle 优化——位图、函数等索引介绍

再看一下这张图，最下面就是叶子块，100001 和200001 和300001 会放在不同的块中，而一般常常会频繁的访问近期的数据，那么由于他们在不同的块中，在索引进行检索的时候，能够有效的减少资源竞争。

创建反向索引的sql

---反向索引

create index rev_index on t(column) reverse;

---将反向索引转换成普通索引。

alter index rev_index  rebuild noreverse;

2、反向索引，在进行范围查询的时候无效,

3、反向索引无序了，所以无法走索引排序,

三、函数索引

我们现在，有一个场景：有一列数据是有大小写的，但是查询的时候，不需要区分大小写。

那么语句只能这么写 select * from t where upper(object_name)='T' ;

首先有一个常识，就是走了函数查询，不会走索引。就像有些查询列的类型与值类型不匹，会进行值类型函数转换，然后无法进行索引查询。

eg: id为varchar类型而查询语句为：select * from t where id = 1。由于数据字段为varchar类型，而参数为number 类型，故会进行值类型转换。检索就走了全表扫描。

那么如何实现场景需求呢？只能让函数索引一展身手：

create index idx_func_ojbnam on t( upper(object_name) );

upper()是Oracle内部函数

现在 select * from t where upper(object_name)='T' ; 这条语句就能走上索引。

请看具体案例

 --测函数索引前准备

 drop table t purge;

 create table t as select * from dba_objects;

 create index idx_object_id on t(object_id);

 create index idx_object_name on t(object_name);

 create index idx_created on t(created);

 --对列做UPPER操作，无法用到索引

 set autotrace traceonly

 set linesize 1000

 ---以下语句由于列运算，所以走的是全表扫描

 select * from t  where upper(object_name)='T' ;

 执行计划

 --------------------------------------------------------------------------

 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

 --------------------------------------------------------------------------

 |   0 | SELECT STATEMENT  |      |    12 |  2484 |   293   (1)| 00:00:04 |

 |*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   293   (1)| 00:00:04 |

 --------------------------------------------------------------------------

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

        1049  consistent gets

           0  physical reads

           0  redo size

        1500  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           2  rows processed

 --去掉列的UPPER操作后立即用索引

 select * from t  where  object_name='T' ;

 执行计划

 ----------------------------------------------------------

 Plan hash value: 1138138579

 -----------------------------------------------------------------------------------------------

 | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

 -----------------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT            |                 |     2 |   414 |     4   (0)| 00:00:01 |

 |   1 |  TABLE ACCESS BY INDEX ROWID| T               |     2 |   414 |     4   (0)| 00:00:01 |

 |*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_NAME |     2 |       |     3   (0)| 00:00:01 |

 -----------------------------------------------------------------------------------------------

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

           6  consistent gets

           0  physical reads

           0  redo size

        1506  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           2  rows processed      

 --如果必须用upper的条件，那你想用到索引，就得去建函数索引

 create index idx_func_ojbnam on t(upper(object_name));

 --继续执行，终于走索引了。

 select * from t  where upper(object_name)='T' ;

 执行计划

 -----------------------------------------------------------------------------------------------

 | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

 -----------------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT            |                 |   775 |   206K|   152   (0)| 00:00:02 |

 |   1 |  TABLE ACCESS BY INDEX ROWID| T               |   775 |   206K|   152   (0)| 00:00:02 |

 |*  2 |   INDEX RANGE SCAN          | IDX_FUNC_OJBNAM |   310 |       |     3   (0)| 00:00:01 |

 -----------------------------------------------------------------------------------------------

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

           6  consistent gets

           0  physical reads

           0  redo size

        1500  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           2  rows processed

场景中使用函数索引的案例实战（可以直接运行）

 /*

   结论：什么类型就放什么值，否则会发生类型转换，导致系能问题！

 （是存放字符的字段就设varchar2类型,是存放数值的字段就设置number类型,是存放日期的字段就设置date类型）

  这里的案例宏中

  select * from t_col_type where id=6; 用不到索引，要改成select * from t_col_type where id='6';

  如果送来的参数无法保证是'6',只能写成select * from t_col_type where to_number(id)=6;并且建to_number(id)的函数索引

  方可，这是很无奈的事。

 */

 --举例说明：

 drop table t_col_type purge;

 create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));

 insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;

 commit;

 create index idx_id on t_col_type(id);

 set linesize 1000

 set autotrace traceonly

 select * from t_col_type where id=6;

 执行计划

 --------------------------------------------------------------------------------

 | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

 --------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT  |            |     1 |    36 |     9   (0)| 00:00:01 |

 |*  1 |  TABLE ACCESS FULL| T_COL_TYPE |     1 |    36 |     9   (0)| 00:00:01 |

 --------------------------------------------------------------------------------

    1 - filter(TO_NUMBER("ID")=6)

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

          32  consistent gets

           0  physical reads

           0  redo size

         540  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed

 --实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。

 select * from t_col_type where id='';

 执行计划

 ------------------------------------------------------------------------------------------

 | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

 ------------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT            |            |     1 |    36 |     2   (0)| 00:00:01 |

 |   1 |  TABLE ACCESS BY INDEX ROWID| T_COL_TYPE |     1 |    36 |     2   (0)| 00:00:01 |

 |*  2 |   INDEX RANGE SCAN          | IDX_ID     |     1 |       |     1   (0)| 00:00:01 |

 ------------------------------------------------------------------------------------------

    2 - access("ID"='')

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

           4  consistent gets

           0  physical reads

           0544  bytes sent via SQL*Net to client

         415  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed          

 create index idx_func_tonumber_id on t_col_type(to_number(id));

 select * from t_col_type where to_number(id)=6;

lt;span style="color: #008080;">75 ----------------------------------------------------------------------------------------------------

 | Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

77t;>| Time     |

 ----------------------------------------------------------------------------------------------------

 |   0 | SELECT STATEMENT            |                      |   100 |  4900 |     2   (0)| 00:00:01 |

 |   1 |  TABLE ACCESS BY INDEX ROWID| T_COL_TYPE           |   100 |  4900 |     2   (0)| 00:00:01 |

 |*  2 |   INDEX RANGE SCAN          | IDX_FUNC_TONUMBER_ID |    40 |       |     1   (0)| 00:00:01 |

 ----------------------------------------------------------------------------------------------------

 2 - access(TO_NUMBER("ID")=6)

 统计信息

 ----------------------------------------------------------

           0  recursive calls

           0  db block gets

           4  consistent gets

           0  physical reads

           0  redo size

         540  bytes sent via SQL*Net to client

         416  bytes received via SQL*Net from client

           2  SQL*Net roundtrips to/from client

           0  sorts (memory)

           0  sorts (disk)

           1  rows processed

列类型不匹配导致无法走索引的案例实战

2、自定义函数索引

首先创建一个自定义函数让 id-1 的形式创建序列，当然没什么意义。

create or replace function f_minus1(i int)

   return int DETERMINISTIC
is

    begin

    return(i-1);

   end;

create index idx_test on test (f_minus1(object_id));

DETERMINISTIC关键字很重要

四、全文检索

干、、写了四个小时，Google 浏览器崩溃了。

秒客网

Oracle 优化——位图、函数等索引介绍

一、位图索引

二、反向索引

三、函数索引

四、全文检索

相关文章