（摘抄）oracle index 索引

小白开始学习索引之路
本文皆来自此视频

索引的说明和目的

• 索引说明：
  索引是表与相关的一个可选结构，在逻辑上和物理上都是独立于表的数据，索引能优化查询，不能优化DML操作，就是所谓的查询快啦删除更新慢啦，oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。如果sql语句仅访问被索引的列，那么数据库只需要从索引中读数据，而不是读取表，如果该语句同时还要访问除索引列之外的列，那么数据库会使用rowid来查找表中的行，通常是查询表数据，数据库以交替的方式先读取索引块，然后读取相应的表块。

• 索引的目的是：主要是减少IO,这是本质，这样才能体现索引的效率。需要建索引的情况如下：

1. 大表，返回的行数<5%
2. 经常使用where子句查询的列
3. 离散度高的列
4. 更新键值代价低
5. 逻辑and,or 效率高
6. 查看索引在建在那表那列：
   select * from user_indexes;
   select * from user_ind_columns;

索引的分类

• 1.B树索引结构(类似于字典查询，最后到leaf bolck,存的是数据rowid和数据项）

      1)叶块之间使用双向链连接，为了可以范围查询。
    	2)删除表行时索引叶块也会更新，只是逻辑更改，并不做物理的删除叶块。
    	3)索引叶块中不保存表行键值的null信息（这个我深有体会即使不是索引查询也会查不到null的行）。
  

• 2.位图索引结构(离散度比较低的时候，需要用位图索引，离散度指的是重复度【比较高用位图索引】create bitmap index job_bitmap on emp1 (job);(因为job的值重复分布的比较多，即离散度比较低)。位图索引结构如下：
  

B树索引的具体分类

索引楼主只用过一般索引和组合索引，小伙伴们有用过其他索引的可以在评论区说下，主要是场景比心。

1. 唯一索引：唯一索引指键值不重复。
   create unique index empno_idx on emp(empno) ;
   drop index empno_idx;
2. 一般索引：键值可以重复
   create index empno_idex on emp(empno)
3. 组合索引:绑定两个或更多列的索引。
   create index empno_idex on emp(empno,depno)
4. 反向索引:为了避免平衡树索引热块，比如emp表中员工号都是以7 开头，这样你建索引就会把很多数据分配到一个索引块里，使用反向索引可以报数据库分布均匀。例如表里有 7788，7889,7666如果建一般索引这三条数据就会分配到一个索引块，如果建反向索引，8877,9887,6667这三个就会分配到三个索引块里。
   create index mgr_idx on emp1(mgr) reverse;
5. 函数索引:如果查询是必须用这个函数，可以这么建索引
   create index fun_idx on emp1(lower(ename));
   select * from empl where lower(ename)=‘scott’;
6. 压缩索引
   create index comp_idx on emp(sal) compress;
7. 升序降序索引
   create index deptno_job_idx on emp(deptno desc,job asc);

索引碎片

由于对基础表做DML操作，导致索引表块的自动更改操作，尤其是delete操作，会引起index表的index_entries的逻辑删除，注意只有当一个索引块中的全部index_entry都被删除，才会把这个索引块删除，索引对基础表的delete，insert,操作做会产生索引碎片问题。例如一个index_entry对应行被删除啦，但是还有其他的index_entry,这样会导致这个索引块不被删除。这样就会产生索引碎片。
在oracle文档里并没有清晰的给出索引碎片的量化标准，oracle官方建议通过seqment Advisor(段顾问)解决表和索引的碎片问题，如果你想自行解决，可以通过查看index_stats视图，当以下三种情况之一发生时，说明积累的碎片应该整理（仅供参考）啦。
1.HEIGHT>=4 这个是索引的高度
2.PCT_USED<50%
3.DEL_LF_ROWS/LF_ROWS>0.2
场景模拟：

create table t(id int);
create index ind_1 on t(id);
drop index in_1;
begin
  for i in 1..1000000 loop
    insert into t values(i);
    if mod(i,100)=0 then
      commit;
    end if;
    end loop;
end;
/

analyze index ind_1 validate structure;
select  name,height,pct_used,del_lf_rows/lf_rows from index_stats

对该表进行删除操作：
delete from t where rownum<800000
重新分析重新执行
analyze index ind_1 validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats
结果

10g及其以后可以通过以下语句
alter index ind_l rebulid [online] [tablespace name]

alter index ind_1 rebuild online;
analyze index ind_1 validate structure;
select  name,height,pct_used,del_lf_rows/lf_rows from index_stats

最后表的结果：

本人是小白，大家有啥好的oracle视频或者资料，希望大家分享给我，俗话说的好独乐乐不如众乐乐。（比心）