1，外键列创建索引
  如果表中创建了外键约束
  alter table address add constraint add_fk1
  forign key (cust_id) reference cust(cust_id);
  创建外键索引
   create index addr_fk1 on address(cust_id);

2 组合索引
  两个列组合起来的索引
  create index cust_dex on cust(las_name,first_name)
3 创建压缩索引 (某列存在大量重复值)
 create index cust_dex on cust(las_name,first_name) compress 2;(2 代表第2列大量重复值)
函数索引
 create index  cus_dex on cust (upper(first_name));
4 虚拟列创建索引(基于函数索引的衍生)
  1,创建虚拟列 alter table cust (up_name generated always as (upper(first_name)) virtual) ;
  crtete index cust_vidx1 on cust_vidx on cust(up_time);
 
5 平衡索引I/O 利用反转键索引
 create index inv_idx on inv(inv_id) reverse;

6 新增一个索引而不影响已经有的应用(第三方应用不支持用户新增自己的索引)
  通过提示hint
 1, alter index inv_idx on(inv_id) invisible
 2,设置优化器考虑不可见的索引 alter system set optimizer_use_invisible_index=true
 3,通过查询 select /*+index(inv inv_dex）*/ inv_id from inv inv_id=1;(可以通过 set autotrace trace explain追踪查看)
   如果想标记可见alter index inv_idx visible；

7 索引组织表 (创建两张多对多的关系表的交集)
 create table cust_assoc
  ( cust_id number,user_group_id number,create_Dtt timestamp(5),update_dtt timestamp(5),constraint cust_assoc_pk primary key(cust_id,
  user_group_id)
  )
  oraganization index including create_dtt pctthreshold 30 tablespace nsestar_index overflow tablespace dim_index;

8 监控索引
 1，监控单个索引
   alter index f_res_idx monitoring useage; (通过v$object_usage)
 2,监控一个用户的索引(脚本)
  set pagesize 0 head off line size 132
  spool enable_mon.sql
  select 'alter index'||index_name||'monitoring usage;'
  from user_index;
  spoll off;
 3,关闭监控
  alter index f_res_idx nomonitoring useage; 

9 快速创建索引的方法
1,nologging
create  index inv_idex1 on inv(inv_id) nologging tablespace inv_mes_idx;
2,parallel 增加并行度
create  index inv_idex1 on inv(inv_id) parallel 2 tablespace inv_mes_idx; (并行度默认是CPU数*parallel_threads_per_cpu)
3,1和2合起来使用

10 快速重建索引
alter index inv_idex1 rebuild parallel nologging (快速重建索引,parallel(并行度),nologging(不产生日志 redo))
回收未使用的索引空间
通过查询 回收空间
select f.task_name,
       to_char(e.execution_start, 'dd-mon-yy hh24:mi') sdate,
       o.attr2,
       o.type,
       o.attr3,
       f.message,
       f.more_info
  from dba_advisor_findings   f,
       dba_advisor_objects    o,
       dba_advisor_executions e
 where o.task_id = f.task_id
   and o.object_id = f.object_id
   and f.task_id = e.task_id
   and e.execution_start > sysdate - 1
   and e.advisor_name = 'Segment Advisor'
 order by f.task_name

1,重建索引
alter index f_reg_idx rebulid;
2,收缩索引
alter index f_reg_idx shrink space;