索引失效情形

--A.函数转换

--使用FULL JOIN,以前碰到的环境:有时写视图时将两表合并使用FULL JOIN

--示例:

DECLARE @A TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT)

INSERT @A

SELECT

CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'B'+LTRIM(NUMBER) END ,

NUMBER

FROM

MASTER..SPT_VALUES

WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 10

DECLARE @B TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT)

INSERT @B

SELECT

CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'C'+LTRIM(NUMBER) END,

NUMBER

FROM

MASTER..SPT_VALUES

WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 20

--1.先SUM统计后再FULL JOIN 连接（这里没有重复所以没SUM了，测试用）

SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID

FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE (A.NAME='A10' OR B.NAME='A10')

SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID

FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE ISNULL(A.NAME,B.NAME)='A10'

--上面两语句逻辑相同,因函数转换导致失效(一般建立好视图后才查询SELECT * FROM V_NAME WHERE NAME=@NAME)

--解决办法:使用UNION ALL(上面两个NAME 列类型相同)

--2.用UNION ALL后再SUM

SELECT NAME,SUM(AID) AID,SUM(BID)

FROM

(

SELECT NAME,ID AS AID,0 AS BID FROM @A

UNION ALL

SELECT NAME,0 ,ID FROM @B

) T

WHERE NAME='A10'

GROUP BY NAME

--------------------------------------------------------------------

--B.列类型转换

--1使用UNION ALL,以前遇到的环境如上示例

--示例:

SET SHOWPLAN_TEXT ON

DECLARE @A TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)

INSERT @A

SELECT 'A'

UNION ALL SELECT 'B'

--列长度和 @A 不一致

DECLARE @B TABLE(COL VARCHAR(5) NOT NULL PRIMARY KEY)

INSERT @B

SELECT 'ABCCC' UNION ALL SELECT 'BCFDF'

--列长度和 @A 一致

DECLARE @C TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)

INSERT @C

SELECT 'ADD' UNION ALL SELECT 'BAD'

SELECT * FROM

(

SELECT COL FROM @A

UNION ALL

SELECT COL FROM @B

WHERE COL LIKE 'A%'

SELECT * FROM

(

SELECT COL FROM @A

UNION ALL

SELECT COL FROM @C

WHERE COL LIKE 'A%'

SET SHOWPLAN_TEXT ON

--结果:列类型不一致会导致类型转换,低向高精度转换,最后引起索引失效

StmtText

----------------------------------------------------------------------------------------------------

SELECT * FROM

(

SELECT COL FROM @A

UNION ALL

SELECT COL FROM @B

WHERE COL LIKE 'A%'

（所影响的行数为 1 行）

StmtText

--------------------------------------------------------------------------------------------------------------------------------------------

|--Concatenation

|--Filter(WHERE:(like([Expr1002], 'A%', NULL)))

| |--Compute Scalar(DEFINE:([Expr1002]=Convert(@A.[COL])))

| |--Clustered Index Scan(OBJECT:(@A))

|--Clustered Index Seek(OBJECT:(@B), SEEK:(@B.[COL] >= 'A' AND @B.[COL] < 'B'), WHERE:(like(@B.[COL], 'A%', NULL)) ORDERED FORWARD)

（所影响的行数为 5 行）

StmtText

---------------------------------------------------------------------------------------------------

SELECT * FROM

(

SELECT COL FROM @A

UNION ALL

SELECT COL FROM @C

WHERE COL LIKE 'A%'

（所影响的行数为 1 行）

StmtText

--------------------------------------------------------------------------------------------------------------------------------------------

|--Concatenation

|--Clustered Index Seek(OBJECT:(@A), SEEK:(@A.[COL] >= 'A' AND @A.[COL] < 'B'), WHERE:(like(@A.[COL], 'A%', NULL)) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:(@C), SEEK:(@C.[COL] >= 'A' AND @C.[COL] < 'B'), WHERE:(like(@C.[COL], 'A%', NULL)) ORDERED FORWARD)

（所影响的行数为 3 行）

StmtText

------------------------

SET SHOWPLAN_TEXT ON

--2使用SQL语句时数值传参数处理

--示例:

SET SHOWPLAN_TEXT ON

DECLARE @T TABLE(ID DECIMAL(10,2) PRIMARY KEY)

INSERT @T SELECT 1.23 UNION ALL SELECT 2.3

DECLARE @PARA DEC(12,2)

SET @PARA=2.3

SELECT * FROM @T WHERE ID=@PARA

SELECT * FROM @T WHERE ID=CONVERT(DEC(10,2),@PARA)

SET SHOWPLAN_TEXT OFF

--结果ID类型向高类型转换,导致索引失效

SELECT * FROM @T WHERE ID=@PARA

（所影响的行数为 2 行）

StmtText

----------------------------------------------------------------

|--Clustered Index Scan(OBJECT:(@T), WHERE:(@T.[ID]=[@PARA]))

（所影响的行数为 1 行）

StmtText

------------------------------------------------------

SELECT * FROM @T WHERE ID=CONVERT(DEC(10,2),@PARA)

（所影响的行数为 1 行）

StmtText

----------------------------------------------------------------------------------------

|--Clustered Index Seek(OBJECT:(@T), SEEK:(@T.[ID]=Convert([@PARA])) ORDERED FORWARD)

（所影响的行数为 1 行）

--3使用SQL语句时传字符类型参数处理

--示例:

IF OBJECT_ID('T') IS NOT NULL

DROP TABLE T

CREATE TABLE T(COL CHAR(10) PRIMARY KEY )

INSERT T SELECT 'ABCD'

DECLARE @S VARCHAR(10),@S1 NVARCHAR(10)

SELECT @S='ABCD',@S1='ABCD'

SELECT * FROM T WHERE COL=@S

SELECT * FROM T WHERE COL=@S1

--结果:是类型存储的问题,varchar\char 存储时只占一个字节,而nchar\nvarchar存储时是占两个字节（导致类型转换，索引失效）

--(这些从SQL存储引擎可以确定)具体可以参考博客：石头哥的，小麦的，影子老师的

/*StmtText

-------------------------------------------------------------------------

DECLARE @S VARCHAR(10),@S1 NVARCHAR(10)

SELECT @S='ABCD',@S1='ABCD'

SELECT * FROM T WHERE COL=@S

（所影响的行数为 2 行）

StmtText

--------------------------------------------------------------------------------------------------------------

|--Clustered Index Seek(OBJECT:([xzdb].[dbo].[T].[PK__T__1ABFEC2C]), SEEK:([T].[COL]=[@S]) ORDERED FORWARD)

（所影响的行数为 1 行）

StmtText

---------------------------------

SELECT * FROM T WHERE COL=@S1

（所影响的行数为 1 行）

StmtText

---------------------------------------------------------------------------------------------------------

|--Clustered Index Scan(OBJECT:([xzdb].[dbo].[T].[PK__T__1ABFEC2C]), WHERE:(Convert([T].[COL])=[@S1]))

（所影响的行数为 1 行）

--以上仅学习的一点记录,如有不对之处,希望各位拍砖指导,感谢论坛各位大虾的指点,努力Ing,转载可不注明作者信息

数据库中有一个表，用PL/SQL查看该表的索引没有被DROP掉，但是表上的数据查询起来很慢（查询时间大概是原来的3倍），后来重建了一下索引就好了，请问这是为什么，在什么情况下会出现类似的索引丢失的情况？

可能是你的表经常被更新，碎片太多，索引占用空间太大，优化器觉得没有必要用索引了
就直接全表扫描了啊，你重新建立索引，就整理了碎片了啊，当然就又用索引了

索引失效的情况很多，比如左边使用了函数 ，表没有分析 ，导致索引扫描的cost高于全表扫描，表很小 ，等等。需要具体分析。你可以根据执行计划来判断.

******************************************

以下情况会导致索引失效：
1) 直接导入:
imp with SKIP_UNUSABLE_INDEXES=Y
or sqlldr with SKIP_INDEX_MAINTENANCE

2) 在索引维护过程中出现ORA-1652/1653错误:
sqlldr DIRECT=Y failes with ORA-1652 or 1653

3) 分区维护导致ROWID发生改变:
ALTER TABLE MOVE PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER TABLE SPLIT PARTITION

****************************************************
索引失效问题解决方法：
1)导致的原因：
在SQL*LOADER 加载过程中会维护索引，由于数据量比较大，在SQL*LOADER 加载过程中出现异常情况，导致ORACLE 来不及维护索引，导致索引处于失效状态，影响查询和加载。
异常情况主要有：在加载过程中杀掉SQL*LOADER 进程，重启，表空间不够等。

2）解决方法：
重建索引
3）如何重建索引
a) 查看索引类型
select t1.index_name,t1.partitioned from Dba_Indexes t1
where t1.Table_Name=upper('CCB_COGNOS_PROD_BALANCE_AA')
索引名称是否分区索引
GNOS_PROD_BALANCE_AA_N1 NO
b)非分区索引
重建索引：alter index cin.CCB_COGNOS_PROD_BALANCE_AA_N1 rebuild Nologging
c)分区索引
找出失效的分区索引：
select t.Index_Name, t.Partition_Name, t.Tablespace_Name, t.Status
from Dba_Ind_Partitions t
where t.Index_Name = 'CMZ_LOCAL_IDX_2'

重建所有状态为unusable的索引
ALTER INDEX 索引名
REBUILD PARTITION 分区名
TABLESPACE 表空间名
NOLOGGING

【转】ORACLE索引失效解决方案

最近碰到这样一个问题：在PROD_PARTS表中新添加了一个索引：

create index IDX_PT_DV_ID on PROD_PARTS (DEVICE_ID);

但是在使用DEVICE_ID字段进行查询时，发现该索引并没有被利用到：

SELECT * FROM PROD_PARTS WHERE device_id =122511619;
执行计划：
TABLE ACCESS FULL

之后请教DBA后，发现是数据统计的问题，具体的解决办法是执行下面的语句：

analyze table PROD_PARTS compute statistics;
ANALYZE TABLE PROD_PARTS COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

analyze table PROD_PARTS compute statistics for table for all indexes for all indexed columns;

posted @ 2012-02-16 22:43 kelin1314 阅读(1637) 评论(0) 编辑收藏举报

刷新页面返回顶部

我的编程之路

索引失效情形

公告