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

Ако приемем, че id е първичен ключ в t1, вашата заявка може (или не може, зависи от настройката на PGA на вашия Oracle) да работи по-добре, когато е написана по следния начин:

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;

Това пренаписване не налага никаква необходимост от създаване на допълнителни, но иначе безполезни индекси.

Бих казал, че вашият проблем е, че правите много LEFT JOINs и крайният набор от резултати става твърде голям след прилагането на всички тези JOINs. Също така индексите не могат да се използват по този начин за изчисляване на 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 от неиндексираните данни, което отнема много време, тъй като има високи изисквания към I/O операциите . Лошото използване на индекси при използване на MIN или MAX може да доведе до ситуацията, при която всичките ви исторически данни от таблицата са кеширани в паметта, без да са ви необходими за нищо друго освен изчисление MIN или MAX.

Без частта CROSS APPLY на заявката сървърът ще трябва да зареди в паметта всички отделни дати от t5 и да изчисли MAX от целия зареден набор от резултати.

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

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

Проверете този SQL Fiddle, особено разликите в плановете за изпълнение.