我有下面的一个SQL语句,在FRAME表上根据ID、SUBSTATION_ID和CONTAINER_ID分别建立的索引。但是查看这个语句的执行计划,不管是外层的那个FRAME还是内层的那个FRAME使用的都是全表扫描,而没有使用索引。但是如果in后面跟的是('1234','2345') 这样的形式却会使用ID建立的索引。大家帮忙分析一下,看是什么原因造成的吗?我查过了,表中建立索引的字段数据类型是一致的,不存在需要转换的字段SELECT DECODE(C.USE_STATUS,'1','空闲','2','占用','3','预占') AS 状态,COUNT(C.ID) AS CNT
FROM CONNECTOR C,
FRAME D
WHERE C.CABLE_DEVICE_ID = D.ID
AND D.COL_ROW = '横'
AND D.CONTAINER_ID IN
( SELECT DISTINCT A.CONTAINER_ID
FROM FRAME A,
BLOCK B
WHERE A.ID = B.MDF_FRAME_ID
AND A.SUBSTATION_ID IN (SELECT DISTINCT ID FROM SUBSTATION WHERE STD_CODE LIKE '2101%')
AND A.COL_ROW = '直'
AND B.EXCH_CODE = '2251'
)
AND C.SERIAL_FAULT = '1'
AND C.RES_TYPE = '5'
GROUP BY C.USE_STATUS
FROM CONNECTOR C,
FRAME D
WHERE C.CABLE_DEVICE_ID = D.ID
AND D.COL_ROW = '横'
AND D.CONTAINER_ID IN
( SELECT DISTINCT A.CONTAINER_ID
FROM FRAME A,
BLOCK B
WHERE A.ID = B.MDF_FRAME_ID
AND A.SUBSTATION_ID IN (SELECT DISTINCT ID FROM SUBSTATION WHERE STD_CODE LIKE '2101%')
AND A.COL_ROW = '直'
AND B.EXCH_CODE = '2251'
)
AND C.SERIAL_FAULT = '1'
AND C.RES_TYPE = '5'
GROUP BY C.USE_STATUS
如果不行,就用hint,强制用索引即可。