I'm not sure if I'm completely missing something in the process below in using ROWNUM to perform a top-N query. I have created indexes that I would have expected to avoid a SORT from being performed, but I can't seem to get the query to avoid the "SORT ORDER BY STOPKEY".
The database is 11gR2 (11.2.0.2) running on Oracle Linux 5.7.
create table projrecord (
recid number,
loaddate date,
comments varchar2(20));
create table customer (
recid number,
loaddate date,
custval varchar2(20));
insert into projrecord values (1, to_date('03-MAY-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'TEST');
insert into projrecord values (2, to_date('10-JUN-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'TEST');
insert into projrecord values (3, to_date('20-AUG-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'TEST');
insert into projrecord values (4, to_date('25-SEP-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'TEST');
insert into projrecord values (5, to_date('12-OCT-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'TEST');
insert into customer values (1, to_date('03-MAY-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'customer1');
insert into customer values (1, to_date('03-MAY-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'customer1');
insert into customer values (1, to_date('03-MAY-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'customer2');
insert into customer values (3, to_date('20-AUG-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'customer3');
insert into customer values (4, to_date('25-SEP-12 02:10:00', 'DD-MON-YY HH24:MI:SS'), 'customer4');
create unique index idx_projrecord_recid on projrecord (recid);
alter table projrecord add constraint projrecord_recid_pk primary key (recid)
using index idx_projrecord_recid;
alter table customer add constraint customer_recid_fk foreign key (recid)
references projrecord;
create unique index idx_projrecord_reciddate on projrecord (recid, loaddate);
create index idx_cust_lcustvalreciddate on customer (lower(custval), recid, loaddate);
exec dbms_stats.gather_table_stats(user, 'projrecord');
exec dbms_stats.gather_table_stats(user, 'customer');
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> sho parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.2
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> select /*+ gather_plan_statistics */ *
2 from (
3 select pr.recid, pr.loaddate, pr.comments
4 from projrecord pr, customer c
5 where pr.recid = c.recid
6 and pr.loaddate = c.loaddate
7 and lower(c.custval) = 'customer1'
8 order by pr.loaddate desc)
9 where rownum < 2;
RECID LOADDATE COMMENTS
---------- --------- --------------------
1 03-MAY-12 TEST
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID 4rnatds0f7wr0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select pr.recid,
pr.loaddate, pr.comments from projrecord pr, customer c where
pr.recid = c.recid and pr.loaddate = c.loaddate and lower(c.custval)
= 'customer1' order by pr.loaddate desc) where rownum < 2
Plan hash value: 706850593
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers| OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5| | | |
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 5| | | |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 5| | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1 | 1 |00:00:00.01 | 5| 2048 | 2048 | 2048 (0)|
| 4 | NESTED LOOPS | | 1 | | 2 |00:00:00.01 | 5| | | |
| 5 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 3| | | |
|* 6 | INDEX RANGE SCAN | IDX_CUST_LCUSTVALRECIDDATE | 1 | 1 | 2 |00:00:00.01 | 1| | | |
|* 7 | INDEX UNIQUE SCAN | IDX_PROJRECORD_RECIDDATE | 2 | 1 | 2 |00:00:00.01 | 2| | | |
| 8 | TABLE ACCESS BY INDEX ROWID| PROJRECORD | 2 | 1 | 2 |00:00:00.01 | 2| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - filter(ROWNUM<2)
6 - access("C"."SYS_NC00004$"='customer1')
7 - access("PR"."RECID"="C"."RECID" AND "PR"."LOADDATE"="C"."LOADDATE")
31 rows selected.
I saw a note on Metalink (833286.1) that addressed slow ROWNUM queries, however the parameter "_optimizer_rownum_pred_based_fkr=FALSE" it suggested did not change the query plan.
I have also tried to use ROW_NUMBER, but I still get a SORT with "WINDOW SORT PUSHED RANK".
Can anyone explain why I can't seem to avoid the SORT?
Thanks!