甲骨文11g-排名前N的订单速度较慢

Question

我正在尝试为一个记录超过100万条的表中有超过10万行的用户按时间检索最后20行。当用户有少量记录时，查询性能良好（毫秒）。但是，对于拥有大约10K-100K条记录的用户，获取20条记录需要2分钟以上的时间。

以下是查询：

select * from ( select * from TABLE1                                                                                                              
         where USER_ID= 41063660  and                                                                                                                        
         COL1=0 and COL2 = 0
  order by LAST_EVENT_DATE desc) where rownum <= 20 ;

在（用户ID、COL1、COL2、上次事件日期描述）上有一个索引（I_LASTEVENTDT）

以下是解释计划：

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

我试图效仿http://use-the-index-luke.com/sql/sorting-grouping/indexed-order-by

我也尝试过在（USER_ID，COL1，COL2）和（LAST_EVENT_DT DESC）上创建单独的索引，还尝试了在（USER_ID，LAST_EVENT_DT DESC）上创建索引。尽管后一个索引取消了排序顺序，但两者的性能都较差。

如何从这个查询中获得更好的性能？

提前谢谢。

Answer 1

我认为您应该寻找索引快速全扫描，而不是整行排序。此外，我还试图实现少量记录（20条），并将它们再次连接到主表。

with Dates as (
    select /*+ Materialize */ LAST_EVENT_DATE 
       from ( select LAST_EVENT_DATE 
                      from TABLE1                                                                                                              
                     where USER_ID= 41063660  
                        and COL1=0 
                        and COL2 = 0
                     order by LAST_EVENT_DATE desc) 
       where rownum <= 20 )
select t2.* 
  from (
    select t1.*
      from TABLE1 t1 join Dates on t1.LAST_EVENT_DATE >= Dates.LAST_EVENT_DATE 
                               and t1.USER_ID= 41063660  
                               and t1.COL1=0 
                               and t1.COL2 = 0
    order by t1.LAST_EVENT_DATE desc) as t2
where rownum <= 20;

Answer 2

首先，尝试以下方法：

SELECT *
FROM 
   (SELECT *, ROW_NUMBER() OVER (ORDER BY last_event_date desc) R
    FROM table1
    WHERE user_id = 41063660
    AND col1 = 0
    AND col2 = 0)
WHERE R <= 20;

如果这证明不是很快，请尝试：

SELECT *
FROM table1,
    ( SELECT last_event_date, ROW_NUMBER() OVER (ORDER BY last_event_date desc) R
    FROM table1
    WHERE user_id = 41063660
    AND col1 = 0
    AND col2 = 0 ) sub
WHERE table1.user_id = 41063660
AND table1.col1 = 0
AND table1.col2 = 0
AND sub.R = 20
AND table1.last_event_date >= sub.last_event_date
AND ROWNUM <= 20;

也许有一种更直接的方法来编写它——我没有一个Oracle实例可供我试用。

另一种选择是实现一个SQL函数，仅在给定某些键的情况下获取第20行（或第N行）的日期。然后以类似于我的第二个示例的样式调用SQL函数，但不包含子查询。