关于索引

--查所有表的索引

SELECT T.TABLE_NAME, --表名

T.INDEX_NAME, --索引名

I.UNIQUENESS, --是否非空

I.INDEX_TYPE, --索引类型

C.CONSTRAINT_TYPE, --键类型

WM_CONCAT(T.COLUMN_NAME) COLS,

(CASE

WHEN C.CONSTRAINT_TYPE = 'P' OR C.CONSTRAINT_TYPE = 'R' THEN --主键和外键创建脚本拼接

'ALTER TABLE ' || T.TABLE_NAME || ' ADD CONSTRAINT ' ||

T.INDEX_NAME || (CASE

WHEN C.CONSTRAINT_TYPE = 'P' THEN

' PRIMARY KEY ('

ELSE

' FOREIGN KEY ('

END) || WM_CONCAT(T.COLUMN_NAME) || ');'

ELSE --索引创建脚本拼接

'CREATE ' || (CASE

WHEN I.UNIQUENESS = 'UNIQUE' THEN

I.UNIQUENESS || ' '

ELSE

CASE

WHEN I.INDEX_TYPE = 'NORMAL' THEN

''

ELSE

I.INDEX_TYPE || ' '

END

END) || 'INDEX ' || T.INDEX_NAME || ' ON ' || T.TABLE_NAME || '(' ||

WM_CONCAT(COLUMN_NAME) || ');'

END) SQL_CMD, --拼接创建脚本

'drop index '||T.INDEX_NAME||';' as drop_cmd

FROM USER_IND_COLUMNS T, USER_INDEXES I, USER_CONSTRAINTS C

WHERE T.INDEX_NAME = I.INDEX_NAME

AND T.INDEX_NAME = C.CONSTRAINT_NAME(+)

AND T.TABLE_NAME LIKE 'JOB%' --自建表规则（只查询自己创建的表【我的建表规则以TB_开头】，排除系统表）

AND I.INDEX_TYPE != 'FUNCTION-BASED NORMAL' --排除基于函数的索引

--and T.COLUMN_NAME not like '%RID%'

--and I.UNIQUENESS not in('UNIQUE')

GROUP BY T.TABLE_NAME,

T.INDEX_NAME,

I.UNIQUENESS,

I.INDEX_TYPE,

C.CONSTRAINT_TYPE

 

容易引起oracle索引失效的原因很多：

　　1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等，对索引列进行运算.需要建立函数索引就可以解决了。

　　2、新建的表还没来得及生成统计信息，分析一下就好了

　　3、基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。

　　4、使用<>、not in 、not exist，对于这三种情况大多数情况下认为结果集很大，一般大于5%-15%就不走索引而走FTS。

　　5、单独的>、<。

　　6、like "%_" 百分号在前。

　　7、单独引用复合索引里非第一位置的索引列。

　　8、字符型字段为数字时在where条件里不添加引号。

　　9、当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

　　10、索引失效，可以考虑重建索引，rebuild online。

　　11、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null  都会走、联合索引 is not null 只要在建立的索引列（不分先后）都会走。