Настройка / перезапись SQL-запроса со многими левыми внешними объединениями и тяжелыми таблицами

Question

У меня есть четыре - пять таблиц, которые действительно большие по размеру, и они оставлены внешне объединенными с использованием запроса ниже. Есть ли способ переписать его, чтобы улучшить производительность?

SELECT t1.id,
     MIN(t5.date) AS first_pri_date,
     MIN(t3.date) AS first_pub_date,
     MAX(t3.date) AS last_publ_date,
     MIN(t2.date) AS first_exp_date
FROM t1
    LEFT JOIN t2 ON (t1.id = t2.id)
    LEFT JOIN t3 ON (t3.id = t1.id)
    LEFT JOIN t4 ON (t1.id = t4.id)
    LEFT JOIN t5 ON (t5.p_id =t4.p_id)
GROUP BY t1.id
ORDER BY t1.id;

Количество записей:

• t1: 6434323
• t2: 6934562
• t3: 9141420
• t4: 11515192
• t5: 3797768

Есть индексы в большинстве столбцов, используемых для объединения. Наиболее трудоемкой частью плана объяснения является внешнее соединение с t4 что происходит в конце концов. Я просто хотел узнать, есть ли способ переписать это, чтобы улучшить производительность.

Answer 1

Я бы сказал, что ваша проблема в том, что вы делаете много ЛЕВЫХ СОЕДИНЕНИЙ, и после применения всех этих СОЕДИНЕНИЙ окончательный набор результатов становится слишком большим. Также индексы не могут быть использованы таким образом, чтобы вычислить MIN или MAX самым быстрым способом. При правильном использовании индексов вы сможете очень быстро рассчитать MIN или MAX.

Я бы написал запрос так:

SELECT t1.id,     
(SELECT MIN(t5.date) FROM t5 JOIN t4 ON t5.p_id = t4.p_id WHERE t4.id = t1.id) AS first_pri_date,
(SELECT MIN(date) FROM t3 WHERE t3.id = t1.id) AS first_pub_date,
(SELECT MAX(date) FROM t3 WHERE t3.id = t1.id)  AS last_publ_date,
(SELECT MIN(date) FROM t2 WHERE t2.id = t1.id) AS first_exp_date
FROM t1
ORDER BY t1.id;

Для лучшей производительности создайте индексы на (id, date) или же (p_id, date), Таким образом, ваши индексы будут выглядеть так:

CREATE INDEX ix2 ON T2 (id,date);
CREATE INDEX ix3 ON T3 (id,date);
CREATE INDEX ix5 ON T5 (p_id,date);
CREATE INDEX ix4 ON T4 (id);

Но все еще остается проблема с объединением t4 а также t5, В случае, если есть соотношение 1:1 между t1 а также t4было бы лучше написать что-то вроде этого во второй строке:

(SELECT MIN(t5.date) FROM t5 WHERE t5.p_id = (SELECT p_id FROM t4 WHERE t4.id=t1.id)) AS first_pri_date,

Если оно равно 1:N, а также если CROSS APPLY и OUTER APPLY работают в вашей версии Oracle, вы можете переписать вторую строку следующим образом:

 (SELECT MIN(t5min.PartialMinimum) 
 FROM t4 
 CROSS APPLY 
 (
    SELECT PartialMinimum = MIN(t5.date)
    FROM t5
    WHERE t5.p_id = t4.p_id
 ) AS t5min
 WHERE t4.id = t1.id) 
 AS first_pri_date

Все это нацелено на максимально эффективное использование индексов при расчете MIN или MAX. Таким образом, весь SELECT можно переписать так:

SELECT t1.id,     
 (SELECT MIN(t5min.PartialMinimum) 
 FROM t4 
 CROSS APPLY 
 (
    SELECT TOP 1 PartialMinimum = date
    FROM t5
    WHERE t5.p_id = t4.p_id
    ORDER BY 1 ASC
 ) AS t5min
 WHERE t4.id = t1.id)  AS first_pri_date,
(SELECT TOP 1 date FROM t2 WHERE t2.id = t1.id ORDER BY 1 ASC)  AS first_exp_date,
(SELECT TOP 1 date FROM t3 WHERE t3.id = t1.id ORDER BY 1 ASC)  AS first_pub_date,
(SELECT TOP 1 date FROM t3 WHERE t3.id = t1.id ORDER BY 1 DESC)  AS last_publ_date
FROM t1 
ORDER BY 1;

Это, как я считаю, самый оптимальный способ, как получить MIN или MAX из таблицы исторических данных.

Дело в том, что использование MIN с большим количеством неиндексированных значений заставляет сервер загружать все данные в память, а затем вычислять MIN или MAX из неиндексированных данных, что занимает много времени, поскольку предъявляет высокие требования к операциям ввода-вывода., Неправильное использование индексов при использовании MIN или MAX может привести к ситуации, когда у вас все данные хронологической таблицы будут кешироваться в памяти, не нуждаясь в них ни для чего, кроме вычисления MIN или MAX.

Без части запроса CROSS APPLYY сервер должен был бы загрузить в память все отдельные даты из t5 и вычислить MAX из всего загруженного набора результатов.

Отметьте, что функция MIN в правильно проиндексированной таблице ведет себя как TOP 1 ORDER BY, что очень быстро. Таким образом, вы можете получить свои результаты мгновенно.

CROSS APPLY доступен в Oracle 12C, в противном случае вы можете использовать конвейерные функции.

Проверьте эту скрипту SQL, особенно различия в планах выполнения.

Answer 2

При условии, что id является первичным ключом в t1ваш запрос может (или может не зависеть от настройки вашей Oracle PGA) работать лучше, когда написано следующее:

SELECT --+ leading(t1) use_hash(t2x,t3x,t45x) full(t1) no_push_pred(t2x) no_push_pred(t3x) no_push_pred(t45x) all_rows
    t1.id,
    t45x.first_pri_date,
    t3.first_pub_date,
    t3.last_publ_date,
    t2.first_exp_date
FROM t1
    LEFT JOIN (
        SELECT t2.id,
            MIN(t2.date) AS first_exp_date
        FROM t2
        GROUP BY t2.id
    ) t2x
        ON t2x.id = t1.id
    LEFT JOIN (
        SELECT t3.id,
            MIN(t3.date) AS first_pub_date,
            MAX(t3.date) AS last_publ_date
        FROM t3
        GROUP BY t3.id
    ) t3x
        ON t3x.id = t1.id
    LEFT JOIN (
        SELECT --+ leading(t5) use_hash(t4)
            t4.id,
            MIN(t5.date) AS first_pri_date
        FROM t4
            JOIN t5 ON t5.p_id = t4.p_id
        GROUP BY t4.id
    ) t45x
        ON t45x.id = t1.id
ORDER BY t1.id;

Это переписывание не требует создания дополнительных, но в то же время бесполезных индексов.