查看index内容

1.简单查询在相关表上建立了哪些索引

 select index_name from all_indexes where table_name='xxx';

 

2.根据表名或index名称查看index的内容:

select i.index_name,c.column_name,c.COLUMN_POSITION from all_indexes i,all_ind_columns c  where i.index_name=c.index_name and i.table_name ='COTS_TAB_ACCT_MVMT' order by i.index_name,c.COLUMN_POSITION;

(或者i.index_name='xxx'来查询)

 

3.如果index中包含函数索引,由上述sql查出的结果中colume_name就会像'SYS_NCXXX$',这时需要通过下面sql去查

select column_expression from all_ind_expressions e where e.index_name='xxx';

 

4.或者干脆通过DBMS_METADAT去查

SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME') FROM DUA;