Как настроить диапазон/интервальный запрос в Oracle?

У меня есть таблица A с интервалами (COL1, COL2):

Интервалы гарантируются как "исключительные", т.е. они никогда не будут перекрываться. Другими словами, этот запрос не дает строк:

В настоящее время существует индекс на (COL1, COL2). Теперь мой запрос следующий:

Это выполняется хорошо (менее миллисекунд для миллионов записей в A) для низких значений :some_value, потому что они очень избирательны по индексу. Но он работает довольно плохо (почти секунда) для больших значений :some_value из-за более низкой селективности предиката доступа.

План исполнения кажется мне хорошим. Поскольку существующий индекс уже полностью охватывает предикат, я получаю ожидаемый INDEX RANGE SCAN:

В 3 становится очевидным, что предикат доступа является выборочным только для низких значений :some_value, тогда как для более высоких значений операция фильтра "пинает" по индексу.

Есть ли способ улучшить этот запрос, чтобы быть быстрым, независимо от значения :some_value? Я могу полностью переделать таблицу, если необходима дальнейшая нормализация.

Ответы

Ответ 1

Ваша попытка хорошая, но не хватает нескольких важных вопросов.

Пусть начнется медленно. Я принимаю индекс на COL1, и я действительно не против, если там также включен COL2.

Из-за ограничений, которые у вас есть на ваших данных (особенно на неперекрывающихся), вам просто нужна строка перед строкой, где COL1 есть <= некоторое значение.... [- сделать перерыв--] его вы заказываете COL1

Это классический запрос Top-N:

select *
  FROM ( select *
          from A
         where col1 <= :some_value
         order by col1 desc
       )
 where rownum <= 1;

Обратите внимание, что должен использовать ORDER BY для получения определенного порядка сортировки. Поскольку WHERE применяется после ORDER BY, вы также должны обернуть фильтр top-n во внешнем запросе.

Это почти сделано, единственная причина, по которой нам действительно нужно фильтровать на COL2 тоже, - это отфильтровать записи, которые вообще не попадают в диапазон. Например. если some_value равно 5, и у вас есть эти данные:

  COL1 | COL2
     1 |  2
     3 |  4   <-- you get this row 
     6 | 10

Эта строка будет верна как результат, если COL2 будет 5, но, к сожалению, в этом случае правильным результатом вашего запроса будет [пустой набор]. Это единственная причина, по которой нам нужно фильтровать для COL2 следующим образом:

select *
  FROM ( select *
           FROM ( select *
                    from A
                   where col1 <= :some_value
                   order by col1 desc
                )
          where rownum <= 1
        )
  WHERE col2 >= :some_value;

У вашего подхода было несколько проблем:

отсутствует ORDER BY - опасно в связи с фильтром rownum!
применение предложения Top-N (rownum filter) слишком рано. Что делать, если результат нет? База данных считывает индекс до конца, rownum (STOPKEY) никогда не срабатывает.
Сбой оптимизатора. С предикатом between моя установка 11g не приходит к идее читать индекс в порядке убывания, поэтому он фактически читает его с начала (0) вверх, пока не найдет значение COL2, которое соответствует - ИЛИ - COL1 заканчивается.

COL1 | COL2
   1 |  2   ^
   3 |  4   |      (2) go up until first match.
            +----- your intention was to start here
   6 | 10

Что на самом деле происходило:

  COL1 | COL2
     1 |  2   +----- start at the beginning of the index
     3 |  4   |      Go down until first match.      
              V
     6 | 10

Посмотрите на план выполнения моего запроса:

------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |     1 |    26 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                           |        |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |        |       |       |            |          |
|   3 |    VIEW                         |        |     2 |    52 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | A      | 50000 |   585K|     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| SIMPLE |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Обратите внимание на INDEX RANGE SCAN **DESCENDING**.

Наконец, почему я не включил COL2 в индекс? Это один запрос строки-сверху-n. Вы можете сэкономить не более одного доступа к таблице (независимо от того, что выше приведено выше.) Если вы ожидаете найти строку в большинстве случаев, вам нужно будет пойти в таблицу в любом случае для других столбцов (возможно), чтобы вы ничего не спасет, просто используйте пространство. Включение COL2 улучшит производительность только в том случае, если запрос не возвращает ничего!

по теме:

Как использовать функцию индекса в запросе mysql Я ответил на очень похожий вопрос об этом много лет назад. То же решение.
Используйте Индекс, Лукас!

Ответ 2

Я думаю, потому что диапазоны не пересекаются, вы можете определить col1 как первичный ключ и выполнить запрос следующим образом:

SELECT *
  FROM    a
       JOIN
          (SELECT MAX (col1) AS col1
             FROM a
            WHERE col1 <= :somevalue) b
       ON a.col1 = b.col1;

Если между диапазонами, которые вы должны добавить, есть пробелы:

Where col2 >= :somevalue

в качестве последней строки.

План выполнения:

SELECT STATEMENT  
 NESTED LOOPS  
  VIEW  
   SORT AGGREGATE 
    FIRST ROW  
     INDEX RANGE SCAN (MIN/MAX) PKU1
  TABLE ACCESS BY INDEX A
   INDEX UNIQUE SCAN PKU1

Ответ 3

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

Ответ 4

Я не генерировал выборочные данные, чтобы проверить это, но вы можете попробовать попробовать.

ALTER TABLE A ADD COL3 NUMBER(15);

UPDATE A SET COL3 = COL2 - COL1;

Создайте индекс на COL3.

SELECT /*+FIRST_ROWS(1)*/ *
FROM A
WHERE :some_value < COL3
AND ROWNUM = 1;