本文概述（表名已做脱敏处理）：
数据库视图常用于封装一些复杂冗长的sql,减少宽带传输，增加可读性
但视图如果写的较复杂时，特别是一些标量子查询存在优化点时，从视图外部无法调优。可考虑把视图写出来再优化
本文中的优化主要还是针对：分区表全分区扫描、标量子查询冗余、分区表中如何快速求最大日期等的一些优化手段
提醒下，优化后的语句一定要严格测试，确保数据与之前语句跑出的数据完全匹配后在上线。本文中用的是intersect匹配交集
今天在监视数据库时，发现一条长查询的语句可以优化。其主要原因是查询语句引用到的一张视图写的比较烂
原始的sql语句
select ORDER_TYPE_NAME, ORDER_ID, to_char(ACCT_DATE, 'YYYY-MM-DD HH24:MI:SS') as ACCT_DATE, TERM_ID, TERM_SEQ, PRTN_ID, CUST_TYPE, CUST_TYPE_NAME, CUST_CODE, CUST_NAME, ACTION_CODE, ACTION_NAME, OBJ_CODE, CARD_NUMBER, FACE_VALUE_NAME, to_char(DUE_MONEY) as DUE_MONEY, to_char(PAY_MONEY) as PAY_MONEY, to_char(END_BAL) as END_BAL, ORDER_STAT, ORDER_STAT_NAME, PAY_STAT, PAY_STAT_NAME, ACT_STAT, ACT_STAT_NAME, ERR_CODE, AREA_CODE, AREA_NAME, ORDER_TYPE, ACCT_CODE, GOODS_NAME, EVENT_SEQ from VIEW_EXP_TMP where 1 = 1 and ACCT_DATE >= to_date('2015-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and ACCT_DATE <= to_date('2015-11-26 23:59:59', 'YYYY-MM-DD HH24:MI:SS') and AREA_CODE in (SELECT AREA_CODE FROM T_AREACODE START WITH AREA_CODE = '000000' CONNECT BY PARENT_AREA = PRIOR AREA_CODE) and ORDER_TYPE = 'OT501' |
统计信息
我们可以看到大量的重复标量子查询引用，并有些range all.我的优化点也主要是优化VIEW_EXP_TMP这个视图
view VIEW_EXP_TMP code
CREATE OR REPLACE VIEW VIEW_EXP_TMP (area_code, area_name, order_type, order_type_name, order_id, acct_date, acct_date_str, term_id, term_seq, prtn_id, cust_type, cust_type_name, cust_code, cust_name, action_code, action_name, obj_code, card_number, face_value_name, due_money, pay_money, end_bal, order_stat, order_stat_name, pay_stat, pay_stat_name, act_stat, act_stat_name, err_code, acct_code, goods_name, event_seq) AS SELECT ord.area_code, (SELECT area_name FROM T_AREACODE WHERE area_code = ord.area_code) area_name, ord.order_type, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ORDERTYPE' AND dict_id = ord.order_type) order_type_name, ord.order_id, ord.acct_date, TO_CHAR(ord.acct_date, 'yyyy-mm-dd hh24:mi:ss') acct_date_str, ord.term_id, ord.term_seq, DECODE(tci.prtn_id, 0, 1, tci.prtn_id) prtn_id, tci.cust_type, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'CUM_CUSTTYPE' AND dict_id = (SELECT cust_type FROM T_CLIENT_INFO WHERE cust_id = ord.cust_id)) cust_type_name, (SELECT cust_code FROM T_CLIENT_INFO WHERE cust_id = ord.cust_id) cust_code, (SELECT cust_name FROM T_CLIENT_INFO WHERE cust_id = ord.cust_id) cust_name, (SELECT action_code FROM T_ACTION WHERE action_id = tac.action_id) action_code, (SELECT action_name FROM T_ACTION WHERE action_id = tac.action_id) action_name, tac.obj_code, (SELECT dtl_info FROM T_QUERY_DORDER WHERE dtl_id = '1531' AND event_seq = tac.event_seq) card_number, -- 卡号 (SELECT dtl_info FROM T_QUERY_DORDER WHERE dtl_id = 'B525' AND event_seq = tac.event_seq) face_value_name, -- 卡面值 (SELECT due_moeny FROM T_FEE WHERE order_id = ord.order_id) due_moeny, (SELECT pay_money FROM T_FEE WHERE order_id = ord.order_id) pay_money, ((SELECT MAX(end_bal) FROM T_BANK_DEALLOG WHERE acct_code NOT IN ('440000000000000001', '440000000000000002') AND event_seq = tac.event_seq) - (SELECT NVL((SELECT th FROM t_cum_custom WHERE custom_type = 'T10' AND action_id = tac.action_id AND th_type = 'TH001' AND cust_id = ord.cust_id), 0) FROM dual)) end_bal, -- 交易后余额 ord.stat order_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ORDERSTAT' AND dict_id = ord.stat) order_stat_name, ord.pay_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_PAYSTAT' AND dict_id = ord.pay_stat) pay_stat_name, ord.act_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ACTSTAT' AND dict_id = ord.act_stat) act_stat_name, (SELECT q.dtl_info FROM T_QUERY_DORDER q, T_ACCT aa --T_ACCT没有必须在这在关联次，因为下面的from有这张表，可直接引用 WHERE q.dtl_id = '0001' AND q.event_seq = tac.event_seq and aa.order_id = ord.order_id and aa.END_DATE = (select max(x.end_date) from T_ACCT x)) err_code, -- 错误编码 (SELECT acct_code FROM T_INFOPAYMENT --交易支付表是张大表，可指定分区谓词 WHERE order_id = ord.order_id and rownum = 1) acct_code, --商户账号 (SELECT VALUE1 FROM t_scs_act_attr WHERE ACTLIST_ID = tac.actlist_id AND attr_id = '1016') goods_name, --商品名称 tac.event_seq -- 流水号 FROM T_CLIENT_INFO tci, T_ACCT tac, t_scs_order ord WHERE tac.order_id = ord.order_id and tci.cust_id = ord.cust_id --order by ord.acct_date desc; ; |
T_CLIENT_INFO这张表被重复关联了4次，其实只要一次即可
T_ACTION这张表被重复关联了2次，其实只要一次即可
标题查询重复关联的还有：T_FEE
优化后的语句我建议视图单独拿出来跑，因为这样可以加一些分区列避免全分区扫描。放在一个视图中灵活性不强
SELECT (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ORDERTYPE' AND dict_id = ord.order_type) order_type_name, ord.order_id, to_char(ACCT_DATE, 'YYYY-MM-DD HH24:MI:SS') as acct_date, ord.term_id, ord.term_seq, DECODE(tci.prtn_id, 0, 1, tci.prtn_id) prtn_id, tci.cust_type, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'CUM_CUSTTYPE' AND dict_id = tci.cust_type) cust_type_name, tci.cust_code cust_code, tci.cust_name cust_name, action.action_code, action.action_name, tac.obj_code, (SELECT dtl_info FROM T_QUERY_DORDER WHERE dtl_id = '1531' AND event_seq = tac.event_seq) card_number, -- 卡号 (SELECT dtl_info FROM T_QUERY_DORDER WHERE dtl_id = 'B525' AND event_seq = tac.event_seq) face_value_name, -- 卡面值 to_char(fee.due_moeny) as due_money, to_char(fee.pay_money) as pay_money, to_char(((SELECT MAX(end_bal) FROM T_BANK_DEALLOG WHERE acct_code NOT IN ('440000000000000001', '440000000000000002') AND event_seq = tac.event_seq) - (SELECT NVL((SELECT th FROM t_cum_custom WHERE custom_type = 'T10' AND action_id = tac.action_id AND th_type = 'TH001' AND cust_id = ord.cust_id), 0) FROM dual))) end_bal, -- 交易后余额 ord.stat order_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ORDERSTAT' AND dict_id = ord.stat) order_stat_name, ord.pay_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_PAYSTAT' AND dict_id = ord.pay_stat) pay_stat_name, ord.act_stat, (SELECT dict_name FROM T_DICTIONARY WHERE dict_typeid = 'SCS_ACTSTAT' AND dict_id = ord.act_stat) act_stat_name, (SELECT q.dtl_info FROM T_QUERY_DORDER q WHERE q.dtl_id = '0001' AND q.event_seq = tac.event_seq and tac.END_DATE = (select max(x.end_date) from T_ACCT x where CREATE_DATE >= (select max(create_date) - 1 from T_ACCT))) err_code, -- 错误编码 *max(x.end_date)这玩艺应该是要标记下是否是最新结束的订单，看业务只是需要得到最新的处理日期， 可详细指定只扫描最大的那个分区*/ ord.area_code, area.area_name, ord.order_type, (SELECT acct_code FROM T_INFOPAYMENT WHERE order_id = ord.order_id and rownum = 1 and CREATE_DATE between to_date('2015-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2015-11-26 23:59:59', 'YYYY-MM-DD HH24:MI:SS') + 1) acct_code, --商户账号 (SELECT VALUE1 FROM t_scs_act_attr WHERE ACTLIST_ID = tac.actlist_id AND attr_id = '1016') goods_name, --商品名称 tac.event_seq -- 流水号 FROM T_CLIENT_INFO tci, T_ACCT tac, t_scs_order ord, T_ACTION action, T_FEE fee, (SELECT AREA_CODE, area_name FROM T_AREACODE START WITH AREA_CODE = '000000' CONNECT BY PARENT_AREA = PRIOR AREA_CODE) area WHERE tac.order_id = ord.order_id and tci.cust_id = ord.cust_id and tac.action_id = action.action_id(+) and ord.order_id = fee.order_id and ord.AREA_CODE = area.AREA_CODE
and ord.ACCT_DATE >= to_date('2015-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and ord.ACCT_DATE <= to_date('2015-11-26 23:59:59', 'YYYY-MM-DD HH24:MI:SS') and tac.CREATE_DATE >= to_date('2015-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and tac.CREATE_DATE <= to_date('2015-11-26 23:59:59', 'YYYY-MM-DD HH24:MI:SS') + 1 and ord.ORDER_TYPE = 'OT501' |
优化后的统计信息
主要是优化了些标量子查询、全分区扫描等。大家可以用beyone compare对比看区别
我们来看下执行后的时间对比
优化后
执行只需7秒
优化前需要34秒
用instersect对比结果数据也是吻合的
