查看: 8434|回复: 39

各位老大,帮忙啊 优化大数据量sql

[复制链接]
论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:43:33ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2014-03-22 06:00:12
发表于 2012-1-18 14:27 | 显示全部楼层 |阅读模式
本帖最后由 lingyuhihi 于 2012-1-19 16:22 编辑

t_ply_base表数据2400多万,t_clm_main表是800多万,T_PLY_VHL表是1600多万,主要是这几个个表大,其他的都很小
insert的结果是50多万条

三个表的c_ply_no字段都是索引

t_fin_clmdue 800多万,c_clm_no有索引

insert into adtPaid
        (c_ply_no,
         c_dpt_cde,
         c_dpt_cde2,
         c_dpt_cde3,
         c_dpt_cde4,
         C_BSNS_TYP,
         c_kind_no,
         c_prod_no,
         c_nme_cn,
         c_dpt_attr,
         C_INSRNT_CNM,
         T_INSRNC_BGN_TM,
         T_INSRNC_END_TM,
         N_AMT,
         N_PRM,
         C_SNAP_FLAG,
         C_CHK_CDE,
         C_CHK_REC,
         C_LCN_NO,
         N_SET_NUM,
         N_TON_NUM,
         C_NEW_FLAG,
         C_USE_ATR,
         C_FST_REG_DTE,
         C_CLM_NO,
         N_CLM_TMS,
         report_id,
         T_CLM_RGST_TM, --立案日期
         T_CLS_TM, --结案日期
         C_CLM_MAINSTATUS, --赔案主状态
         N_SUM_ESTMT_AMT, --估损总金额
         N_SUM_PRE_AMT, --预赔总金额
         N_SUM_CLM_AMT, --赔款金额
         C_PAY_MRK,
         C_CHASE_MRK,
         C_LTGT_MRK, --诉讼标志      
         C_RPT_NME, --报案人姓名
         C_RPTMAN_TEL, --报案人电话
         T_RPT_TM, --报案登记日期  
         --C_FIRST_SPOT,--是否第一现场查勘
         T_ACCDNT_TM, --出险时间
         C_ACCDNT_CAUS_CDE, --出险原因
         c_rcpt_no,
         c_clnt_mrk,
         T_bal_tm,
         T_clm_TM,
         T_check_tm,
         n_pay_amt,
         n_paid_AMT,
         c_clm_typ,
         c_accnt_flag,
         c_bala_mrk,
         c_accounts_name,
         c_customer_accounts,
         C_Bank_accounts,
         c_identity_no,
         C_Mobile_Phone,
         c_rp_type,
         c_voucher_no,
         c_voucher_no1)
        select ply.c_ply_no,
               ply.c_dpt_cde,
               (SELECT C_DPT_ABR
                  FROM t_department
                 where c_dpt_cde = substr(ply.C_DPT_CDE, 1, 2)), --二级机构
               (SELECT C_DPT_ABR
                  FROM t_department
                 where c_dpt_cde = substr(ply.C_DPT_CDE, 1, 4)), --三级机构
               (SELECT C_DPT_ABR
                  FROM t_department
                 where c_dpt_cde = substr(ply.C_DPT_CDE, 1, 6)), --四级机构
               (select c_cnm
                  from abaudit_tmp.t_comm_code
                 where c_par_cde = '190'
                   and c_cde in ('19001', '19002', '19007')
                   and c_cde = ply.C_BSNS_TYP), --渠道大类
               (select c_nme_cn
                  from abaudit_tmp.t_prd_kind
                 where c_kind_no =
                       (select c_kind_no
                          from abaudit_tmp.T_PRD_PROD
                         where c_prod_no = ply.C_PROD_NO)), --险类
               ply.C_PROD_NO, --险种代码
               (select c_nme_cn
                  from abaudit_tmp.T_PRD_PROD
                 where c_prod_no = ply.C_PROD_NO), --险种
               (select c_cnm
                  from abaudit_tmp.t_comm_code
                 where c_par_cde = '014'
                   and c_cde = ply.c_dpt_attr), --部门属性
               ply.C_INSRNT_CNM, --被保险人名称
               ply.T_INSRNC_BGN_TM,
               ply.T_INSRNC_END_TM, --保险起止期
               ply.N_AMT,
               ply.N_PRM,
               VHL.C_SNAP_FLAG,
               VHL.C_CHK_CDE,
               VHL.C_CHK_REC,
               VHL.C_LCN_NO,
               VHL.N_SET_NUM,
               VHL.N_TON_NUM,
               VHL.C_NEW_FLAG,
               VHL.C_USE_ATR,
               VHL.C_FST_REG_DTE,
               CLAIM.C_CLM_NO,
               CLAIM.N_CLM_TMS,
               (select report_id
                  from abaudit_tmp.T_GC_CLAIM
                 where accepted_claim_no = CLAIM.C_CLM_NO
                    or third_claim_no = CLAIM.C_CLM_NO), --报案号
               CLAIM.T_CLM_RGST_TM, --立案日期
               CLAIM.T_CLS_TM, --结案日期
               decode(trim(CLAIM.C_CLM_MAINSTATUS),
                      '0',
                      '报案',
                      '1',
                      '派工',
                      '2',
                      '查勘',
                      '3',
                      '申请核损',
                      '4',
                      '核损',
                      '5',
                      '立案',
                      '6',
                      '理算',
                      '7',
                      '申请核赔',
                      '8',
                      '核赔',
                      '9',
                      '结案',
                      'A',
                      '无资料报案'), --赔案主状态
               CLAIM.N_SUM_ESTMT_AMT, --估损总金额
               CLAIM.N_SUM_PRE_AMT, --预赔总金额
               CLAIM.N_SUM_CLM_AMT, --赔款金额
               decode(trim(CLAIM.C_PAY_MRK),
                      '1',
                      '赔付',
                      '2',
                      '拒赔',
                      '3',
                      '通融赔款'), --赔付标志
               decode(trim(CLAIM.C_CHASE_MRK),
                      '0',
                      '正常',
                      '',
                      '正常',
                      '1',
                      '追偿'), --追偿标志
               decode(trim(CLAIM.C_LTGT_MRK),
                      '0',
                      '正常',
                      '',
                      '正常',
                      '1',
                      '诉讼'), --诉讼标志      
               RGST.C_RPT_NME, --报案人姓名
               RGST.C_RPTMAN_TEL, --报案人电话
               RGST.T_RPT_TM, --报案登记日期  
               --decode(trim(select max(C_FIRST_SPOT) from T_CLM_SRVYREG srv where srv.c_clm_no = claim.c_clm_no and srv.n_clm_tms = claim.n_clm_tms and srv.N_SRVY_TMS = 1),'1','是','0','否','','否'),--是否第一现场查勘
               (select T_ACCDNT_TM
                  from abaudit_tmp.T_CLM_SRVYREG srv
                 where srv.c_clm_no = claim.c_clm_no
                   and srv.n_clm_tms = claim.n_clm_tms
                   and srv.N_SRVY_TMS = 1), --出险时间
               (SELECT c_accdnt_caus_cnm
                  FROM abaudit_tmp.t_accident_rsn
                 where C_KIND_NO =
                       (select c_kind_no
                          from abaudit_tmp.T_PRD_PROD
                         where c_prod_no = ply.C_PROD_NO)
                   and c_accdnt_caus_cde =
                       (select C_ACCDNT_CAUS_CDE
                          from abaudit_tmp.T_CLM_SRVYREG srv
                         where srv.c_clm_no = claim.c_clm_no
                           and srv.n_clm_tms = claim.n_clm_tms
                           and srv.N_SRVY_TMS = 1)), --出险原因
               clmdue.c_rcpt_no,
               decode(trim(clmdue.c_clnt_mrk), '0', '法人', '1', '个人'), --客户类型
               clmdue.T_bal_tm,
               clmdue.T_clm_TM,
               clmdue.T_check_tm,
               clmdue.n_pay_amt,
               clmdue.n_paid_amt,
               clmdue.c_clm_typ,
               decode(trim(CLMDUE.c_accnt_flag),
                      '00',
                      '未收付-未确认',
                      '01',
                      '未收付-已确认',
                      '10',
                      '已收付-未确认',
                      '11',
                      '已收付-已确认'),
               decode(trim(CLMDUE.c_bala_mrk),
                      '1',
                      '需审核',
                      '2',
                      '分公司审核',
                      '3',
                      '总公司审核',
                      '4',
                      '审核',
                      '5',
                      '导出',
                      '6',
                      '付款成功',
                      '7',
                      '付款失败'),
               CUSTOMER.c_accounts_name,
               CUSTOMER.c_customer_accounts,
               CUSTOMER.C_Bank_accounts,
               CUSTOMER.c_identity_no,
               CUSTOMER.C_Mobile_Phone,
               (select c_rp_name
                  from abaudit_tmp.t_fin_rptype
                 where t_fin_rptype.c_rp_type in
                       (select c_rp_type
                          from abaudit_tmp.t_fin_dcr dcr
                         where dcr.c_rcpt_no = clmdue.c_rcpt_no
                           and rownum = 1)),
               --dcr.c_rp_type,
               (select c_voucher_no
                  from abaudit_tmp.t_fin_dcr
                 where c_rcpt_no = clmdue.c_rcpt_no
                   and rownum = 1),
               --madcr.c_voucher_no      
               (select c_voucher_no
                  from abaudit_tmp.t_fin_madcr
                 where c_rcpt_no = clmdue.c_rcpt_no
                   and rownum = 1)
          from abaudit_tmp.t_clm_main CLAIM
          left join abaudit_tmp.t_fin_clmdue clmdue on claim.c_clm_no = clmdue.c_clm_no
                                       and CLAIM.n_clm_tms =
                                           clmdue.n_clm_tms
          left join abaudit_tmp.t_fin_rpclmcustomer CUSTOMER on CUSTOMER.c_rcpt_no =
                                                    clmdue.c_rcpt_no
          LEFT JOIN abaudit_tmp.t_clm_rgst RGST on rgst.c_clm_no = claim.c_clm_no
                                   and rgst.n_clm_tms = claim.n_clm_tms
        --left join t_fin_dcr dcr on clmdue.c_rcpt_no = dcr.c_rcpt_no left join t_fin_madcr madcr on clmdue.c_rcpt_no = madcr.c_rcpt_no
          left join abaudit_tmp.t_ply_base ply on ply.c_ply_no = claim.c_ply_no
          left join abaudit_tmp.t_ply_vhl VHL on vhl.c_ply_no = ply.c_ply_no
         where claim.T_CRT_TM > to_date('2011-07-10', 'YYYY-MM-DD') -- AND claim.T_CRT_TM<=to_date('2011-06-10','YYYY-MM-DD')
           and claim.C_CLM_MAINSTATUS = '9';
求优化方案?
数据库server配置4C 64G
这个sql的执行计划看附件 explant .txt (2.82 KB, 下载次数: 89)
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2012-1-18 14:34 | 显示全部楼层
这样的SQL 没法优化 看你 执行计划已经用了并行了

使用道具 举报

回复
论坛徽章:
7
ITPUB学员
日期:2011-04-08 09:44:05ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04紫蛋头
日期:2011-12-26 14:20:322012新春纪念徽章
日期:2012-01-04 11:53:54奥运会纪念徽章:花样游泳
日期:2012-06-25 12:17:162013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-25 06:00:13
发表于 2012-1-18 14:53 | 显示全部楼层
请问你 adtPaid 是分区表吗?

使用道具 举报

回复
论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:43:33ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2014-03-22 06:00:12
 楼主| 发表于 2012-1-18 15:01 | 显示全部楼层
KAERTIGER 发表于 2012-1-18 14:53
请问你 adtPaid 是分区表吗?

no

使用道具 举报

回复
论坛徽章:
7
ITPUB学员
日期:2011-04-08 09:44:05ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04紫蛋头
日期:2011-12-26 14:20:322012新春纪念徽章
日期:2012-01-04 11:53:54奥运会纪念徽章:花样游泳
日期:2012-06-25 12:17:162013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-25 06:00:13
发表于 2012-1-18 15:09 | 显示全部楼层
这SQL 多久跑一次?什么时间跑?
说实话,看完这SQL都向睡觉的感觉。

使用道具 举报

回复
论坛徽章:
4
2011新春纪念徽章
日期:2011-02-18 11:43:33ITPUB十周年纪念徽章
日期:2011-11-01 16:25:222013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2014-03-22 06:00:12
 楼主| 发表于 2012-1-18 15:10 | 显示全部楼层
本帖最后由 lingyuhihi 于 2012-1-18 15:20 编辑
KAERTIGER 发表于 2012-1-18 15:09
这SQL 多久跑一次?什么时间跑?
说实话,看完这SQL都向睡觉的感觉。


一月一次,据传跑完要好几天,刚接手维护,好几天业务接受不了

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
发表于 2012-1-18 15:14 | 显示全部楼层
好几天,牛B

使用道具 举报

回复
论坛徽章:
289
蛋疼蛋
日期:2013-03-29 13:46:58优秀写手
日期:2013-12-24 06:00:12福特
日期:2014-02-17 17:30:59生肖徽章:兔
日期:2012-05-24 19:03:36SQL极客
日期:2013-12-09 14:13:35ITPUB季度 技术新星
日期:2014-02-24 11:00:06IT宝贝
日期:2014-08-27 10:32:17马上加薪
日期:2014-08-05 09:18:33SQL数据库编程大师
日期:2016-01-13 10:30:43玉石琵琶
日期:2014-03-04 16:46:07
发表于 2012-1-18 15:16 | 显示全部楼层
看到了BITMAP CONVERSION,觉得悬,先禁掉这个看看

使用道具 举报

回复
论坛徽章:
114
授权会员
日期:2005-10-30 17:05:332013年新春福章
日期:2013-02-25 14:51:24奔驰
日期:2013-08-01 21:18:36宝马
日期:2013-12-04 21:52:282014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14
发表于 2012-1-18 15:34 | 显示全部楼层
本帖最后由 花好月不圆 于 2012-1-18 15:35 编辑

claim.T_CRT_TM > to_date('2011-07-10', 'YYYY-MM-DD')   -- AND claim.T_CRT_TM<=to_date('2011-06-10','YYYY-MM-DD')
的条件下得到的数据大约是多少?

claim.C_CLM_MAINSTATUS = '9'; 限制条件下的数据大约多少?



使用道具 举报

回复
论坛徽章:
2
2012新春纪念徽章
日期:2012-01-04 11:53:292013年新春福章
日期:2013-02-25 14:51:24
发表于 2012-1-18 15:35 | 显示全部楼层
帮顶  好长啊啊啊啊~~~~

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表