Oracle 创建/使用/管理索引/(PCTFREE/PCTUSED)

名词解释:
1.PCTFREE
PCTFREE：为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

2.PCTUSED
PCTUSED：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

3.优化方向
如果希望儲存空間發揮最大使用效益，可以把PCTUSED設大一點，相反的，如果想要提高IO效能，應該把PCTUSED設定小一點。

一:创建索引

创建索引语法

–1.创建不唯一索引
create index 索引名称 on 表名(列名) tablespace 表空间名 storage(…) pctfree 0;

–2.创建联合索引
create index 索引名称 on 表名(列名1,列名2) tablespace 表空间名;

–3.创建唯一索引
create unique index 索引名称 on 表名(列名) tablespace 表空间名称;

–4.创建位图索引
create bitmap index 索引名称 on 表名(列名) tablespace 表空间名称;

–5.创建反转键索引
create unique index 索引名称 on 表名(列名) tablespace 表空间名称 reverse;

–6.创建函数索引(既可以是普通B-Tree索引也可以是位图索引)
create index 索引名称 on 表名(substr(列名,1,4)) tablespace 表空间名称;

注意:创建索引遵循的策略如下
1.导入数据后再创建索引
2.不需要为很小的表创建索引
3.对于取值范围很小的字段(比如:性别) 应当建立位图索引
4.限制表中的所有数目
5.为所有设置合适的PCTFREE值
6.存储索引的表空间最好单独设定

二:使用索引

oracle采用两种方式访问表记录:
1.全表扫描(full table scan):访问每条记录,oracle采用一次读入多个数据块(database block)的方式优化全表扫描
2.索引扫描(index scan):采用rowid的方式访问提高效率,rowid包含了表中记录的物理位置信息,通过索引实现数据和数据存放位置的联系提高效率

oracle访问索引两种方式:
1.索引唯一扫描(index unique scan)
如果检索的列就包含在索引中,那么oracle就只查询索引数据,而不再检索表数据

2.索引范围扫描(index range scan)
2.1第一种情况是:通过唯一索引的范围,where index between 1 and 100
2.2第二种情况是:通过不唯一索引查询,oracle就不能进行唯一索引扫描,所以会先通过索引范围扫描后再通过rowid范围表

注意: 在where子句中如果索引使用like查询,且第一个字符由通配符开始,例如 like “%305” ,这时候索引将不被使用!

oracle在有索引而不使用索引的情况:
1.如上
2.表未作分析或者分析信息太旧,导致oracle判断失误
3.根据表拥有的记录和数据块数,实际上全表扫描要比索引扫描更快

哪种情况下全表扫描快于索引扫描:
oracle在评估使用索引的代价cost时有两个数据:
1.CF(Clustering Factor) :可以理解为每读入一个索引块要对应读入多少个数据块
2.FF(Filtering Factor):即SQL语句所选择的结果集占总的数据量的百分比

代价COST计算公式: FF*(CF + 索引块个数)

注意: 如果表频繁插入或者修改会造成CF越来越大,甚至比实际数据块数量大,这时候需要重新整理索引

使用索引注意事项:
1.索引不是越多越好,特别是大量从来或者几乎不使用的索引,对系统有损害,OLTP系统每张表最好不要超过5个索引,否则降低性能
2.很多时候单列索引不如符合索引有效率
3.用于多表连接的字段,加上索引会更好

三:管理索引

1.删除索引(有损坏的数据块,或过多碎片,或索引失去价值):
drop index 索引名称
2.重建索引(在指定的表空间重新建立然后删除原来的索引)
alter index 索引名称 rebuild
3.合并索引(用于清理碎片.但是不会改变其物理组织结构):
alter index 索引名称 coalesce
4.修改不知名索引(由oracle自动创建的索引):
alter index 表名 primary key using index pctfree 5;
5.修改索引(但是initial和minextents参数自索引建立后不可更改):
alter index 索引名称 storage(平常听crease 50);