Подсказка FIRST_ROWS(n) не дает оптимизированный результат для предложения Order by

Question

У нас около 8 миллионов записей в таблице, содержащей около 50 столбцов, нам нужно увидеть несколько записей очень быстро, поэтому мы используем подсказку FIRST_ROWS(10) для этой цели, и она работает удивительно быстро.

SELECT /*+ FIRST_ROWS(10) */ ABC.view_ABC.ID, ABC.view_ABC.VERSION, ABC.view_ABC.M_UUID, ABC.view_ABC.M_PROCESS_NAME FROM ABC.view_ABC

---

Однако, когда мы помещаем предложение ORDER BY, например, время создания (которое является почти уникальным значением для каждой строки в этой таблице), этот запрос займет много времени, чтобы вернуть все столбцы.

SELECT /*+ FIRST_ROWS(10) */ ABC.view_ABC.ID, ABC.view_ABC.VERSION, ABC.view_ABC.M_UUID, ABC.view_ABC.M_PROCESS_NAME FROM ABC.view_ABC ORDER BY ABC.view_ABC.CREATIONTIME DESC

Одна вещь, которую я заметил, это; если мы поместим ORDER BY для некоторого столбца, такого как VERSION, который имеет одинаковое значение для нескольких строк, это даст результат лучше.

это ORDER BY не работает эффективно для любого уникального столбца, как для ID столбец в этой таблице.

Стоит рассмотреть еще одну вещь; если мы уменьшим количество столбцов, которые должны быть извлечены, например, 3 столбца вместо 50 столбцов, результаты будут как-то быстрее.

Статистика сбора PS выполняется по этой таблице еженедельно, но данные отправляются ежечасно. Только INSERT оператор работает на этой таблице, нет DELETE или же UPDATE запросы выполняются на этой таблице.

Кроме того, здесь нет простого представления, созданного без этой таблицы, вышеупомянутые запросы выполняются в том же представлении.

Answer 1

В этом столбце есть многостолбцовый индекс (CREATION_TIME), каким-то образом оптимизатор oracle hint не использовал этот индекс.

Однако на той же таблице был еще один столбец (TERMINATION_TIME), у него был индекс на себя. Таким образом, мы используем тот же запрос, но с этим индексированным столбцом в ORDER BY пункт.

Ниже приведен план объяснения первого запроса с CREATION_TIME в предложении ORDER BY, который является частью индекса из нескольких столбцов.

-------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                                |  7406K|   473M|       |   308K  (1)| 01:01:40 |
|   1 |  SORT ORDER BY     |                                |  7406K|   473M|   567M|   308K  (1)| 01:01:40 |
|   2 |   TABLE ACCESS FULL| Table_ABC                      |  7406K|   473M|       |   189K  (1)| 00:37:57 |
-------------------------------------------------------------------------------------------------------------

И это с TERMINATION_TIME в качестве предложения ORDER BY.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                                |    10 |   670 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_ABC                      |  7406K|   473M|    10   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| XGN620150305000000             |    10 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Если вы видите, это явная разница в стоимости, задействованных строках, использовании временного пространства (которое даже не используется в более позднем случае) и, наконец, времени.

Теперь время ответа на запрос намного лучше.

Благодарю.

Answer 2

Без order by предложение оптимизатор может выполнить любые операции соединения, которые скрывает ваше представление, и начать возвращать данные, как только они будут получены. Подсказка меняет способ доступа к базовым таблицам, так что он, например, выполняет соединение с вложенным циклом вместо объединения слиянием, что позволит ему быстро найти первые подходящие строки; но может быть менее эффективным в целом для возврата всех данных. Ваш совет подсказывает оптимизатору, что вы хотите, чтобы он установил приоритет скорости первой партии строк, возвращаемых над скоростью всего запроса.

Когда вы добавляете order by После этого все данные должны быть найдены, прежде чем они могут быть заказаны. Все условия соединения должны быть выполнены, все вложенные циклы / слияния и т. Д. Выполнены, а затем весь набор результатов должен быть отсортирован в указанном вами порядке, прежде чем будут возвращены какие-либо строки.

Если столбец, по которому вы упорядочиваете, проиндексирован, и этот индекс используется (или может использоваться) оптимизатором для идентификации строк в таблице управления, то, возможно, он может включать это в сортировку, но вы не можете полагаться на это как оптимизатор может менять план по мере изменения данных и статистики.

Возможно, вам будет полезно посмотреть планы выполнения ваших различных запросов, с подсказкой и без подсказки, чтобы увидеть, что оптимизатор делает в каждом случае, в том числе, где в цепочке шагов он выполняет операцию сортировки, и типы из соединений он делает.