ansaurus

Question

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

Answer 1

+1 A:

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?)

Tony Andrews 2010-08-03 08:50:16

@Tony - Will try the nomerge hint and update further. About the to_char, this query is actually part of a bigger query with a union all clause where this last column is expected to be a string - hence the to_char - i cut out the irrevelant part of the query just to make it simpler to understand.

InSane 2010-08-03 10:05:32

@Tony - Your answer definitely made sense and i tried out the nomerge hint but no dice :-( :-(

InSane 2010-08-03 10:35:44

@InSane, Try the `/*+MATERIALIZE*/` hint instead - and put the hint in the inner select, not the outer one.

Jeffrey Kemp 2010-08-04 04:58:04

@Jeffrey - Thanks for the inputs! Tried out your suggestion as well. No luck. I have found out something more (weird!) in the course of my debugging. Adding that info as "Edit" to the original post.

InSane 2010-08-04 06:10:14

The sort (ORDER BY) in the inner query may require the optimiser to not merge the inner query, and may be the reason why it works in one version; however, Oracle can avoid a sort operation in some scenarios (e.g. if a suitable index is used in the plan to access the rows in the right order), in which case Oracle may find a plan where merging is allowed.

Jeffrey Kemp 2010-08-05 01:24:35

Answer 2

+1 A:

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:

Restructured joins to use ANSI syntax to differentiate joins from filters.
Changed LIKE UPPER('AUTO') to = 'AUTO'
Removed unnecessary level of nesting.
Changed order by to use expression vs. positional notaion
Moved filtering criteria PEM.ENDORSEMENT_STATUS = 'POST' from correlated subquery to main query, which may correct wrong results issue.
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.

Adam Musch 2010-08-04 14:02:57

Don't forget, ROWNUM is applied before sorting - so this might not return the results expected; you probably do need the additional level of nesting.

Jeffrey Kemp 2010-08-05 01:22:20

@Adam - Thanks for the changes - though your queries might work - my immediate concern is to try and figure out why the query i posted is working differently from 9i to 10g. As its an existing application with a few hundred queries similar to the one i posted, i ideally do not want to make major changes like the ones you suggested to all of them at this time as it will involve a huge amount of retesting

InSane 2010-08-05 02:10:43

Answer 3

A:

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.

BillThor 2010-08-08 21:20:26

@Bill - My inner query "SELECT PM.POLICY_NO field1 PM......" is an attempt at first building the entire result set...The outer select is just an extra select query in order to filter out the rows from that resultset using the rownum in the outer query" - Is that different from what you are suggesting?

InSane 2010-08-10 04:22:34

Also, You are right in that Explain Plan has helped by indicating that a additional view is being created which seems to be messing up my rownum logic.What i am really trying to get at is why? This is because this is an existing application migrated from 9i where the same queries work fine and i dont want to change all these queries due to the testing effort involved. I am not sure if some of the settings are causing the optimizer to work differently and maybe i can get by changing those settings instead! I hope that explains my predicament a bit better!!

InSane 2010-08-10 04:23:40

10g automatically generates statistics. These needed to be manually generated in 9i. If you weren't generating statistics, the resulting plan may have been significantly different than you get with statistics generated. In general, good statistics generate better plans. However, there are exceptions and your case may be one. If you can still run the query in a 9i environment, it would be interesting to compare the plans.

BillThor 2010-08-10 18:59:44

@Bill - Added the original query plan from the 9i environment for comparision as Edit 4 in original post. The 10g query plan for the same query is Edit 2. If you compare the 2, the only key difference seems to be that the "FILTER" step from Step 5 in my 9i plan is now occuring at Step 3 in my 10g plan which makes all the difference to the rownum. Can u possibly throw any light on why this would be happening?

InSane 2010-08-11 03:19:47

The view that is being dropped separates the houre and middle select statements. This separation is what permits the rownum range select to work. Without it uou end up selecting on "rownum >= 20 and rownum <= 40. As rownum is generated on the selected rows the first candidate is always 1 and is rejected.

BillThor 2010-08-12 18:30:35

@Bill - Thanks for the explanation! That does help!!

InSane 2010-08-13 02:06:12

Answer 4

+2 A:

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

Jeffrey Kemp 2010-08-10 03:52:58

@Jeffrey - i had tried out the push_subq as suggested earlier but it didnt work either. Just to make sure, i tried out your query as well - but it doesnt work either. I cant paste it below but the query plan for your query is almost exactly same as the query plan i have posted in EDIT 2 except for the row "3 2 FILTER" which is the only step missing. The additional view being created in step 4 - "4 3 VIEW (Cost=17 Card=1 Bytes=128)" is the one that is messing up the rownum. Are there some DB settings that may cause the hint PUSH_SUBQ to be ignored?

InSane 2010-08-10 04:15:59

edited - another idea to try :)

Jeffrey Kemp 2010-08-10 07:50:31

BRILLIANT - the CTE version works exactly as expected!! In fact, i tried by even removing the MATERIALIZE hint and it still works exactly as expected i.e the change in the query syntax to use the CTE is making the subquery to evaluate first hence providing rownum with values - hence making the where clause work as expected!! When i compared the 2 query plans - my original query plan i.e. EDIT2 v/s the CTE's plan, the only difference is "FILTER" step from Step 3 in my plan is now occuring at Step 5 which makes all the difference. Can u throw some light on why?

InSane 2010-08-11 03:00:41

Note, you may find that without the MATERIALIZE hint, Oracle may choose later on (e.g. in a later version) to not materialize the query.

Jeffrey Kemp 2010-08-11 04:39:07

Is using the query hints a good idea in Oracle? I come from a SQL server background and have usually considered it not such a great idea to use the DB hints much...due to hints being interpreted differently across versions or hints resulting in non optimal query plans - In SQL Server, i used to go with the understanding that the optimizer knows best. In the oracle world, is using hints common practice?

InSane 2010-08-11 11:51:07

No, generally it's a good **rule of thumb** to avoid hints for the reasons you give. However, this is a case where a hint is being used exactly for the purpose it was made - we *don't* want the optimizer to choose another plan in the future - so we add the hint to make sure.

Jeffrey Kemp 2010-08-12 02:30:10

@Jeffrey - thanks!

InSane 2010-08-12 08:36:27

ansaurus

tags:

views:

answers:

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

related questions