Oracle 11g - верхний N порядок медленно

Question

Я пытаюсь извлечь последние 20 строк по времени для пользователей, у которых в таблице более 100 000 строк и более 1 миллиона записей. Производительность запроса хорошая (в мс), когда у пользователя небольшое количество записей. Но для получения 20 записей для пользователей с записями от 10 до 100 тыс. Требуется более 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 ;

Есть индекс (I_LASTEVENTDT) для (USER_ID, COL1, COL2, LAST_EVENT_DATE DESC)

Вот план объяснения:

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

Сначала попробуйте что-то вроде:

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 в стиле, подобном моему второму примеру, но без подзапроса.

Answer 2

Я думаю, что вы должны искать индекс быстрого полного сканирования и не заказывать всю строку. Кроме того, я пытаюсь получить небольшое количество записей (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;