Oracle 10G - Query using rownum stopped working after migration from 9i

Question

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.

1. If i comment this line from my query, the query works fine.

   AND PEM.ENDORSEMENT_STATUS = 'POST'

2. 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)

Answer 1

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

Answer 2

It sounds like Oracle is mergeing the inline view into the main query so that field8 (based on ROWNUM) is calculated too late. I haven't seen that happen myself, but if that is what is happening you could try adding a NO_MERGE hint like this:

SELECT /*+ NO_MERGE(vw) */ * 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
  )
) vw
WHERE field8 BETWEEN 21 AND 30;

(Incidentally, why the TO_CHAR on ROWNMUM when you are treating it as a number in the WHERE clause anyway?)

Answer 3

Try this:

SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rn) 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 
         ,rownum as rn
   FROM POLICY_MAIN PM 
        inner join POLICY_ENDORSEMENT_MAIN PEM 
           on PM.POLICY_NO = PEM.POLICY_NO 
        inner join MASTER_UW_LOB_CLASS MAS 
           on PM.POLICY_LOB = MAS.UW_LOB_CODE 
  WHERE 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 MAS.UW_CLASS_CODE = 'AUTO'
    AND PEM.ENDORSEMENT_STATUS = 'POST'
    AND PEM.POLICY_ENDORSEMENT_NO = 
         (SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO) 
            FROM POLICY_ENDORSEMENT_MAIN PEM2 
           WHERE PEM.POLICY_NO           = PEM2.POLICY_NO 
         ) 
  order by pm.policy_no ASC) 
WHERE rn BETWEEN 21 AND 40 

Changes:

1. Restructured joins to use ANSI syntax to differentiate joins from filters.
2. Changed LIKE UPPER('AUTO') to = 'AUTO'
3. Removed unnecessary level of nesting.
4. Changed order by to use expression vs. positional notaion
5. Moved filtering criteria PEM.ENDORSEMENT_STATUS = 'POST' from correlated subquery to main query, which may correct wrong results issue.

6. Changed pagination condition to use a numeric expression rather than a character one, because:

   select * from dual where '211' between '21' and '40';

   select * from dual where 211 between 21 and 40;

Do not return the same results.

Answer 4

Explain plan should help you identify the problem. As Tony has stated, any merging of the inner query into the outer query will break your query. Any queries where the condition RONUM > 1 unconditionally applies will fail.

Queries such as you are building may require building the entire result set and then filtering out the rows for the page. You may want to consider building a key set for the desired rows in then inner query and then adding additional columns in the outer query. A CARDINALITY hint on the query selecting on rownum may help.

Try using "rownum() over (order by 1) rn" to generate the order. (I assume order is different than 1 at times.) Add a "/*+ FIRST_ROWS(20) */" to the first inner query. http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html for more help.