報表優(yōu)化交流_20110301

1、Leiyry北京華勝天成科技股份有限公司2011年3月報表優(yōu)化原則l使用匯總表分區(qū)使用匯總表分區(qū)l避免避免left joinl去除冗余代碼去除冗余代碼l復(fù)雜視圖物理化復(fù)雜視圖物理化l增加中間表增加中間表l匯總表字段冗余匯總表字段冗余使用匯總表分區(qū)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)使用匯總表分區(qū)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.使用匯總表分區(qū) 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. 使用匯總表分區(qū)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 統(tǒng)計日期統(tǒng)計日期 NOT NULL, CpOrgCd CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政組織機構(gòu)代碼郵政組織機構(gòu)代碼 NOT NULL, PostBusnTypCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 業(yè)務(wù)種類代碼業(yè)務(wù)種類代碼 NOT NULL, PostAttr CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TIT

9、LE 函件屬性函件屬性 NOT NULL, SumInd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 匯總數(shù)據(jù)標(biāo)志匯總數(shù)據(jù)標(biāo)志 NOT NULL, SysSrc CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 系統(tǒng)來源系統(tǒng)來源 NOT NULL, ProvDistCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 省行政區(qū)劃代碼省行政區(qū)劃代碼 NOT NULL, CpBusnDivCd CHAR(2) CHARACTER SET

10、 LATIN NOT CASESPECIFIC TITLE 業(yè)務(wù)專業(yè)代碼業(yè)務(wù)專業(yè)代碼 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 );使用匯總表分區(qū)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 統(tǒng)計日期 NOT NULL, CpOrgCd CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政組織機構(gòu)代碼郵政組織機構(gòu)代碼 NOT NULL, PostBusnTypCd CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC

12、TITLE 業(yè)務(wù)種類代碼業(yè)務(wù)種類代碼 NOT NULL, PostAttr CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 函件屬性函件屬性 NOT NULL, SumInd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 匯總數(shù)據(jù)標(biāo)志匯總數(shù)據(jù)標(biāo)志 NOT NULL, SysSrc CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 系統(tǒng)來源系統(tǒng)來源 NOT NULL, ProvDistCd CHAR(6) CHARACTER SET L

13、ATIN NOT CASESPECIFIC TITLE 省行政區(qū)劃代碼省行政區(qū)劃代碼 NOT NULL, CpBusnDivCd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 業(yè)務(wù)專業(yè)代碼業(yè)務(wù)專業(yè)代碼 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 );使用匯總表分區(qū)l分區(qū)分區(qū)DDLl分區(qū)允許類型：數(shù)字型、日期型分區(qū)允許類型：數(shù)字型、日期型l分區(qū)數(shù)量限制：分區(qū)數(shù)量限制：65535lSQL中使用分區(qū)字段中使用分區(qū)字段l按照按照DDL使用分區(qū)字段使用分區(qū)字段報表優(yōu)化原則l使用匯總表分區(qū)使用匯總表分區(qū)l避免避免left joinl去除冗余代碼去除冗余代碼l復(fù)雜視圖物理化復(fù)雜視圖物理化l增加中間表增加中間表l匯總表字段冗余匯總表字段冗余避免left joinl匯總表中維度字段已經(jīng)做過處理，無需匯總表中維度字段已經(jīng)做過處理，無需left join代碼表代碼表l含有累計數(shù)報表含有累計數(shù)報表l不同主題匯

15、總表關(guān)聯(lián)不同主題匯總表關(guān)聯(lián)l匯總表有無數(shù)據(jù)，需要保證表樣報表匯總表有無數(shù)據(jù)，需要保證表樣報表累計數(shù)報表累計數(shù)報表Sel from 累計數(shù)表累計數(shù)表Where UnionSel from 當(dāng)期數(shù)表當(dāng)期數(shù)表Where 注意字段類型和長度注意字段類型和長度不同主題匯總表關(guān)聯(lián)不同主題匯總表關(guān)聯(lián)Sel from 主題一主題一Where UnionSel from 主題二主題二Where 注意字段類型和長度注意字段類型和長度固定表樣固定表樣Sel from 代碼表代碼表1，代碼表，代碼表2 -笛卡爾積笛卡爾積Where UnionSel from 匯總表匯總表Where 注意字段類型和長度，注意笛卡爾積的

16、數(shù)量注意字段類型和長度，注意笛卡爾積的數(shù)量報表優(yōu)化原則l使用匯總表分區(qū)使用匯總表分區(qū)l避免left joinl去除冗余代碼去除冗余代碼l復(fù)雜視圖物理化復(fù)雜視圖物理化l增加中間表增加中間表l匯總表字段冗余匯總表字段冗余報表優(yōu)化原則l使用匯總表分區(qū)使用匯總表分區(qū)l避免left joinl去除冗余代碼去除冗余代碼l復(fù)雜視圖物理化復(fù)雜視圖物理化l增加中間表增加中間表l匯總表字段冗余匯總表字段冗余復(fù)雜視圖物理化-案例l自定義維度自定義維度n自定義機構(gòu)自定義機構(gòu)tb_prt_cporg_unionn自定義業(yè)務(wù)種類自定義業(yè)務(wù)種類TB_CDE_CPBUSNTYP_UNION自定義機構(gòu)CREATE MULTIS

17、ET TABLE pims_pdata.tb_prt_cporg_union , ( CpOrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政機構(gòu)代碼郵政機構(gòu)代碼 NOT NULL, CpOrgNm VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政機構(gòu)名稱郵政機構(gòu)名稱 NOT NULL, CpOrgFullNm VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政機構(gòu)全稱郵政機構(gòu)全稱, SubCp

18、OrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下級郵政機構(gòu)代下級郵政機構(gòu)代碼碼, SubCpOrgLevCd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下級郵政機構(gòu)級下級郵政機構(gòu)級別代碼別代碼, CpOrgLevCd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政機構(gòu)級別代碼郵政機構(gòu)級別代碼, ParntCpOrgCd VARCHAR(8) CHARACTER SET LATIN NOT CASESPE

19、CIFIC TITLE 上級郵政機構(gòu)上級郵政機構(gòu)代碼代碼, SortNum DECIMAL(11,0) TITLE 順序號, unionmod INTEGER)PRIMARY INDEX PXI ( SubCpOrgCd )PARTITION BY unionmod ;自定義機構(gòu)/*機構(gòu)和自定義機構(gòu)，機構(gòu)和自定義機構(gòu)，unionmod=1是機構(gòu)，是機構(gòu)，unionmod=2是自定義機構(gòu)，是自定義機構(gòu)，unionmod=3是自定義機構(gòu)下轉(zhuǎn)是自定義機構(gòu)下轉(zhuǎn)*/ 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,) )#) ) )自定義業(yè)務(wù)種類CREATE MULTISET TABLE pims_pdata.TB_CDE_CPBUSNTYP_UNION , ( CpBusnDivCd CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政業(yè)務(wù)專業(yè)代碼郵政業(yè)務(wù)專業(yè)代碼 NOT NULL, CpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECI

22、FIC TITLE 郵政業(yè)務(wù)種類代郵政業(yè)務(wù)種類代碼碼 NOT NULL, CpBusnTypDesc VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 郵政業(yè)務(wù)種郵政業(yè)務(wù)種類描述類描述, SubCpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 下級郵政業(yè)下級郵政業(yè)務(wù)種類代碼務(wù)種類代碼, StrtDt DATE FORMAT YYYYMMDD TITLE 起始日期, EndDt DATE FORMAT YYYYMMDD TITLE 結(jié)束日期, CpBusn

23、TypLev INTEGER, ParntCpBusnTypCd VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 上級郵政上級郵政業(yè)務(wù)種類代碼業(yè)務(wù)種類代碼, SortNum DECIMAL(10,0) TITLE 排序序號, unionmod INTEGER)PRIMARY INDEX PXI ( CpBusnDivCd ,SubCpBusnTypCd )PARTITION BY unionmod ;自定義業(yè)務(wù)種類/*業(yè)務(wù)種類和自定義業(yè)務(wù)種類，業(yè)務(wù)種類和自定義業(yè)務(wù)種類，unionmod=1是業(yè)務(wù)種類，是業(yè)務(wù)種類，unionmod=2是自定義業(yè)務(wù)種類是自定義業(yè)務(wù)種類，unionmod=3是自定義業(yè)務(wù)種類下轉(zhuǎn)是自定義業(yè)務(wù)種類下轉(zhuǎn)*/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