Oracle 10G-从9i迁移后，使用rownum的查询停止工作 [英] Oracle 10G - Query using rownum stopped working after migration from 9i
问题描述
我们最近将数据库从9i迁移到了10G (是的..迟到总比没有好，而且不-当前不能选择移动到11g:-))
We just recently moved our DB from 9i to 10G (Yes..better late than never and No - moving to 11g is currently not an option :-))
我的Oracle 10G DB的详细信息是:-
Details of my Oracle 10G DB are :-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
自从这一举动以来，我面临着一个非常奇怪的问题. 在9i上可以正常使用的查询在10G上无法正常工作.
I am faced with a very weird problem since that move. A query that was and still is working fine with 9i just wont work on 10G.
我确实搜索了与rownum相关的其他SO问题，但找不到任何类似的东西.
I did search through other SO questions related to rownum but couldnt really find anything similar.
SQL查询是:-
SELECT * FROM
( SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rownum) field8
FROM
( SELECT
field1,
field2,
field3,
field4,
field5,
field6,
field7,
''
FROM
.......REST OF MY COMPLEX INNER QUERY
)
)
WHERE field8 BETWEEN 21 AND 30;
基本上，21/30是数字，它们是传递给查询以进行分页的记录的索引，在9i中，此查询的工作方式与预期的一样，并且仅返回指定的数据集.
Basically, the 21 / 30 are numbers that are the index of the records passed to the query for pagination and in 9i, this query works like expected and returns the specified set of data only.
但是，在10G中，该查询根本不起作用-始终返回0条记录.
However in 10G, this same query does not work at all - always returns 0 records.
如果我注释查询中与rownum相关的部分:-
If i comment the rownum related parts of the query:-
to_char(rownum) field8 and
WHERE field8 BETWEEN 21 AND 30;
然后我得到了整个结果集，那就太好了. 但是，由于我的意图是使用rownum进行分页，因此整个目的被挫败了.
then i get the entire result set and thats great. But since my intention is to do pagination using the rownum, the entire purpose is defeated.
是否有人知道此查询停止使用10G的任何原因. 我尝试查找对rownum实现的任何更新，但还没有真正遇到任何有帮助的东西.
Does anyone know of any reason why this query has stopped working with 10G. I tried looking up any updates to the rownum implementation but havent been able to really come across anything that will help.
- 在进行调试时，遇到了一些对我来说毫无意义的事情. 我在下面输入了整个查询，因为没有它我无法解释.
EDIT :- While doing my debugging, i have come across something that to me, is making no sense. I am putting in the entire query below as i cant explain without it.
SELECT * FROM
( SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rownum) field8 from
( SELECT PM.POLICY_NO field1
,PM.INSURED_CODE field2
,PM.INSURED_NAME field3
,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
,'' field6
,'' field7
,'' field8
FROM POLICY_MAIN PM
,POLICY_ENDORSEMENT_MAIN PEM
,MASTER_UW_LOB_CLASS MAS
WHERE PM.POLICY_NO = PEM.POLICY_NO
AND PM.POLICY_NO LIKE UPPER('%%')
AND PM.INSURED_CODE LIKE UPPER('%%')
AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
AND PM.POLICY_TYPE IS NULL
AND PM.POLICY_STATUS = 'POST'
AND PM.POLICY_LOB = MAS.UW_LOB_CODE
AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
AND PEM.POLICY_ENDORSEMENT_NO =
(SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO)
FROM POLICY_ENDORSEMENT_MAIN PEM2
WHERE PEM.POLICY_NO = PEM2.POLICY_NO
***AND PEM.ENDORSEMENT_STATUS = 'POST'***
)
***order by 1 ASC***
)
)
WHERE field8 BETWEEN 21 AND 40
请参考最里面的子查询中***之间标记的行.
Refer the lines marked between *** in the innermost subquery.
-
如果我在查询中对此行进行注释，则查询工作正常.
If i comment this line from my query, the query works fine.
AND PEM.ENDORSEMENT_STATUS ='POST'
如果我在查询中注释了这一行，而其他所有内容均保持原始状态不变，那么查询也可以正常工作
If i comment this line from my query and everything else remains unchanged from the original, the query works fine too
按1个ASC排序
order by 1 ASC
与rownum有关的早期观点仍然成立，但是单独注释这些行似乎使rownum无关紧要，并且整个查询工作正常(除了现在的结果在逻辑上有所不同这一事实之外)
The earlier points related to rownum still hold true but commenting these lines individually seems to be making the rownum thing irrelevant and the entire query works fine (except for that fact that the results are logically different now)
我很困惑.至少可以说！！
I am confused. To say the least!!!
为上述查询添加执行计划
Adding the execution plan for the above query
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19 Card=1 Bytes=114)
1 0 VIEW (Cost=19 Card=1 Bytes=114)
2 1 COUNT
3 2 FILTER
4 3 VIEW (Cost=17 Card=1 Bytes=128)
5 4 SORT (ORDER BY) (Cost=17 Card=1 Bytes=130)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_ENDORSEMENT_MAIN' (TABLE) (Cost=2 Card=1 Bytes=39)
7 6 NESTED LOOPS (Cost=16 Card=1 Bytes=130)
8 7 NESTED LOOPS (Cost=14 Card=1 Bytes=91)
9 8 TABLE ACCESS (FULL) OF 'POLICY_MAIN' (TABLE) (Cost=14 Card=1 Bytes=82)
10 8 INDEX (UNIQUE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=9)
11 7 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=1 Card=1)
12 3 SORT (AGGREGATE)
13 12 FILTER
14 13 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=2 Card=2 Bytes=68)
与上述查询完全相同，但如果我删除
Exact same query as above but if i remove the
ORDER BY 1 ASC
子句，然后按预期检索结果.
clause, then the results are retrieved as expected. The PLAN for this query without the order by is below
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=1 Bytes=114)
1 0 VIEW (Cost=18 Card=1 Bytes=114)
2 1 COUNT
3 2 FILTER
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_ENDORSEMENT_MAIN' (TABLE) (Cost=2 Card=1 Bytes=39)
5 4 NESTED LOOPS (Cost=16 Card=1 Bytes=130)
6 5 NESTED LOOPS (Cost=14 Card=1 Bytes=91)
7 6 TABLE ACCESS (FULL) OF 'POLICY_MAIN' (TABLE) (Cost=14 Card=1 Bytes=82)
8 6 INDEX (UNIQUE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=9)
9 5 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=1 Card=1)
10 3 SORT (AGGREGATE)
11 10 FILTER
12 11 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=2 Card=2 Bytes=68)
请注意，这两个计划之间的唯一真正区别是，不起作用的计划在步骤3之后具有以下两个附加步骤，因为如果没有按-的顺序，这些步骤就不会出现在查询中.
Note that the only real difference between the two plans is that the one that is not working has the following two additional steps after step 3 where as these steps are not present in the query without the order by - which is working fine.
按预期，步骤5是完成数据排序的步骤.
As expected, step 5 is the step where the ordering of the data is being done.
4 3 VIEW (Cost=17 Card=1 Bytes=128)
5 4 SORT (ORDER BY) (Cost=17 Card=1 Bytes=130)
由于顺序，似乎第4步可能是正在创建的其他视图.
It seems that step 4 is maybe an additional view being created due to the ordering.
这为什么应该阻止rownum逻辑起作用，这是我仍在尝试掌握的内容.
WHY this should prevent the rownum logic from working is what i am still trying to grasp.
任何帮助表示赞赏！
编辑4 -9i环境中的原始查询计划
EDIT 4 - Original Query plan from 9i environment
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 COUNT
3 2 VIEW
4 3 SORT (ORDER BY)
5 4 FILTER
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_MAIN'
7 6 NESTED LOOPS
8 7 NESTED LOOPS
9 8 TABLE ACCESS (FULL) OF 'POLICY_ENDORSEMENT_MAIN'
10 8 INDEX (RANGE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (UNIQUE)
11 7 INDEX (RANGE SCAN) OF 'PK_POLICY_MAIN' (UNIQUE)
12 5 SORT (AGGREGATE)
13 12 FILTER
14 13 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (UNIQUE)
推荐答案
正如亚当(Adam)所建议的那样，在应用了sort和ROWNUM之后，子查询正在过滤结果.
As Adam has suggested, the subquery is filtering the results after the sort and ROWNUM are applied.
我认为您需要通过使用PUSH_SUBQ提示来迫使该子查询更早地被过滤:
I think you need to force that subquery to be filtered earlier, by using the PUSH_SUBQ hint:
SELECT * FROM
( SELECT field1, field2 , field3, field4, field5, field6, field7,
ROWNUM field8 from
( SELECT PM.POLICY_NO field1
,PM.INSURED_CODE field2
,PM.INSURED_NAME field3
,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
,'' field6
,'' field7
,'' field8
FROM POLICY_MAIN PM
,POLICY_ENDORSEMENT_MAIN PEM
,MASTER_UW_LOB_CLASS MAS
WHERE PM.POLICY_NO = PEM.POLICY_NO
AND PM.POLICY_NO LIKE UPPER('%%')
AND PM.INSURED_CODE LIKE UPPER('%%')
AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
AND PM.POLICY_TYPE IS NULL
AND PM.POLICY_STATUS = 'POST'
AND PM.POLICY_LOB = MAS.UW_LOB_CODE
AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
AND PEM.POLICY_ENDORSEMENT_NO =
(SELECT /*+ PUSH_SUBQ*/
MAX(PEM2.POLICY_ENDORSEMENT_NO)
FROM POLICY_ENDORSEMENT_MAIN PEM2
WHERE PEM.POLICY_NO = PEM2.POLICY_NO
AND PEM.ENDORSEMENT_STATUS = 'POST'
)
order by 1 ASC
)
)
WHERE field8 BETWEEN 21 AND 40
我还从ROWNUM中删除了TO_CHAR-您想使用数字进行范围比较.
I've also removed the TO_CHAR from the ROWNUM - you want to use numbers for that range comparison.
编辑
尝试#2-改用CTE:
Try #2 - use CTE instead:
WITH q AS
( SELECT /*+MATERIALIZE*/
field1, field2 , field3, field4, field5, field6, field7,
ROWNUM field8 from
( SELECT PM.POLICY_NO field1
,PM.INSURED_CODE field2
,PM.INSURED_NAME field3
,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
,'' field6
,'' field7
,'' field8
FROM POLICY_MAIN PM
,POLICY_ENDORSEMENT_MAIN PEM
,MASTER_UW_LOB_CLASS MAS
WHERE PM.POLICY_NO = PEM.POLICY_NO
AND PM.POLICY_NO LIKE UPPER('%%')
AND PM.INSURED_CODE LIKE UPPER('%%')
AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
AND PM.POLICY_TYPE IS NULL
AND PM.POLICY_STATUS = 'POST'
AND PM.POLICY_LOB = MAS.UW_LOB_CODE
AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
AND PEM.POLICY_ENDORSEMENT_NO =
(SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO)
FROM POLICY_ENDORSEMENT_MAIN PEM2
WHERE PEM.POLICY_NO = PEM2.POLICY_NO
AND PEM.ENDORSEMENT_STATUS = 'POST'
)
order by 1 ASC
)
)
SELECT * from q
WHERE field8 BETWEEN 21 AND 40
这篇关于Oracle 10G-从9i迁移后，使用rownum的查询停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
