oracle索引

索引是为了满足用户特定的查询，给数据行中的特定列上加一个查询映射！


索引是一张按你索引列值排序的表


索引是以空间换时间


建立索引的原则：
1、一定要在频繁查询的列上建立索引！
2、如果某个列频繁修改，也不适合建索引（修改记录会影响其他行的索引重置）！


1、主键索引
我们要求用户对每个表都应该建有主键约束，只要有主键约束，就自动建有主键索引！

2、唯一索引
只要列上建有唯一约束，就自动建有唯一索引
反之只要列上建有唯一索引，就自动建有唯一约束
如果某个列上已经建有主键约束或者唯一索引，那么不能重复建立！
alter table emp drop primary key;
create unique index idx_uq_emp_empno on emp (empno);

insert into emp (empno, ename) values (7369, 'aaa');--违反唯一约束条件

3、位图索引
如果某个列的列值变化不多，就在固定的那么几个值之间变化（性别、城市、选课）
create bitmap index idx_bt_emp_job on emp (job);

4、普通索引
姓名、成绩、地址、电话
I、在某个列上
    create index idx_emp_ename on emp (ename);
    
    查询列条件的优化顺序：
    如果查询条件中有主键，首先根据主键条件筛选
    然后再从左往右依次根据查询条件筛选
    
    原始：select * from emp where deptno=10 or ename like 'M%' or empno>7777;
    1、先empno>7777
    2、再deptno=10
    3、最后ename like 'M%'
    
    可见上述查询并没有利用到你的索引特性！
    
    优化：
    select * from emp where empno>7777 or ename like 'M%' or deptno=10;
    查询条件顺序应该是：主键列 索引列 其它列
    
    
  II、组合索引
    经常要在几个列之间做组合查询
    select * from emp where ename like 'M%' and deptno=10;
    
    create index idx_emp_ename_deptno on emp (ename, deptno);
    
    但是注意：如果你建了组合索引，那么查询的顺序一定要按照建索引时列的顺序
    -- 下面的查询与你建的组合索引没有任何优化关系（没有用到你建的组合索引）
    select * from emp where deptno=10 and ename like 'M%';--顺序很重要
    
    
  III、函数索引
    如果我们查询某个列时，经常要做函数运算，可以直接在这个列上建函数索引
    也就是先通过函数将你这个列的值给先算出来放一边，然后你的查询中再用函数，就直接查之前索引中已经算出来的值比较
    
    select * from emp where lower(ename) = lower('scott')
    
    create index idx_emp_lower_ename on emp (lower(ename))
  
  IV、算法比较索引
    如果某个列经常要做数学运算> < >= <=，那么可以给这些列预先做好与运算结构，放一边，然后你的查询中使用运算时，就直接查之前索引中已经算出来的值比较
    
    create index idx_emp_sal on emp (sal-2000)
    select * from emp where sal-2000>0
    
    create index idx_stu_score on stu (score-60)
    -- 这些写也不会利用到索引
    select * from stu where score>=60
    -- 这才是正确的索引写法
    select * from stu where score-60>=0  --where后的内容需要与索引的内容一致




===================================


drop table t_test
create table t_test(id int, name varchar2(10));


begin
  for i in 1..500000
    loop
      insert into t_test values (i, 'Tom'||(mod(i, 10)+1));
    end loop;
  commit;
end;


t_test 占用 9437184
idx_test 占用 9437184


select * from user_segments where segment_name in ('T_TEST', 'IDX_TEST')


select count(*) from t_test where name='Tom3' -- 0.015


create index idx_test on t_test (name)


delete from t_test where name='Tom3'
删除数据时，处于对磁盘IO性能的考虑，并不会立刻释放磁盘空间，
我们以后可以在合适时间（凌晨）做一个计划任务去move数据！


alter table t_test move; 
当你释放表空间，注意索引表空间不会释放！
因此，我们还需要再重建索引！


alter index idx_test rebuild
重新建立索引表


索引表在创建或者重建时，物理表上加锁，只能读不允许再做写操作！


索引表在创建时，这时如果有用户正在根据你的这个索引表查询：
1、alter index idx_test rebuild
  数据库是另外开辟一个索引表空间来放你新建的索引
  原有的老索引还被其他用户继续访问
  等新索引表建完，那么删除老索引表，启动新索引表
  这就是为什么我们在凌晨检索淘宝数据时，会发现查询中商品清单，你点击进去可能是失效的！
  
2、我们在重置索引时，可以通过online关键字，让用户搜索物理表，暂不使用索引表（因为这时候数据有可能是无效的）
alter index idx_test rebuild online
这样在这0.663秒重置索引时间内的姓名查询，数据库会直接查物理表（做全表扫描）


=======================================


如果数据基表相当大（几个G），我们倒不建议大家重置索引表？
而是重新删除原有索引表，直接新建（又节省时间又节省空间数据也不会有无效数据产生）