Hi Tom,
I'm trying to retrieve the last 20 rows by time for users with more than 100K rows in a table with more than 1 million records. The query performance is fine (in ms) when the user has a small number of records. But takes more than 2 minutes from the application to fetch 20 records for users with about 10K-100K records.
Here's the query:
select * from ( select * from TABLE1
where USER_ID= 41063660 and COL1=0 and COL2 = 0
order by LAST_EVENT_DATE desc)
where rownum <= 20 ;
There is an index (I_LASTEVENTDT) on (USER_ID, COL1, COL2, LAST_EVENT_DATE DESC)
Here is the explain plan:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 38960 | | 66959 (1)| 00:13:24 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 65500 | 121M| | 66959 (1)| 00:13:24 |
|* 3 | SORT ORDER BY STOPKEY | | 65500 | 96M| 102M| 66959 (1)| 00:13:24 |
| 4 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 65500 | 96M| | 47280 (1)| 00:09:28 |
|* 5 | INDEX RANGE SCAN | I_LASTEVENTDT | 65500 | | | 309 (0)| 00:00:04 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
5 - access("USER_ID"=41063660 AND "COL1"=0 AND
"COL2"=0)
I have tried creating a separate index on (USER_ID, COL1, COL2) and (LAST_EVENT_DT DESC) too and also tried index on (USER_ID,LAST_EVENT_DT DESC) . The performance was worse for both.
In one of your reference article
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html which shows a similar query, I see that the sort order by stop key filters down the results to 10, but in my query plan, the sort is still done on 65K rows.
I'm not sure why.
How to I get better performance from this query?
Thanks in advance.
Sorry - you'll need to send me a test case. I couldn't reproduce what you are seeing - I'm getting the expected index scan of just the appropriate 20 rows
SQL> alter session set optimizer_features_enable = '11.2.0.4';
Session altered.
SQL> create table t
2 ( user_id int,
3 col1 int,
4 col2 int,
5 last_event_date date );
Table created.
SQL>
SQL> insert /*+ APPEND */ into t
2 select mod(rownum,1000),
3 mod(rownum,1000), mod(rownum,1000),
4 date '2010-01-01' + rownum * 2000 / 100000000
5 from
6 ( select 1 from dual connect by level <= 10000 ),
7 ( select 1 from dual connect by level <= 10000 );
100000000 rows created.
SQL>
SQL> create index IX on t ( user_id, col1, col2, last_event_date desc ) ;
Index created.
SQL> select /*+ gather_plan_statistics */ *
2 from (
3 select * from t
4 where USER_ID= 416 and COL1=416 and COL2 = 416
5 order by LAST_EVENT_DATE desc)
6 where rownum <= 20 ;
USER_ID COL1 COL2 LAST_EVEN
---------- ---------- ---------- ---------
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
416 416 416 23-JUN-15
20 rows selected.
SQL> @exp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID b532d7wawh362, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ( select * from t where
USER_ID= 416 and COL1=416 and COL2 = 416 order by LAST_EVENT_DATE
desc) where rownum <= 20
Plan hash value: 3186571263
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 7 |
|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 7 |
| 2 | VIEW | | 1 | 65259 | 20 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN| IX | 1 | 65259 | 20 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - access("USER_ID"=416 AND "COL1"=416 AND "COL2"=416)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SQL>
SQL>