Оптимизация соединения запросов Oracle для диапазона значений (объединение по n между x и y)

Это неприятная проблема с ключами диапазона (даты начала/окончания, значения от/до и т. д.). Хотя Oracle может использовать сканирование индекса для исключения одной стороны (больше или меньше) вашего диапазона, это оставляет другую сторону открытой, и в этом проблема.

В этой ситуации я использую два подхода в зависимости от объема данных.

1. Для небольших таблиц (1 млн строк на самом деле довольно мало) или для тех случаев, когда вы собираетесь выполнять лишь несколько поисков определенных значений, просто создайте объединенный индекс (из двух столбцов) на (from_value,to_value) и убедитесь, что ваши запросы выполняют сканирование диапазона. на нем (подсказка, если это не так). Это позволит избежать попадания в таблицу любой строки, выходящей за пределы диапазона. Тем не менее, по-прежнему требуется сканирование одного блока связанного списка конечных узлов индекса для всех старых диапазонов, которые предшествуют желаемой дате - это потому, что двоичный поиск, выполняемый в первом столбце, будет удовлетворяться самым старым значением и продолжать удовлетворяться. до тех пор, пока не превысит целевое значение. Таким образом, вы в конечном итоге выполняете множество операций по получению индексных блоков.

2. Для больших таблиц (сотни миллионов или миллиарды строк) и когда вы собираетесь обрабатывать большую часть значений (например, в процессе ETL, материализованном представлении или какой-либо повседневной работе), вам может понадобиться другая стратегия. Что для меня хорошо работает, так это группировать таблицу диапазонов на основе размера диапазона, чтобы вы могли использовать предикат равенства в своих соединениях для большинства строк и минимизировать количество строк, в которых необходимо использовать неравенство. Есть несколько способов сделать это. Вот пример из системы, которая историзирует данные с использованием идентификаторов начала/конца (которые в других местах привязаны к временным меткам). Мне часто нужно получить строку, которая была активна на момент определенного идентификатора снимка. Когда объем действительно огромен, требуется некоторый творческий подход:

      SELECT /*+ NO_MERGE USE_HASH(dt rt) */
             gd.driving_start_snapshot_id,
             rt.*
        FROM drivingtable dt,
             rangetable rt
       WHERE FLOOR(rt.composite_start_snapshot_id / 1000000) = FLOOR(rt.composite_end_snapshot_id / 1000000)
         AND FLOOR(dt.driving_start_snapshot_id / 1000000) = FLOOR(rt.composite_start_snapshot_id / 1000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id
      UNION ALL
      SELECT /*+ NO_MERGE USE_HASH(dt rt) */
             dt.driving_start_snapshot_id,
             rt.*
        FROM drivingtable dt,
             rangetable rt
       WHERE FLOOR(rt.composite_start_snapshot_id / 1000000) != FLOOR(rt.composite_end_snapshot_id / 1000000)
         AND FLOOR(rt.composite_start_snapshot_id / 10000000) = FLOOR(rt.composite_end_snapshot_id / 10000000)
         AND FLOOR(dt.driving_start_snapshot_id / 10000000) = FLOOR(rt.composite_start_snapshot_id / 10000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id
      UNION ALL
      SELECT /*+ NO_MERGE USE_HASH(dt rt) */
             dt.driving_start_snapshot_id,
             rt.*
        FROM drivingtable dt,
             rangetable rt
       WHERE FLOOR(rt.composite_start_snapshot_id / 10000000) != FLOOR(rt.composite_end_snapshot_id / 10000000)
         AND FLOOR(rt.composite_start_snapshot_id / 20000000) = FLOOR(rt.composite_end_snapshot_id / 20000000)
         AND FLOOR(dt.driving_start_snapshot_id / 20000000) = FLOOR(rt.composite_start_snapshot_id / 20000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id
      UNION ALL
      SELECT /*+ NO_MERGE USE_HASH(dt rt) */
             dt.driving_start_snapshot_id,
             rt.*
        FROM drivingtable dt,
             rangetable rt
       WHERE FLOOR(rt.composite_start_snapshot_id / 20000000) != FLOOR(rt.composite_end_snapshot_id / 20000000)
         AND FLOOR(rt.composite_start_snapshot_id / 100000000) = FLOOR(rt.composite_end_snapshot_id / 100000000)
         AND FLOOR(dt.driving_start_snapshot_id / 100000000) = FLOOR(rt.composite_start_snapshot_id / 100000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id
      UNION ALL
      SELECT /*+ NO_MERGE USE_MERGE(dt rt) */
             dt.driving_start_snapshot_id,
             rt.*
        FROM drivingtable dt,
             rangetable rt
       WHERE FLOOR(rt.composite_start_snapshot_id / 100000000) != FLOOR(rt.composite_end_snapshot_id / 100000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id
   

В этом примере первый блок говорит, что если диапазон находится в пределах одного и того же сегмента в 1 миллион (ПЛОЩАДЬ значения / 1 миллион):

FLOOR(rt.composite_start_snapshot_id / 1000000) = FLOOR(rt.composite_end_snapshot_id / 1000000)

затем выполните эквисоединение для этого значения сегмента:

FLOOR(dt.driving_start_snapshot_id / 1000000) = FLOOR(rt.composite_start_snapshot_id / 1000000)

Внутри этого эквивалентного соединения уточните результаты с помощью фактического оператора > < или between:

AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id

Но эквисоединение выполняет большую часть работы и именно здесь достигается прирост производительности.

Для строк, охватывающих несколько блоков по 1 миллиону (!=), попробуйте использовать более широкий блок — например, 10 миллионов. Если начало/конец находится в одном и том же 10-миллионном блоке, выполните равное соединение по значению этого блока:

          FLOOR(rt.composite_start_snapshot_id / 1000000) != FLOOR(rt.composite_end_snapshot_id / 1000000)
      AND FLOOR(rt.composite_start_snapshot_id / 10000000) = FLOOR(rt.composite_end_snapshot_id / 10000000)
      AND FLOOR(dt.driving_start_snapshot_id / 10000000) = FLOOR(rt.composite_start_snapshot_id / 10000000)

Для строк, охватывающих несколько блоков по 10 миллионов, снова расширьте их... попробуйте 20 миллионов, затем 100 миллионов и т. д. Просто убедитесь, что размер каждого сегмента кратен предыдущему, чтобы избежать обработки одной и той же строки в большем количестве блоков. чем один блок. В противном случае вам придется включить весь список сегментов !=, которые предшествуют каждому блоку, чтобы гарантировать отсутствие дублирования строк.

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

             FLOOR(rt.composite_start_snapshot_id / 100000000) != FLOOR(rt.composite_end_snapshot_id / 100000000)
         AND dt.driving_start_snapshot_id >= rt.composite_start_snapshot_id
         AND dt.driving_start_snapshot_id < rt.composite_end_snapshot_id

Это позволит выполнить соединение по неравенству без какого-либо соединения по равенству, но, надеюсь, к тому времени вы будете иметь дело с очень небольшим количеством строк, поэтому влияние на производительность будет смягчено.

Этот подход можно записать в SQL различными способами, причем вышеописанный является только одним из способов. Но общая концепция заключается в том, чтобы разделять и властвовать с помощью отдельных шагов, которые находят способ использовать предикат равенства для как можно большего числа строк. Сколько дискретных шагов и размер корзины вы выберете, зависит от ваших данных и от того, где находится точка убывающей отдачи для выполнения нескольких проходов. Но во многих случаях я обнаруживал, что 3–5 отдельных шагов действительно могут значительно повысить производительность при объединении диапазонов по сравнению с выполнением их всех одновременно.