索引，妙不可言

王硕

5.2.2.4位图索引重复度低

位图索引：第一位图索引列重复列低；第二该表极少更新。

在object_id列建立为图索引，count（*）性能比较：

drop table t purge;

create table t as select * from dba_objec；

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;

create bitmap index idx_bit_object_id on t(object_id);

select count(*) from t;

代价为10739 全表扫描

强制使用索引：

select /*+index(t,idx_bit_object_id)*/ count(*) from t;

代价12987，比全表扫描10739还高。

结论：btree索引存储的是列值，而位图索引存储的是比特位值。

一个生产库中，如果同一个列重复度低，但是更新频繁，就不能加位图索引。

王硕

5.2.3函数索引

5.2.3.1列运算让索引失去作用

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);

select count(*) from t;

set autotrace traceonly

set linesize 1000

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

全表扫描 逻辑读772

去掉列upper操作，则使用索引:

select * from t where object_name='T';

如果object_name 不存在小写字母。upper操作不影响结果。但是一般是大写的就不用upper查询了。

5.2.3.2正确使用函数索引：

用函数运算代替列名：

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

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

逻辑读与普通索引一样，代价确实113，远大于普通索引的2，只是比全表扫描好，全表索引逻辑读为772，代价为175；

查看索引类型：

select index_name,index_type from user_indexes where table_name='T';

类型为FUNCTION-BASED NORMAL

5.2.3.3避免列运算

经典案例1：

set autotrace traceonly

set linesize 1000

select * from t where object_id-10<=30;

select * from t where object_id<=40;

要同时用列运算，又保证高效：

create index idx_object_id_2 on t(object_id-10);

select * from t where object_id-10<=30;

2.经典案例2

错误示范：

select * from t where substr(object_name,1,4)='CLUS';

select * from t where substr(object_name,1,5)='CLUST';

select * from t where substr(object_name，1,6）='CLUSTE';

正确示范：

select * from t where object_name like 'CLUS%';

select * from t where object_name like 'CLUST%';

select * from t where object_name like 'CLUSTE%';

3.经典案例3

错误示范：（找出两天的数据）

select *

from t

where trunc(created)>=TO_DATE('2012-10-02','YYYY-MM-DD'）

and trunc(created)<=TO_DATE('2012-10-03','YYYY-MM-DD');

只有建一个trunc相关的函数索引，否则用不上索引。

但是建立索引就有弊端,所以可以优化sql：

select *

from t 

where created>=TO_DATA('2012-10-02','YYYY-MM-DD'）

and created<TO_DATE('2012-10-03','YYYY-MM-DD')+1

只要把<=改为<后面加个1 ，就可以不用把create去整了。