今天开发找我，说很奇怪，明明是有数据的，但就是查不出来数据，经过诊断，是Oracle11.2.0.1.0中降序索引的bug。

sql> select * from v$version;

NLSRTL Version 11.2.0.1.0 - Production

select find_time
from (WITH ORG AS (SELECT ORG_ID,ORG_NAME,NAME_FULL_PATH
FROM TOP_ORGANIZATION)
SELECT DT.*,
(SELECT listagg(substr(o.name_full_path,
instr(o.name_full_path,'/',-1,3) + 1,
length(o.name_full_path)),
';') WITHIN
GROUP(
ORDER BY 1)
FROM ORG O
WHERE O.ORG_ID = CASE WHEN INSTR(DT.FIND_TEAM_OID,';') = 0 THEN DT.FIND_TEAM_OID ELSE SUBSTR(DT.FIND_TEAM_OID,INSTR(DT.FIND_TEAM_OID,';') - 1) END) FIND_TEAM_ONAME,
NVL2(DT.LIGHT,
DECODE(DT.LIGHT,
1,
'red-light.png',
2,
'yellow-light.png',
3,
'green-light.png',
''),
'') AS GG_LIGHT_PICTURE
FROM (SELECT /*C.ID CONCERN_ID,*/
D.ID,
D.PARENT_GG_ID,
D.GG_PHENOMENON_ID,
D.GG_POSITION_ID,
D.GG_TYPE_ID,
D.GG_TYPE_NAME,
D.PROVINCE_CODE,
D.GG_CODE,
D.STATE,
D.SPECIALITY_TYPE,
D.SPECIALITY,
D.HAS_RETRO,
D.IS_PROJECT_PERIOD,
D.GG_LEVEL,
D.DEAL_MEASURE,
D.FIND_SOURCE,
D.GG_SOURCE_TYPE,
D.SITE_ID,
D.SITE_NAME,
D.FUNCTION_LOCATION_ID,
D.FUNCTION_LOCATION_NAME,
D.DEVICE_ID,
D.DEVICE_NAME,
D.PARTS_ID,
D.PARTS_NAME,
D.CLASSIFY_ID,
D.VENDOR_NAME,
D.DEVICE_RELEASE_DATE,
D.MODEL_NAME,
D.DEVICE_RUN_DATE,
D.RUNMANAGE_TEAM_OID,
D.RUNMANAGE_TEAM,
D.VOLTAGE_LEVEL,
D.FINDER_UID,
D.FIND_TEAM_OID,
D.FIND_TIME,
D.CREATOR_UID,
D.TEAM_OID,
D.DEPT_OID,
D.CREATE_DATE,
D.REPORTOR_UID,
D.REPORT_TEAM_OID,
D.REPORT_TIME,
D.DEAL_MAN_UID,
D.DEAL_TEAM_OID,
D.DEAL_TIME,
D.TECH_CLASSIFY,
D.OUGHT_DEAL_TIME,
D.DEAL_DUTY_DEPT_OID,
D.DEAL_RESULT,
D.UNDEAL_REASON,
D.CHECKER_UID,
D.CHECK_DEPT_OID,
D.CHECK_TIME,
D.GG_PHENOMENON,
D.CHECK_NOTES,
D.GG_DESC,
D.GG_CAUSE,
D.GG_POSITION,
D.LEGACY,
D.DEAL_DESC,
D.FLOW_STATE,
D.PROCESS_INS_ID,
D.OPTIMISTIC_LOCK_VERSION,
D.UPDATE_TIME,
D.DATA_FROM,
(SELECT DC.FULL_NAME
FROM DM_CLASSIFY DC
WHERE DC.ID = D.CLASSIFY_ID) AS ALL_CLASSIFY,
(SELECT C.CLASSIFY_NAME
FROM DM_CLASSIFY C
WHERE C.ID = D.CLASSIFY_ID) AS CLASSIFY_NAME,
(SELECT B.PARTITION_NAME
FROM SP_PARTITION_CODE B
WHERE B.PARTITION_VALUE = D.GG_CODE) as BEREAU_CODE_TEXT,
CASE GG_LEVEL
WHEN 4 THEN
''
ELSE
CASE STATE
WHEN 1 THEN
''
ELSE
NVL2(DEAL_TIME,
DECODE(SIGN(D.OUGHT_DEAL_TIME - D.DEAL_TIME),
-1,
'1',
'3'),
DECODE(SIGN(D.OUGHT_DEAL_TIME - SYSDATE - 40),
DECODE(SIGN(D.OUGHT_DEAL_TIME - SYSDATE),
-1,
'1',
2),
''))
END END AS LIGHT,DECODE(SIGN(D.OUGHT_DEAL_TIME - D.DEAL_TIME),'警告：缺陷未及时处理,实际处理时间为：' || TO_CHAR(D.DEAL_TIME,'yyyy-MM-dd') ||',应处理时间：' || TO_CHAR(D.OUGHT_DEAL_TIME,'yyyy-MM-dd'),'提示：缺陷及时处理,'yyyy-MM-dd')),DECODE(SIGN(D.OUGHT_DEAL_TIME - SYSDATE - 40),DECODE(SIGN(D.OUGHT_DEAL_TIME - SYSDATE),'警告：已过' || CASE
WHEN ceil((SYSDATE - D.FIND_Time) * 24) >= 24 THEN
round(ceil((SYSDATE - D.FIND_Time) * 24) / 24,0) || '天' ||
mod(ceil((SYSDATE - D.FIND_Time) * 24),24)
else
to_char(ceil((SYSDATE - D.FIND_Time) * 24))
end || '小时,超过了' || CASE
WHEN ceil((SYSDATE - D.Ought_Deal_Time) * 24) >= 24 THEN
round(ceil((SYSDATE - D.Ought_Deal_Time) * 24) / 24,0) || '天' ||
mod(ceil((SYSDATE - D.Ought_Deal_Time) * 24),24)
else
to_char(ceil((SYSDATE - D.Ought_Deal_Time) * 24))
end || '小时及时处理时间！',
'提醒：已过' || CASE
WHEN ceil((SYSDATE - D.FIND_TIME) * 24) >= 24 THEN
round(ceil((SYSDATE - D.FIND_TIME) * 24 * 60) / 24 / 60,
0) || '天' ||
mod(ceil((SYSDATE - D.FIND_TIME) * 24),24)
else
to_char(ceil((SYSDATE - D.FIND_TIME) * 24))
end || '小时,还剩' || CASE
WHEN ceil((D.Ought_Deal_Time - SYSDATE) * 24) >= 24 THEN
round(ceil((D.Ought_Deal_Time - SYSDATE) * 24 * 60) / 24 / 60,
0) || '天' ||
mod(ceil((D.Ought_Deal_Time - SYSDATE) * 24),24)
else
to_char(ceil((D.Ought_Deal_Time - SYSDATE) * 24))
end || '小时处理时间'),'')) END END AS GG_LIGHT_NOTE,
(SELECT ID
FROM SP_PD_GG_LEVEL_CHANGE L
WHERE L.GG_ID(+) = D.ID
AND ROWNUM = 1) AS GG_LEVEL_CHANGE_ID,
(SELECT COUNT(1)
FROM SP_PD_PP_BUSINESS_RE R
WHERE R.ASSOCIATED_BUSI_OBJECT_ID = D.ID
AND R.PLAN_BUSINESS_RE_TYPE = 'SourceGG') AS GG_RELATION_PROD_PLAN,
(SELECT ORG_NAME NAME
FROM ORG B
WHERE D.REPORT_TEAM_OID = B.ORG_ID) REPORT_TEAM_ONAME,
(SELECT ORG_NAME NAME
FROM ORG B
WHERE D.DEAL_TEAM_OID = B.ORG_ID) DEAL_TEAM_ONAME,
(SELECT ORG_NAME NAME
FROM ORG B
WHERE D.CHECK_DEPT_OID = B.ORG_ID) CHECK_DEPT_ONAME
FROM SP_PD_GG D /*,SPROC_CONCERN C*/
WHERE 1 = 1 /*AND D.ID = C.BUSINESS_ID(+)*/
AND D.GG_LEVEL IN ('1','2','3')
AND D.FIND_TIME >=
to_date('2016-1-22 0:00:00','yyyy-mm-dd hh24:mi:ss')
AND D.FIND_TIME <=
to_date('2016-12-22 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
AND D.GG_CODE = '0306') DT
ORDER BY find_time DESC)
WHERE find_time <=
to_date('2016/12/06 11:40:00','yyyy/mm/dd hh24:mi:ss');


create index IDX_GG_FINDTIME on SP_PD_GG (FIND_TIME desc) 建成这种索引查不出来数据,这个索引在user_indexes中index_type为FUNCTION-BASED NORMAL。

可以看到查不出来数据。
执行计划
----------------------------------------------------------
Plan hash value: 1489160161
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 0 (0)| | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| SP_PD_GG | 7472 | 408K| 57 (0)| 00:00:01 | 4 | 4 |
|* 3 | INDEX RANGE SCAN | IDX_GG_FINDTIME | 95 | | 6 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter(("SYS_ALIAS_9"."GG_LEVEL"=1 OR "SYS_ALIAS_9"."GG_LEVEL"=2 OR "SYS_ALIAS_9"."GG_LEVEL"=3)
AND "SYS_ALIAS_9"."GG_CODE"='0306')
3 - access(SYS_OP_DESCEND("FIND_TIME")>=HEXTORAW('878BF3F9F3D6FEFAFF') AND
SYS_OP_DESCEND("FIND_TIME")<=HEXTORAW('878BFEE9FEF8FEFAFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("FIND_TIME"))>=TO_DATE(' 2016-01-22 00:00:00','syyyy-mm-dd
hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
256 bytes sent via sql*Net to client
1052 bytes received via sql*Net from client
1 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

可以看到现在可以查出来205条数据。
drop index IDX_GG_FINDTIME;
create index IDX_GG_FINDTIME on SP_PD_GG (FIND_TIME);
执行计划
----------------------------------------------------------
Plan hash value: 1132063820
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7472 | 408K| 1916 (1)| 00:00:23 | | |
| 1 | SORT ORDER BY | | 7472 | 408K| 1916 (1)| 00:00:23 | | |
| 2 | PARTITION LIST SINGLE| | 7472 | 408K| 1915 (1)| 00:00:23 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | SP_PD_GG | 7472 | 408K| 1915 (1)| 00:00:23 | 4 | 4 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."FIND_TIME">=TO_DATE(' 2016-01-22 00:00:00','syyyy-mm-dd hh24:mi:ss') AND
("D"."GG_LEVEL"=1 OR "D"."GG_LEVEL"=2 OR "D"."GG_LEVEL"=3) AND
"D"."FIND_TIME"<=TO_DATE(' 2016-12-06 11:40:00','syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7058 consistent gets
0 physical reads
0 redo size
3403 bytes sent via sql*Net to client
1306 bytes received via sql*Net from client
15 sql*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
205 rows processed