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

Предполагая, что 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;

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

Я бы сказал, что ваша проблема заключается в том, что вы делаете много ЛЕВЫХ СОЕДИНЕНИЙ, и окончательный набор результатов становится слишком большим после применения всех этих СОЕДИНЕНИЙ. Также индексы нельзя использовать таким образом для расчета 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. В случае, если между t1 и t4 отношение 1:1, было бы даже лучше написать что-то вроде этого во второй строке:

(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 APPLY серверу пришлось бы загружать в память все отдельные даты из t5 и вычислять MAX из всего загруженного набора результатов.

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

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

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