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;