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