This discussion is archived
7 Replies Latest reply: May 2, 2013 11:41 AM by Christophe Lize RSS

ROWNUM and execution plan

Christophe Lize Newbie
Currently Being Moderated
Hi,

I'm facing something a little bit weird with the ROWNUM predicate. I did not build a test case to show you the problem but I will try to explain it:
Oracle 11.2.0.2 on AIX
3 staging tables partitioned by date (SYS_DT_EXTRACT) joined together
SELECT A.*,
       B.*,
       C.*
  FROM TEST1 A, --14000 rows returned with given predicates
       TEST2 B, --935950 rows returned with given predicates
       TEST3 C --724619 rows returned with given predicates
 WHERE     A.SYS_DT_EXTRACT = '30-APR-2013'
       AND B.SYS_DT_EXTRACT = '30-APR-2013'
       AND C.SYS_DT_EXTRACT = '30-APR-2013'
       AND A.CTNCPT = B.CTNCPT
       AND B.CTNCPT = C.CTNOCP
       AND B.CTDFER = 00000000
       AND A.CLTYRL = '08'
       AND C.CTTXST = '00'
       AND ROWNUM < 2;

--------------------------------------------------------------------------------------------------------------------
| Operation                 | Name                         | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                              |     1 |  1303 | 20017   (2)| 00:04:41 |       |       |
|  COUNT STOPKEY            |                              |       |       |            |       |  |       |
|   NESTED LOOPS            |                              |     2 |  2606 | 20017   (2)| 00:04:41 |       |       |
|    HASH JOIN              |                              |     2 |  2410 | 10726   (2)| 00:02:31 |       |       |
|     PARTITION RANGE SINGLE|                              |   957 |   372K| 10712   (2)| 00:02:30 |   852 |   852 |
|      TABLE ACCESS FULL    | TEST2                        |   957 |   372K| 10712   (2)| 00:02:30 |   852 |   852 |
|     PARTITION RANGE SINGLE|                              |   794 |   312K|    13   (0)| 00:00:01 |   852 |   852 |
|      TABLE ACCESS FULL    | TEST3                        |   794 |   312K|    13   (0)| 00:00:01 |   852 |   852 |
|    PARTITION RANGE SINGLE |                              |     1 |    98 |  4646   (2)| 00:01:06 |   852 |   852 |
|     TABLE ACCESS FULL     | TEST1                        |     1 |    98 |  4646   (2)| 00:01:06 |   852 |   852 |
--------------------------------------------------------------------------------------------------------------------

With ROWNUM < 3;

--------------------------------------------------------------------------------------------------------------------
| Operation                 | Name                         | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                              |     2 |  1800 | 20952   (2)| 00:04:54 |       |       |
|  COUNT STOPKEY            |                              |       |       |            |       |  |       |
|   HASH JOIN               |                              |     3 |  2700 | 20952   (2)| 00:04:54 |       |       |
|    HASH JOIN              |                              |   964 |   467K| 15353   (2)| 00:03:35 |       |       |
|     PARTITION RANGE SINGLE|                              |   957 |   372K| 10712   (2)| 00:02:30 |   852 |   852 |
|      TABLE ACCESS FULL    | TEST2                        |   957 |   372K| 10712   (2)| 00:02:30 |   852 |   852 |
|     PARTITION RANGE SINGLE|                              |   245K|    22M|  4640   (2)| 00:01:05 |   852 |   852 |
|      TABLE ACCESS FULL    | TEST1                        |   245K|    22M|  4640   (2)| 00:01:05 |   852 |   852 |
|    PARTITION RANGE SINGLE |                              |   382K|   146M|  5596   (2)| 00:01:19 |   852 |   852 |
|     TABLE ACCESS FULL     | TEST3                        |   382K|   146M|  5596   (2)| 00:01:19 |   852 |   852 |
--------------------------------------------------------------------------------------------------------------------
With ROWNUM<2 the query takes forever because the plan uses a nested loop as the cardinality found on TEST1 is 1. With ROWNUM<3 the cardinality is better computed and the order of the table is changed and a HASH is used. The query then return in few seconds.
Histograms are not computed so that could explain why computed cardinality is so bad.

Any idea on the problem? I checked for a bug on Oracle Support with no success...

Thanks

Christophe
  • 1. Re: ROWNUM and execution plan
    rp0428 Guru
    Currently Being Moderated
    >
    With ROWNUM<2 the query takes forever because the plan uses a nested loop as the cardinality found on TEST1 is 1. With ROWNUM<3 the cardinality is better computed and the order of the table is changed and a HASH is used. The query then return in few seconds.
    Histograms are not computed so that could explain why computed cardinality is so bad.

    Any idea on the problem? I checked for a bug on Oracle Support with no success...
    >
    You seem to be stating that you don't really have a problem but are just wanting to know why you get the result you are getting.

    Because if you did have a problem you already know the solution you could use.

    So is this just idle curiousity?
  • 2. Re: ROWNUM and execution plan
    davidp 2 Pro
    Currently Being Moderated
    The plans are quite different. The cardinalities look OK to me - not necessarily correct, but reasonable guesses.
    The first plan expects to look in TEST1 for a single row matching
    A.SYS_DT_EXTRACT = '30-APR-2013' AND A.CTNCPT = B.CTNCPT AND A.CLTYRL = '08'
    That's why it says cardinality 1 on TEST1.

    The cost of this scan is estimated as 4646, which is why it switches to a hash join when looking for a second row - it expects to do the scan twice, costing 4646 each time, which makes the nested loop plan more expensive than the hash plan. I expect the optimizer's "mistake" is expecting the first scan to find a matching row.

    The second plan expects to scan TEST1 for rows that satisfy just A.SYS_DT_EXTRACT = '30-APR-2013' AND A.CLTYRL = '08' (the filter criteria without the join criteria) which gives the much larger cardinality.

    I am suspicious of
    B.CTDFER = 00000000
    - without quotes around the '00000000' - that makes 00000000 a numeric zero. Is the table column numeric or character ?
  • 3. Re: ROWNUM and execution plan
    Christophe Lize Newbie
    Currently Being Moderated
    Yes, I have a problem with rownum<2, the query never return... ;o)
    Thanks

    Christophe
  • 4. Re: ROWNUM and execution plan
    Christophe Lize Newbie
    Currently Being Moderated
    Hi David,

    You're rigth, there were missing quote as it's a string but it does not change the plan except for the cost which is a little bit higher, due to the conversion, I think.

    I do not understand why the logique is that different between 2 and more than 2. Because when I set rownum<3 to 6 the same cost and cardinality are calculated, starting from 7 then the cardinality starts to change on the last hash join with TEST3.

    What is clear is that the logic/strategy is clearly different between 1 line returned and 2 or more... But I would like to have an explanation because the logic is not good as it never returns.

    I think I will open a SR.

    Thanks

    Christophe
  • 5. Re: ROWNUM and execution plan
    Martin Preiss Expert
    Currently Being Moderated
    Hi,

    with the rownum < 2 predicate the CBO assumes that the operation can be finished after a few rows. The order is:
    1. read 957 rows from TEST2 and build a hash map in memory
    2. start to access TEST3 and give the first matching result to the NL Join with TEST1

    If you are lucky and your first match for the join of TEST2 und TEST3 can indeed be matched to TEST1 then the NL for the second join is a good idea. But it seems that in your case a lot of matches from the first join don't find a match in TEST1 and so the NL goes on and on and on.

    with the rownum < 3 predicate the CBO thinks that the hash join will be cheaper than a NL join - and that's the better decision in your case. You could use hints or sql baselines to enforce the use of the hash join for all steps, but basically it's not a bad idea of the CBO to use NL joins queries with rownum predicates because they can provide the first results - in theory - faster than a hash join.

    Edited by: Martin Preiss on May 2, 2013 5:15 PM
  • 6. Re: ROWNUM and execution plan
    rp0428 Guru
    Currently Being Moderated
    >
    Yes, I have a problem with rownum<2, the query never return... ;o)
    >
    So? Your workaround is to use 'rownum<3', which you know works and just use the first row.

    You could just wrap your 'rownum<3' query in an outer query that uses 'rownum=1'.
  • 7. Re: ROWNUM and execution plan
    Christophe Lize Newbie
    Currently Being Moderated
    Hi rp0428,

    Thanks for the answer I did it already that way but that's a patch... ;o(

    I performed some tests playing with the stats with and without histograms but it's sometime worse with histograms... I want to build a case to see with Oracle support if this is the attended behavior or if there is something bad in that...

    Thanks all for your answers!

    Christophe

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points