Упорядочить по двум столбцам без индекса в Oracle XE 11g
Почему этот простой запрос для получения первых 100 строк, начиная с заданного времени, упорядочен по времени и первичному ключу (client_time не уникален, поэтому упорядочивает по обоим), не использует индекс?
SELECT *
FROM (SELECT *
FROM requests
WHERE client_time >= TO_TIMESTAMP('2017-07-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY client_time ASC, transaction_id ASC
)
WHERE rownum <= 100;
client_time is TIMESTAMP WITH LOCAL TIME ZONE идентификатор транзакции VARCHAR2(255 CHAR),
Индекс, который я ожидаю использовать, определяется как
CREATE UNIQUE INDEX idx_time_id REQUESTS (client_time, transaction_id);
Выполнение запроса занимает около 2 секунд (6 миллионов строк в моей системе, будет гораздо больше в производстве) и дает следующий план:
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 110K| | 31237 (1)| 00:06:15 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 860K| 931M| | 31237 (1)| 00:06:15 |
|* 3 | SORT ORDER BY STOPKEY| | 860K| 65M| 86M| 31237 (1)| 00:06:15 |
|* 4 | TABLE ACCESS FULL | REQUESTS | 860K| 65M| | 15294 (1)| 00:03:04 |
----------------------------------------------------------------------------------------------------
Информация предиката (идентифицируется по идентификатору операции):
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter("CLIENT_TIME">=TIMESTAMP' 2017-07-01 10:00:00,000000000')
Когда я удаляю вторую часть моего предложения ORDER BY, этот индекс фактически используется, и запрос выполняется за 1 мс.
Если я понял это, воспользуйтесь индексом, правильно , Люк, не должен ли мой запрос тоже использовать этот индекс?
ОБНОВИТЬ:
План после удаления моего второго столбца заказа выглядит так:
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 65100 | 106 (0)| 00:00:02 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 102 | 66402 | 106 (0)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TRX_REQUESTS_LTZ | 102 | 8160 | 106 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IDX_TIME_ID | | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Так что я считаю, что предложение WHERE здесь не является проблемой. Кроме того, ничего не меняется после переписывания WHERE, например:
WHERE client_time >= TO_TIMESTAMP_TZ('2017-07-01 10:00:00 +10:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
2 ответа
Проблема в том, что мой индекс построен на VARCHAR2 колонка. Из-за NLS порядок индексов не может использоваться просто так для сортировки набора результатов.
Изменение идентификатора транзакции на NUMBER решил проблему.
Я подозреваю, что причина в WHERE пункт:
WHERE client_time >= TO_TIMESTAMP('2017-07-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
Вы указали, что client_time это TIMESTAMP WITH TIMEZONE, Тем не менее, константа просто TIMESTAMPбез часового пояса. Это означает, что типы должны быть преобразованы - и это часто мешает использованию индекса.
Вы должны попробовать использовать TO_TIMESTAMP_TZ(), документировано здесь.