报表优化交流_20110301要点详解.ppt

报表优化内部交流 Leiyry 北京华胜天成科技股份有限公司 2011年3月 报表优化原则 使用汇总表分区 避免left join 去除冗余代码 复杂视图物理化 增加中间表 汇总表字段冗余 使用汇总表分区 sel * from pims_pmart2.tb_fct_sum_det_m where statsmt=201101‘ sel * from pims_pmart2.tb_fct_sum_det_m where statsmt=cast(201101 as date format yyyymm) 使用汇总表分区 1) First, we lock a 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_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 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 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 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, 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 involved in processing the request. - The contents of Spool 1 are sent back to the us