前些天有一个奇怪的现象，一条SQL语句不加order by是可以查出10条数据，加了order by后一条数据都查不出来。
用的oracle版本是10.0.1。由于所用的表不能贴出来，所以只能跟大家讲讲思路。
看此条SQL比较复杂，多层嵌套，有肉眼真是看不出来是什么问题，但是我有思路。因为我知道Oracle会对SQL进行查询转换，如in(1,2)会改为or等。所以我决定用10053事件看看两条SQL被转换成什么样了。
trace出来文件后，找到此条SQL的执行计划上的第一个******* UNPARSED QUERY IS *******，这个便是经过查询转换后的SQL语句。
trace出来的结果是两条SQL转换出来的查询语句大相径庭，最后改了一个子查询的写法，order by终于可以出数据了。
原始SQL:
SELECT *
FROM (SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT dg_730.GG_RESERVE_SCHEME_ITEM.RESERVE_LEVEL
FROM dg_730.GG_RESERVE_QUOTA ,
dg_730.GG_RESERVE_QUOTA_ITEM,
(SELECT T.RESERVE_SCHEME_ITEM_ID,
T.RESERVE_QUOTA_ITEM_ID
FROM dg_730.GG_RESERVE_SCHEME_RELATION T
WHERE T.RELATION_ID =
(SELECT MAX(T1.RELATION_ID)
FROM dg_730.GG_RESERVE_SCHEME_RELATION T1
WHERE T.RESERVE_QUOTA_ITEM_ID =
T1.RESERVE_QUOTA_ITEM_ID)) SR,
dg_730.GG_RESERVE_SCHEME_ITEM,
dg_730.GG_RESERVE_SCHEME
WHERE dg_730.GG_RESERVE_QUOTA.RESERVE_QUOTA_ID =
dg_730.GG_RESERVE_QUOTA_ITEM.RESERVE_QUOTA_ID
AND dg_730.GG_RESERVE_QUOTA_ITEM.RESERVE_QUOTA_ITEM_ID =
SR.RESERVE_QUOTA_ITEM_ID
AND SR.RESERVE_SCHEME_ITEM_ID =
dg_730.GG_RESERVE_SCHEME_ITEM.RESERVE_SCHEME_ITEM_ID
AND dg_730.GG_RESERVE_SCHEME.RESERVE_SCHEME_ID =
dg_730.GG_RESERVE_SCHEME_ITEM.RESERVE_SCHEME_ID
AND dg_730.GG_RESERVE_QUOTA_ITEM.STATUS IN
('schemed' , 'noScheme' , 'disabled')
AND dg_730.GG_RESERVE_QUOTA.PREPARE_DEPARTMENT_ID =
'1030000003'
AND dg_730.GG_RESERVE_QUOTA_ITEM.RESERVE_MATERIAL_TYPE=37
AND dg_730.GG_RESERVE_QUOTA.RESERVE_LEVEL= '1'
ORDER
BY RESERVE_SCHEME_NAME desc --加了这个，出不来数据
) INNER_TABLE)
WHERE OUTER_TABLE_ROWNUM <= 18) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0
这是trace处理的两条SQL：
SELECT /*+ */
"from$_subquery$_002"."OUTER_TABLE_ROWNUM" "OUTER_TABLE_ROWNUM"
FROM (SELECT /*+ */
"INNER_TABLE"."ORIGINAL_MATERIAL_ID" "ORIGINAL_MATERIAL_ID",
ROWNUM "OUTER_TABLE_ROWNUM"
FROM (SELECT /*+ */
"SYS_ALIAS_1"."RELATION_ID" "RELATION_ID"
FROM "DG__730"."GG_RESERVE_QUOTA" "GG_RESERVE_QUOTA",
"DG__730"."GG_RESERVE_QUOTA_ITEM" "GG_RESERVE_QUOTA_ITEM",
"DG__730"."GG_RESERVE_SCHEME_RELATION" "SYS_ALIAS_1",
"DG__730"."GG_RESERVE_SCHEME_ITEM" "GG_RESERVE_SCHEME_ITEM",
"DG__730"."GG_RESERVE_SCHEME" "GG_RESERVE_SCHEME"
WHERE "GG_RESERVE_QUOTA"."PREPARE_DEPARTMENT_ID" =
1030000003
AND "GG_RESERVE_QUOTA"."RESERVE_LEVEL" = 1
AND "GG_RESERVE_QUOTA"."RESERVE_QUOTA_ID" =
"GG_RESERVE_QUOTA_ITEM"."RESERVE_QUOTA_ID"
AND "GG_RESERVE_QUOTA_ITEM"."RESERVE_MATERIAL_TYPE" = 37
AND ("GG_RESERVE_QUOTA_ITEM"."STATUS" = 'disabled' OR
"GG_RESERVE_QUOTA_ITEM"."STATUS" = 'noScheme' OR
"GG_RESERVE_QUOTA_ITEM"."STATUS" = 'schemed')
AND "GG_RESERVE_QUOTA_ITEM"."RESERVE_QUOTA_ITEM_ID" =
"SYS_ALIAS_1"."RESERVE_QUOTA_ITEM_ID"
AND "SYS_ALIAS_1"."RESERVE_SCHEME_ITEM_ID" =
"GG_RESERVE_SCHEME_ITEM"."RESERVE_SCHEME_ITEM_ID"
AND "GG_RESERVE_SCHEME"."RESERVE_SCHEME_ID" =
"GG_RESERVE_SCHEME_ITEM"."RESERVE_SCHEME_ID"
ORDER BY "GG_RESERVE_SCHEME"."RESERVE_SCHEME_NAME"
DESC) "INNER_TABLE"
WHERE "INNER_TABLE"."RELATION_ID" =
(SELECT
/*+ */
MAX("T1"."RELATION_ID")
FROM "DG__730"."GG_RESERVE_SCHEME_RELATION" "T1"
WHERE "T1"."RESERVE_QUOTA_ITEM_ID" =
"INNER_TABLE"."RESERVE_QUOTA_ITEM_ID")) "from$_subquery$_002"
WHERE "from$_subquery$_002"."OUTER_TABLE_ROWNUM" > 0
AND "from$_subquery$_002"."OUTER_TABLE_ROWNUM" <= 18
SELECT /*+ */
"from$_subquery$_002"."OUTER_TABLE_ROWNUM" "OUTER_TABLE_ROWNUM"
FROM (SELECT /*+ */
"GG_RESERVE_QUOTA_ITEM"."ORIGINAL_MATERIAL_ID" "ORIGINAL_MATERIAL_ID",
ROWNUM "OUTER_TABLE_ROWNUM"
FROM "DG__730"."GG_RESERVE_QUOTA" "GG_RESERVE_QUOTA",
"DG__730"."GG_RESERVE_QUOTA_ITEM" "GG_RESERVE_QUOTA_ITEM",
"DG__730"."GG_RESERVE_SCHEME_RELATION" "SYS_ALIAS_1",
"DG__730"."GG_RESERVE_SCHEME_ITEM" "GG_RESERVE_SCHEME_ITEM",
"DG__730"."GG_RESERVE_SCHEME" "GG_RESERVE_SCHEME"
WHERE "SYS_ALIAS_1"."RELATION_ID" =
(SELECT
/*+ */
MAX("T1"."RELATION_ID")
FROM "DG__730"."GG_RESERVE_SCHEME_RELATION" "T1"
WHERE "T1"."RESERVE_QUOTA_ITEM_ID" =
"SYS_ALIAS_1"."RESERVE_QUOTA_ITEM_ID")
AND "GG_RESERVE_QUOTA"."PREPARE_DEPARTMENT_ID" = 1030000003
AND "GG_RESERVE_QUOTA"."RESERVE_LEVEL" = 1
AND "GG_RESERVE_QUOTA"."RESERVE_QUOTA_ID" =
"GG_RESERVE_QUOTA_ITEM"."RESERVE_QUOTA_ID"
AND "GG_RESERVE_QUOTA_ITEM"."RESERVE_MATERIAL_TYPE" = 37
AND ("GG_RESERVE_QUOTA_ITEM"."STATUS" = 'disabled' OR
"GG_RESERVE_QUOTA_ITEM"."STATUS" = 'noScheme' OR
"GG_RESERVE_QUOTA_ITEM"."STATUS" = 'schemed')
AND "GG_RESERVE_QUOTA_ITEM"."RESERVE_QUOTA_ITEM_ID" =
"SYS_ALIAS_1"."RESERVE_QUOTA_ITEM_ID"
AND "SYS_ALIAS_1"."RESERVE_SCHEME_ITEM_ID" =
"GG_RESERVE_SCHEME_ITEM"."RESERVE_SCHEME_ITEM_ID"
AND "GG_RESERVE_SCHEME"."RESERVE_SCHEME_ID" =
"GG_RESERVE_SCHEME_ITEM"."RESERVE_SCHEME_ID") "from$_subquery$_002"
WHERE "from$_subquery$_002"."OUTER_TABLE_ROWNUM" > 0
AND "from$_subquery$_002"."OUTER_TABLE_ROWNUM" <= 18
解决方案： 把里面的一个子查询修改，就可以了。
T.RELATION_ID in (SELECT MAX(T1.RELATION_ID)
FROM dg_730.GG_RESERVE_SCHEME_RELATION T1
group by RESERVE_QUOTA_ITEM_ID)