Оптимизация производительности с EXISTS

Question

Я работаю над сценарием поставки DWH, который имеет дело с ~40 миллионами строк на сервере Oracle Exadata.

У меня есть следующие таблицы:

CREATE TABLE P.ARCHIVED_ID
(
    ID_ARCHIVED  VARCHAR2(31 BYTE) NOT NULL, -- CONSTRAINT UNIQUE / PRIMARY KEY
    IS_DELETE    CHAR(1 BYTE)      DEFAULT null
);

CREATE TABLE P.DWH_tableX
(
    ID      VARCHAR2(31 BYTE) NOT NULL, -- CONSTRAINT UNIQUE / PRIMARY KEY
    FIELD1  VARCHAR2(31 BYTE),
    FIELD2  VARCHAR2(31 BYTE),
    FIELD3  CHAR(2 BYTE),
    FIELD4  CHAR(1 BYTE)
);

Как видите, у меня есть IS_DELETE флаг, который можно установить на Y (и по умолчанию NULL).

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

SELECT 
    ID,
    FIELD1,
    FIELD2,
    FIELD3,
    FIELD4
FROM P.DWH_tableX A
WHERE EXISTS (
    SELECT 1 
    FROM P.ARCHIVED_ID B 
    WHERE 
        A.ID = B.ID_ARCHIVED 
        AND IS_DELETE = 'Y'
    );

Вопрос, есть ли лучший способ оптимизировать это, чем следующий индекс?

CREATE BITMAP INDEX P.I_IS_DELETE 
    ON P.ARCHIVED_ID(IS_DELETE)
LOGGING
TABLESPACE TBS_P_01
NOPARALLEL;

---

Еще несколько моментов:

• Я никогда не получу доступ P.ARCHIVED_ID без WHERE IS_DELETE = 'Y' (или только один раз, когда я обновляю это поле в начале скрипта)
• по умолчанию Oracle не регистрируется NULL в индексе, поэтому я использую NULL вместо "N", позволяя мне сделать частичный ="Y" индекс растрового изображения, сохраняя его настолько маленьким и полезным, насколько я могу. ЛОЖНЫЙ
• я использую EXISTS, вместо IN, как рекомендует Oracle (возможность использования индекса),
• Часть с IS_DELETE = 'Y' на данный момент 0%, но, поскольку это DWH, ожидается, что он будет только увеличиваться (поскольку новые строки создаются каждый день в источнике данных, а старые удаляются физически, устанавливая их в IS_DELETE = 'Y' в ДВХ)
• WHERE EXISTS используется около 20 раз в моем сценарии, так как это то, как мы имеем дело с заморозкой архивных значений (переместить их и вернуться во временную таблицу, с insert append намек)

Answer 1

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

"Есть ли лучший способ оптимизировать это, чем следующий индекс"

Почти наверняка да.

Преимущество растровых индексов заключается в наличии нескольких из них. Таким образом, когда мы запускаем фильтрацию запросов по этим столбцам, оптимизатор может выбрать выполнение преобразования "звезда", чтобы найти строки в пересечении растровых изображений. Даже в этом случае растровые индексы в двухвалентных столбцах так же полезны, как столбцы с несколькими различными значениями.

Один растровый индекс сам по себе, в частности, только с двумя значениями, не очень полезен. Учитывая чудовищные накладные расходы на поддержание растровых индексов и их проблемы параллелизма, вам, вероятно, следует рассмотреть другие варианты.

"~ 40 миллионов строк на сервере Oracle Exadata"

Oracle разработала устройство Exadata для обработки больших объемов данных. Это означает поиск путей, которые поддерживают хеш-соединения, фильтры Блума и подобные операции. В Exadata распространенной техникой настройки является удаление существующего индекса, а не создание нового. Несмотря на то, что индексы B-Tree дешевле, чем растровые изображения, они по-прежнему требуют затрат ресурсов (ЦП, СХД, памяти), поэтому стоит подумать о том, предлагает ли использование грубой силы Exadata более низкую стоимость в целом. Это то, за что мы платим большие деньги.

Тем не менее, даже грубая сила Exadata является ограниченным ресурсом. Поэтому, если вы собираетесь многократно выполнять этот запрос (или, скорее, подзапрос EXISTS), вы, скорее всего, получите выгоду от кластеризации исключенных строк. По твоему вопросу похоже IS_DELETE это обновленный атрибут, который нельзя использовать для физической организации на уровне таблицы (CTAS, кластеризация атрибутов). Таким образом, индекс B-дерева на ARCHIVED_ID(IS_DELETE, ID_ARCHIVED) является основным кандидатом.

С составными индексами обычно лучше начинать с наименее селективного столбца, и это верно здесь. Вас интересуют только те строки, где IS_DELETE = 'Y' Таким образом, ведение с этим столбцом уменьшит количество блоков, которые необходимо посетить подзапросу. Ведущий с ID_ARCHIVED будет означать, что подзапрос должен сканировать весь индекс. Даже с Exadata мы всегда должны стремиться минимизировать работу, выполняемую для получения набора записей.

Но, пожалуйста, отметьте это или любой другой индекс.

---

"Что касается индекса растрового изображения, я думал, что он отслеживает только значение Not Null (поэтому Y), я ошибаюсь?"

'Беда так. У растровых индексов есть запись для каждого индексированного поля. Таким образом, в отличие от индексов B-дерева с одним столбцом, они индексируют null записей.

"Я использую EXISTS вместо IN, как рекомендует Oracle (возможность использования индекса)"

Хммм, не уверен, какую рекомендацию Oracle вы там приводите. Что лучше, действительно зависит от данных. Но в основном, если подзапрос огромен, а основной запрос относительно мал, тогда EXISTS более эффективен. Но если объемы данных меняются местами, основной запрос является огромным, а подзапрос - маленьким, IN - лучший выбор. Учитывая, что ваша текущая начальная позиция такова, что подзапрос не возвращает строк (потому что ничего не удалено), и, по-видимому, в ближайшем будущем будет удалено относительно немного записей, кажется более вероятным, что IN - это та конструкция, которую вы должны использовать.

Но опять же: сравните каждый подход и посмотрите, что лучше работает с вашими данными.

Answer 2

Для этого запроса:

SELECT ID, FIELD1, FIELD2, FIELD3, FIELD4
FROM P.DWH_tableX A
WHERE EXISTS (
    SELECT 1 
    FROM P.ARCHIVED_ID B 
    WHERE 
        A.ID = B.ID_ARCHIVED AND
        B.IS_DELETE = 'Y'
    );

Моя первая мысль об индексе будет ARCHIVED_ID(ID_ARCHIVED, IS_DELETE), Было бы интересно узнать, работает ли растровый индекс лучше.

Answer 3

Мои мысли: 1) составить план объяснения для всего, что вы хотите протестировать 2) поскольку IS_DELETE имеет значение NULLABLE, не уверен, что индекс будет использоваться, если не использовать индекс на основе функций, и посмотрите в плане объяснения, если это так

Я бы проверил ФБР, составной индекс, как

создать индекс P.I_IS_DELETE для ARCHIVED_ID(декодировать (IS_DELETE,null,'N','Y'),ARCHIVED_ID));

собирать статистику

exec dbms_stats.gather_table_stats(пользователь, 'ARCHIVED_ID', каскад =>TRUE);

изменить выбор соответственно

SELECT ID, FIELD1, FIELD2, FIELD3, FIELD4 ИЗ P.DWH_tableX A, ГДЕ ЕСТЬ Y ');

и посмотрим как пойдет...