html5网站模板免费,百度排名服务,湖北省城乡建设厅网站首页,wordpress 优化版本优化案例5#xff1a;视图目标列改写优化 1. 问题描述2. 分析过程2.1 目标SQL2.2 解决思路1#xff09;效率低的执行计划2#xff09;视图过滤性3#xff09;查看已有索引定义 2.3 视图改写2.4 增添复合索引 3. 优化总结 DM技术交流QQ群#xff1a;940124259 1. 问题描述… 优化案例5视图目标列改写优化 1. 问题描述2. 分析过程2.1 目标SQL2.2 解决思路1效率低的执行计划2视图过滤性3查看已有索引定义 2.3 视图改写2.4 增添复合索引 3. 优化总结 DM技术交流QQ群940124259 1. 问题描述
视图改写优化单独拿出一例分享未做hint优化简单地改写视图列和增加一个索引就能搞定。 这条SQL本身很简单被广州同事使出三板斧统计信息、索引、ET耗时、HINT、清理执行计划招式使尽却没去留意视图定义本身内容的特点利用视图的谓词下推的策略就能达到优化目的。
截图为同事部分一堆骚操作 2. 分析过程
2.1 目标SQL
-- 原始SQL代码
SELECT * FROM (SELECT A., ROWNUM R FROM(SELECT COUNT(1) OVER () RECORDCOUNT, M. from DISPLAYCENTER.WL_DDBB_WEEK_V mwhere m.bbidBB-DD-002 and bbrq20221014and hzb45 and lzb6 ) A where rownum 1000)b where r0; -- telphoning --4ms-- 视图原始定义WL_DDBB_WEEK_V
CREATE OR REPLACE VIEW WL_DDBB_WEEK_V AS
SELECT t1.bbzd_id bbid, bbmc,
SUBSTR (t1.bbzd_date, 1, 4) || SUBSTR (t1.bbzd_date, 7, 2) || SUBSTR (t1.bbzd_date, 9, 2) AS bbrq,
t1.hzd_nm hzb, t1.lzd_nm lzb, dyzd_sj as VALUE
FROM (SELECT
T1.*
FROM RAW_SMES.Bb_Dwsj_Tb T1) t1 ;2.2 解决思路
1效率低的执行计划 /*
-- predicate condition
1 #NSET2: [6597, 1, 912]
2 #PRJT2: [6597, 1, 912]; exp_num(8), is_atom(FALSE)
3 #SLCT2: [6597, 1, 912]; B.R var2
4 #PRJT2: [6597, 1, 912]; exp_num(8), is_atom(FALSE)
5 #RN: [6597, 1, 912]
6 #PRJT2: [6597, 1, 912]; exp_num(7), is_atom(FALSE)
7 #TOPN2: [6597, 1, 912]; top_num(exp11)
8 #AFUN: [6597, 1, 912]; afun_num(1); partition_num(0); order_num(0)
9 #PRJT2: [6597, 34, 912]; exp_num(6), is_atom(FALSE)
10 #SLCT2: [6597, 34, 912]; (exp_cast(T1.HZD_NM) 45 AND exp_cast(T1.LZD_NM) 6 AND exp11 || exp11 || exp11 20221014)
11 #BLKUP2: [6597, 2754812, 912]; IDX_BB_DWSJ(T1)
12 #SSEK2: [6597, 2754812, 912]; scan_type(ASC), IDX_BB_DWSJ(BB_DWSJ_TB as T1), scan_range[(BB-DD-002,min,min,min),(BB-DD-002,max,max,max))
*/从执行计划步骤12 SSEK2和步骤10 SLCT2操作符的附加信息可以看出视图的过滤条件被下放。 但回表大严重2754812行由此可以推断这表很大然而看着应用复合索引只能命中一个字段定位二次回表再过滤不慢才怪。 所以影响此SQL的罪魁祸首是回表200W的数据造成大量的逻辑读和磁盘读。
2视图过滤性
select count(*) from DISPLAYCENTER.WL_DDBB_WEEK_V ; -- 110 265 448 1亿1千万的数据行
select count(*) from DISPLAYCENTER.WL_DDBB_WEEK_V m where m.bbidBB-DD-002 and m.bbrq20221014; -- 816 过滤性极强
select count(*) from DISPLAYCENTER.WL_DDBB_WEEK_V m where m.bbidBB-DD-002 and hzb45 and lzb6 and bbrq20221014; -- 1视图里面只有一个基表且数据量庞大bbid和bbrq组合条件过滤性很强对它们建个索引效果更好。
3查看已有索引定义
/*
-- 表定义
CREATE TABLE RAW_SMES.BB_DWSJ_TB
(
QYZD_BH VARCHAR2(40),
DWZD_BH VARCHAR2(30),
BBZD_ID VARCHAR2(20),
BBZD_DATE VARCHAR2(10),
BBZD_YEAR VARCHAR2(10),
BBZD_MON VARCHAR2(10),
BBZD_DAY VARCHAR2(10),
BBZD_QUA VARCHAR2(10),
BBZD_TENDAY VARCHAR2(10),
BBZD_WEEK VARCHAR2(10),
HZD_ZB NUMBER,
LZD_ZB NUMBER,
H_BZBM VARCHAR2(30),
L_BZBM VARCHAR2(30),
DYZD_SJ VARCHAR2(500),
DYZD_DATA NUMBER(20,6),
XSSX NUMBER,
HZD_NM VARCHAR2(50),
LZD_NM VARCHAR2(50),
INSERT_ODS_TIME TIMESTAMP(0),
UPDATE_ODS_TIME TIMESTAMP(0),
M_ROW$$ VARCHAR2(128)) STORAGE(ON RAW_SCGK, CLUSTERBTR) ;-- 索引定义
CREATE UNIQUE INDEX UK_M_ROW ON RAW_SMES.BB_DWSJ_TB(M_ROW$$ ASC) STORAGE(ON RAW_SCGK, CLUSTERBTR) ;
CREATE INDEX IDX_BB_DWSJ ON RAW_SMES.BB_DWSJ_TB(BBZD_ID ASC,BBZD_DATE ASC,HZD_NM ASC,LZD_NM ASC) STORAGE(ON RAW_SCGK, CLUSTERBTR) ;
*/看到索引定义(BBZD_ID ASC,BBZD_DATE ASC,HZD_NM ASC,LZD_NM ASC) 时知道他们离优化成功半步之遥不懂BBZD_DATE字段被视图转换拼接 已不再是原始字段所以这个索引无法利用上第2个字段则解释清楚1)所说的执行计划涉及的回表严重。
2.3 视图改写 原始视图的bbrq视图列定义SUBSTR (t1.bbzd_date, 1, 4) || SUBSTR (t1.bbzd_date, 7, 2) || SUBSTR (t1.bbzd_date, 9, 2) AS bbrq 写得太复杂无非就是从字符类型的bbzd_date截取出合法的日期格式数据把一大趾函数转换简单化变成stuff函数减少复杂计算 还能让后面建函数索引更简单方便。
-- redefination view reduce function cost
CREATE OR REPLACE VIEW DISPLAYCENTER.WL_DDBB_WEEK_V
AS
SELECT
t1.bbzd_id bbid,bbmc ,
stuff(t1.bbzd_date, 5, 2, ) AS bbrq, -- 改写位置
t1.hzd_nm hzb ,
t1.lzd_nm lzb ,
dyzd_sj as VALUE
FROM
(
SELECT T1.* FROM RAW_SMES.Bb_Dwsj_Tb T1
)
t1 ;2.4 增添复合索引
create index idx_comb_bbid_hzb_lzb on “RAW_SMES”.“BB_DWSJ_TB”(BBZD_ID, STUFF(bbzd_date, 5, 2, ‘’), HZD_NM,LZD_NM ) ONLINE;
将就原来他们建的索引IDX_BB_DWSJ的逻辑把第2个字段替换成stuff函数。再来一探执行计划的变化不出意外的话将会充分利用上索引前两个字段的过滤性。
/* 执行时间4毫秒
1 #NSET2: [163, 1, 912]
2 #PRJT2: [163, 1, 912]; exp_num(8), is_atom(FALSE)
3 #SLCT2: [163, 1, 912]; B.R var2
4 #PRJT2: [163, 1, 912]; exp_num(8), is_atom(FALSE)
5 #RN: [163, 1, 912]
6 #PRJT2: [163, 1, 912]; exp_num(7), is_atom(FALSE)
7 #TOPN2: [163, 1, 912]; top_num(exp11)
8 #AFUN: [163, 1, 912]; afun_num(1); partition_num(0); order_num(0)
9 #PRJT2: [163, 68469, 912]; exp_num(6), is_atom(FALSE)
10 #SLCT2: [163, 68469, 912]; (exp_cast(T1.HZD_NM) 45 AND exp_cast(T1.LZD_NM) 6)
11 #BLKUP2: [163, 68469, 912]; IDX_COMB_BBID_HZB_LZB(T1)
12 #SSEK2: [163, 68469, 912]; scan_type(ASC), IDX_COMB_BBID_HZB_LZB(BB_DWSJ_TB as T1), scan_range[(BB-DD-002,20221014,min,min),(BB-DD-002,20221014,max,max))
*/执行计划BLKUP2 显示回表68469索引统计信息未收集收集一下就成。 总体来说优化已经达到预期目标4毫秒已经很nice。可能美中不足复合索引剩下两字段没用上跑到SLCT2作回表过滤。 细心地会发现(exp_cast(T1.HZD_NM) 45 AND exp_cast(T1.LZD_NM) 6) 出现exp_cast数据库内部隐式转换所以才漏掉。 喊他们把条件数字带上单引号【hzb45 and lzb6】避免类型转换也就解决索引全列过滤。 3. 优化总结 懂得索引合理创建不要乱建索引复合索引的组合字段弄得太多不是好事因为能利用上的索引键就一个或两个完全没意义弄这么多索引键潜在隐藏一个信息索引体积太大索引B树庞大可能引起大量的IO读写影响索引扫描的效率。 一定要充分利用索引特性够小体积小可以理解为表的瘦身版够高效过滤性强。 明白视图的优化手段无非包含视图上拉、视图合并等等优化思想。
