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 :-))
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
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.
I did search through other SO questions related to rownum but couldnt really find anything similar.
SQL Query is :-
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;
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.
However in 10G, this same query does not work at all - always returns 0 records.
If i comment the rownum related parts of the query:-
to_char(rownum) field8 and
WHERE field8 BETWEEN 21 AND 30;
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.
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
order by 1 ASC
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!!!
EDIT 2:
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)
EDIT 3:
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)
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.
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)
It seems that step 4 is maybe an additional view being created due to the ordering.
WHY this should prevent the rownum logic from working is what i am still trying to grasp.
Any help appreciated!!
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)
As Adam has suggested, the subquery is filtering the results after the sort and ROWNUM are applied.
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
I've also removed the TO_CHAR from the ROWNUM - you want to use numbers for that range comparison.
EDIT
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