SQL优化

CREATE TABLE temp_table_name 
TABLESPACE temp_tablespace COMPRESS FOR QUERY HIGH LOGGING PARALLEL 8 AS 
SELECT /*+parallel(t,8)*/ 
    t.phone_number,
    TO_CHAR(t.event_date,'MM') || '月' || TO_CHAR(t.event_date,'dd') || '' ||
    TO_CHAR(t.event_date,'hh24:mi:ss') || '' || 
    CASE WHEN t4.call_id IS NULL THEN '0' ELSE TO_CHAR(COUNT(*)) END || '' || 
    CASE 
        WHEN t3.channel_type = '' AND t2.agent_name = '' THEN '' 
        ELSE '' || t3.channel_type || t3.channel_name || 
             '' || t2.agent_name || CASE WHEN t2.agent_name IS NOT NULL THEN '' ELSE '' END || t1.service_name || '。' 
    END AS call_info
FROM main_table t
JOIN service_offer_table t1 
    ON t.service_offer_id = t1.service_offer_id
    AND t1.service_offer_id IN ('1001', '1002', '1003', '1004', '1005', '1006', '1007', '1008', '1009', '1010')
LEFT JOIN agent_info_table t2 
    ON t.accept_agent_id = t2.user_id 
    AND t2.record_date = TO_CHAR(SYSDATE - 4, 'yyyymmdd')
LEFT JOIN channel_info_table t3 
    ON t3.channel_id = t.update_org 
    AND t3.record_date = TO_CHAR(SYSDATE - 4, 'yyyymmdd')
LEFT JOIN call_history_table t4
    ON t4.call_id = t.phone_number
    AND t4.call_end_time <= t.event_date
    AND TO_CHAR(t4.call_end_time, 'YYYYMMDD') = TO_CHAR(t.event_date, 'YYYYMMDD')
JOIN temp_table t5
    ON t.phone_number = t5.phone_number
WHERE 
    TO_CHAR(t.event_date, 'yyyymmdd') >= TO_CHAR(SYSDATE - 33, 'yyyymmdd') 
    AND TO_CHAR(t.event_date, 'yyyymmdd') <= TO_CHAR(SYSDATE - 3, 'yyyymmdd')
GROUP BY 
    t.phone_number, t.event_date, t3.channel_type, t3.channel_name, t2.agent_name, t1.service_name, t4.call_id
ORDER BY t.event_date;

对上面的代码，在PL/SQL数据库中进行查询时，跑了半小时以上一些没有跑出来，于是我在此做了优化，最终使结果在16分钟内跑出来。

1. 首先t5表的数据量更少，考虑到t4表的数据量较大。选择先计算其他表的数据，然后生成结果集再join t4表

2.  首先，先单独进行join t1,t2,t3,t5。  为了提高性能，使用了高压缩 + compress high query + nologging的方式创建单独的临时表，这样第一部分使用半分钟就执行出来

3.接着用第一部分的内容join t4表，然后 group by count(*) 最终生成了想要的结果。计算速度大大提高

    优化执行顺序，创建临时表是一种手段。

优化sql

CREATE TABLE temp_schema.temp_table_name COMPRESS FOR QUERY HIGH NOLOGGING TABLESPACE temp_tablespace AS
SELECT
    /*+parallel(t,8)*/
    t.phone_number,
    t.event_date,
    t3.channel_type,
    t3.channel_name,
    t2.agent_name,
    t1.service_name
FROM
    main_schema.main_table t
JOIN
    dict_schema.service_offer_table t1 ON t.service_offer_id = t1.service_offer_id
    AND t1.service_offer_id IN ('1001', '1002', '1003', '1004', '1005', '1006', '1007', '1008', '1009', '1010')
LEFT JOIN
    agent_schema.agent_info_table t2 ON t.accept_agent_id = t2.user_id
    AND t2.record_date = TO_CHAR(SYSDATE - 5, 'yyyymmdd')
LEFT JOIN
    channel_schema.channel_info_table t3 ON t3.channel_id = t.update_org
    AND t3.record_date = TO_CHAR(SYSDATE - 5, 'yyyymmdd')
JOIN
    temp_schema.temp_accnbr_table t5 ON t.phone_number = t5.phone_number
WHERE
    TO_CHAR(t.event_date, 'yyyymmdd') >= TO_CHAR(SYSDATE - 34, 'yyyymmdd')
    AND TO_CHAR(t.event_date, 'yyyymmdd') <= TO_CHAR(SYSDATE - 4, 'yyyymmdd');



SELECT
    temp_table_name.phone_number,
    TO_CHAR(temp_table_name.event_date, 'MM') || '' || TO_CHAR(temp_table_name.event_date, 'dd') || '' ||
    TO_CHAR(temp_table_name.event_date, 'hh24:mi:ss') || '' ||
    CASE WHEN t4.call_id IS NULL THEN '0' ELSE TO_CHAR(COUNT(*)) END || '' ||
    CASE
        WHEN temp_table_name.channel_type = '' AND temp_table_name.agent_name = '' THEN ''
        ELSE '' || temp_table_name.channel_type || temp_table_name.channel_name || ',' || temp_table_name.agent_name ||
             CASE WHEN temp_table_name.agent_name IS NOT NULL THEN '' ELSE '' END || temp_table_name.service_name || '。'
    END AS call_info
FROM
    temp_schema.temp_table_name
LEFT JOIN
    call_schema.call_history_table t4 ON t4.call_id = temp_table_name.phone_number
    AND TO_CHAR(t4.call_end_time, 'YYYYMMDD') = TO_CHAR(temp_table_name.event_date, 'YYYYMMDD')
    AND t4.call_end_time <= temp_table_name.event_date
GROUP BY
    temp_table_name.phone_number,
    temp_table_name.event_date,
    temp_table_name.channel_type,
    temp_table_name.channel_name,
    temp_table_name.agent_name,
    temp_table_name.service_name,
    t4.call_id
ORDER BY
    temp_table_name.event_date;