报表优化交流_20110301

1、Leiyry北京华胜天成科技股份有限公司2011年3月报表优化原则l使用汇总表分区使用汇总表分区l避免避免left joinl去除冗余代码去除冗余代码l复杂视图物理化复杂视图物理化l增加中间表增加中间表l汇总表字段冗余汇总表字段冗余使用汇总表分区lsel * from pims_pmart2.tb_fct_sum_det_m where statsmt=201101lsel * from pims_pmart2.tb_fct_sum_det_m where statsmt=cast(201101 as date format yyyymm)使用汇总表分区1) First, we lock a

2、distinct pims_pmart2.pseudo table for read on a RowHash to prevent global deadlock for pims_pmart2.tb_fct_sum_det_m. 2) Next, we lock pims_pmart2.tb_fct_sum_det_m for read. 3) We do an all-AMPs RETRIEVE step from a single partition of pims_pmart2.tb_fct_sum_det_m with a condition of ( pims_pmart2.tb

3、_fct_sum_det_m.StatsMt = 201101) with a residual condition of (pims_pmart2.tb_fct_sum_det_m.StatsMt = 201101) into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 5,615,348 rows (662,611,064 bytes). The estimated time for this step

4、is 0.47 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. - The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.47 seconds.使用汇总表分区 1) First, we lock a distinct pims_pmart2.pseudo table fo

5、r read on a RowHash to prevent global deadlock for pims_pmart2.tb_fct_sum_det_m. 2) Next, we lock pims_pmart2.tb_fct_sum_det_m for read. 3) We do an all-AMPs RETRIEVE step from pims_pmart2.tb_fct_sum_det_m by way of an all-rows scan with a condition of ( (pims_pmart2.tb_fct_sum_det_m.StatsMt (DATE,

6、FORMAT yyyymm)= DATE 2011-01-01) into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 5,615,348 rows (662,611,064 bytes). The estimated time for this step is 2.91 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs

7、involved in processing the request. - The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 2.91 seconds. 使用汇总表分区CREATE MULTISET TABLE pims_pmart2.tb_fct_sum_det_m ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( StatsMt C

8、HAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 统计日期统计日期 NOT NULL, CpOrgCd CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政组织机构代码邮政组织机构代码 NOT NULL, PostBusnTypCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 业务种类代码业务种类代码 NOT NULL, PostAttr CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TIT

9、LE 函件属性函件属性 NOT NULL, SumInd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 汇总数据标志汇总数据标志 NOT NULL, SysSrc CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 系统来源系统来源 NOT NULL, ProvDistCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 省行政区划代码省行政区划代码 NOT NULL, CpBusnDivCd CHAR(2) CHARACTER SET

10、 LATIN NOT CASESPECIFIC TITLE 业务专业代码业务专业代码 NOT NULL,)UNIQUE PRIMARY INDEX ( StatsMt ,CpOrgCd ,PostBusnTypCd ,PostAttr ,SumInd ,SysSrc ,ProvDistCd )PARTITION BY RANGE_N(CAST(StatsMt ) AS DATE FORMAT YYYYMM) BETWEEN DATE 2000-01-01 AND DATE 2100-12-31 EACH INTERVAL 1 MONTH );使用汇总表分区CREATE MULTISET TAB

11、LE pims_pmart2.tb_fct_sum_det_d ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( StatsDt DATE FORMAT YYYYMMDD TITLE 统计日期 NOT NULL, CpOrgCd CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政组织机构代码邮政组织机构代码 NOT NULL, PostBusnTypCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC

12、TITLE 业务种类代码业务种类代码 NOT NULL, PostAttr CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 函件属性函件属性 NOT NULL, SumInd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 汇总数据标志汇总数据标志 NOT NULL, SysSrc CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 系统来源系统来源 NOT NULL, ProvDistCd CHAR(6) CHARACTER SET L

13、ATIN NOT CASESPECIFIC TITLE 省行政区划代码省行政区划代码 NOT NULL, CpBusnDivCd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 业务专业代码业务专业代码 NOT NULL)UNIQUE PRIMARY INDEX ( StatsDt ,CpOrgCd ,PostBusnTypCd ,PostAttr ,SumInd ,SysSrc ,ProvDistCd )PARTITION BY RANGE_N(StatsDt BETWEEN DATE 2000-01-01 AND DATE 2100-1

14、2-31 EACH INTERVAL 1 DAY );使用汇总表分区l分区分区DDLl分区允许类型：数字型、日期型分区允许类型：数字型、日期型l分区数量限制：分区数量限制：65535lSQL中使用分区字段中使用分区字段l按照按照DDL使用分区字段使用分区字段报表优化原则l使用汇总表分区使用汇总表分区l避免避免left joinl去除冗余代码去除冗余代码l复杂视图物理化复杂视图物理化l增加中间表增加中间表l汇总表字段冗余汇总表字段冗余避免left joinl汇总表中维度字段已经做过处理，无需汇总表中维度字段已经做过处理，无需left join代码表代码表l含有累计数报表含有累计数报表l不同主题汇

15、总表关联不同主题汇总表关联l汇总表有无数据，需要保证表样报表汇总表有无数据，需要保证表样报表累计数报表累计数报表Sel from 累计数表累计数表Where UnionSel from 当期数表当期数表Where 注意字段类型和长度注意字段类型和长度不同主题汇总表关联不同主题汇总表关联Sel from 主题一主题一Where UnionSel from 主题二主题二Where 注意字段类型和长度注意字段类型和长度固定表样固定表样Sel from 代码表代码表1，代码表，代码表2 -笛卡尔积笛卡尔积Where UnionSel from 汇总表汇总表Where 注意字段类型和长度，注意笛卡尔积的

16、数量注意字段类型和长度，注意笛卡尔积的数量报表优化原则l使用汇总表分区使用汇总表分区l避免left joinl去除冗余代码去除冗余代码l复杂视图物理化复杂视图物理化l增加中间表增加中间表l汇总表字段冗余汇总表字段冗余报表优化原则l使用汇总表分区使用汇总表分区l避免left joinl去除冗余代码去除冗余代码l复杂视图物理化复杂视图物理化l增加中间表增加中间表l汇总表字段冗余汇总表字段冗余复杂视图物理化-案例l自定义维度自定义维度n自定义机构自定义机构tb_prt_cporg_unionn自定义业务种类自定义业务种类TB_CDE_CPBUSNTYP_UNION自定义机构CREATE MULTIS

17、ET TABLE pims_pdata.tb_prt_cporg_union , ( CpOrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政机构代码邮政机构代码 NOT NULL, CpOrgNm VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政机构名称邮政机构名称 NOT NULL, CpOrgFullNm VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政机构全称邮政机构全称, SubCp

18、OrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下级邮政机构代下级邮政机构代码码, SubCpOrgLevCd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下级邮政机构级下级邮政机构级别代码别代码, CpOrgLevCd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政机构级别代码邮政机构级别代码, ParntCpOrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPE

19、CIFIC TITLE 上级邮政机构上级邮政机构代码代码, SortNum DECIMAL(11,0) TITLE 顺序号, unionmod INTEGER)PRIMARY INDEX PXI ( SubCpOrgCd )PARTITION BY unionmod ;自定义机构/*机构和自定义机构，机构和自定义机构，unionmod=1是机构，是机构，unionmod=2是自定义机构，是自定义机构，unionmod=3是自定义机构下转是自定义机构下转*/ and ( (b.unionmod=1 and (#prompt(trace,integer,0)# in (0) and b.CpOrg

20、Cd in (#csv( split ( , prompt(area,token,) )#) or (#prompt(trace,integer,0)# in (1) and b.ParntCpOrgCd in (#csv( split ( , prompt(area,token,) )#) ) or (b.unionmod=2 and (#prompt(trace,integer,0)# in (-2) and b.CpOrgCd in (#csv( split ( , prompt(area,token,) )#) ) or (b.unionmod=3 and (#prompt(trace

21、,integer,0)# in (-3) and b.ParntCpOrgCd in (#csv( split ( , prompt(area,token,) )#) ) )自定义业务种类CREATE MULTISET TABLE pims_pdata.TB_CDE_CPBUSNTYP_UNION , ( CpBusnDivCd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政业务专业代码邮政业务专业代码 NOT NULL, CpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECI

22、FIC TITLE 邮政业务种类代邮政业务种类代码码 NOT NULL, CpBusnTypDesc VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 邮政业务种邮政业务种类描述类描述, SubCpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下级邮政业下级邮政业务种类代码务种类代码, StrtDt DATE FORMAT YYYYMMDD TITLE 起始日期, EndDt DATE FORMAT YYYYMMDD TITLE 结束日期, CpBusn

23、TypLev INTEGER, ParntCpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 上级邮政上级邮政业务种类代码业务种类代码, SortNum DECIMAL(10,0) TITLE 排序序号, unionmod INTEGER)PRIMARY INDEX PXI ( CpBusnDivCd ,SubCpBusnTypCd )PARTITION BY unionmod ;自定义业务种类/*业务种类和自定义业务种类，业务种类和自定义业务种类，unionmod=1是业务种类，是业务种类，unionmod=2是自定义业务种类是自定义业务种类，unionmod=3是自定义业务种类下转是自定义业务种类下转*/and ( (f.unionmod=1 and (#prompt(kindtrace,integer,0)# = 0 and f.CpBusnTypCd in (#csv( split ( , prompt(kindvalue,token,) )#) or (#prompt(kindtrace,integer,0)# = 1