Oracle 索引index那些事

Oracle 索引index那些事,基于函数的索引会有timestamp，当进行基于时间点的恢复的时候，如果timestamp比系统的恢复时间点新，可能

表修改很少，可以多创建索引，特别是read only的表
表修改很多，需要着重考虑

15%的数据以下筛选适合创建索引

索引不包含null，所以对条件为is Not null是合适创建索引的，优不优良另说

最大尺寸的index应该在表的一半以下

可以使用并行创建index，加快建索引的速度

创建索引可以使用nologging  --提速的好主意，再结合parallel，但是生成归档量没有测试，大家可以测试一下啊

unusable indexes 
--优化器不会考虑，dml也不会维护这些索引，适合大批量数据加载的时候，加载完成后在将索引开启
当将索引置为unusable时，index segment会被删除
skip_unusable_indexes参数决定了dml是否维护被置为unusable的index

invisible indexes 
这个索引也会被optimizer忽略，但是dml语句会维护这个索引，单个的分区索引是不能置为invisible模式的。
使用场景：
1 测试删除一个索引产生的影响
2 创建临时索引为一定的操作而不影响现有的程序
3 在已经拥有索引的列上创建其他索引  --可能是新特性，一个索引上可以创建多个索引

understand when to create multiple indexes on the same set of columns    -p745
可以在多个列上创建不同类型的索引，但同一时刻只能一条索引是visible的，其他索引必须是invisible的。

--分区索引可以在普通表上创建，这个是优化的一个好思路，不能光看准分区表  ****

重建和coalesce索引 
很明显coalesce功能少，影响小
coalesce会将相同height的leaf block进行合并，所以不单纯是回收不使用的叶块，但不会缩减index 的height

创建特别大索引的时候应该单独创建一个temporary tablespace，创建索引肯定会涉及到排序操作的，当然把session的sort_area_size调大一些也有好处。

create index idx_aa on aa(id) online;  --指定online不能使用parallel，不能存在ddl操作，但允许基表上进行dml操作。

基于函数的索引会有timestamp，当进行基于时间点的恢复的时候，如果timestamp比系统的恢复时间点新，可能这个索引就失效了，可以使用analyze

index ... validate sructure语句进行确认，没有实验进行支撑，观点略显苍白。

 

creating a key-compressed index

--会使用前缀后缀值进行压缩，，适合非唯一值比较多的引导列情况

--节省空间，提高性能
压缩索引适合非唯一的列的情况，也是前缀和后缀进行压缩

create index hr.emp_ename  on emp(ename) tablespace users compress 1;

alter index hr.emp_ename rebuild nocompress;

unuseable index  -- -所谓创建即不可用，不分配segment

create index  id_a on a (id unusable);

invisible index    --同上，但分配段，dml操作也会被数据库维护（不用这些索引的时候还是少创建为宜），可以在session级别使用optimizer_use_invisible_indexes为true使用索引
--上面两个索引都会被优化器忽略

create index emp_id on emp(id) invisible;

unusable 索引被优化器忽略，并且在基表进行dml操作时不会得到维护

alter index id1 rename to id2;

alter index id1 monitoring usage;
alter index id1 nomonitoring usage;

dba_ind_expressions  --可以查看基于函数的索引的表达式

alter index ... validate structure;  --之后可以查询index_stats获得关于index的状态。

user_object_usage;  --获得index是否在使用。

相关阅读：

Oracle收集统计信息导致索引被监控

由Oracle索引来理解ArcSDE索引

Oracle索引技术之如何建立最佳索引

Oracle索引列NULL值引发执行计划该表的测试示例

Oracle索引 主键影响查询速度

Oracle索引扫描