--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
GO
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
)T
WHERE COL LIKE 'A%'
SELECT * FROM
(
SELECT COL FROM @A
UNION ALL
SELECT COL FROM @C
)T
WHERE COL LIKE 'A%'
GO
SET SHOWPLAN_TEXT ON
--结果:列类型不一致会导致类型转换,低向高精度转换,最后引起索引失效
GO
/*
StmtText
----------------------------------------------------------------------------------------------------
SELECT * FROM
(
SELECT COL FROM @A
UNION ALL
SELECT COL FROM @B
)T
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
)T
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
GO
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)
GO
SET SHOWPLAN_TEXT OFF
GO
--结果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 )
GO
INSERT T SELECT 'ABCD'
GO
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;