1/5用友股份-LE支持服务业务本部技术方案--《SQL效率优化案例-直方图》建立日期：2014-01-20修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2014-00082/5文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间内部员工伙伴客户日期作者所属部门邮件地址版本2014-01-20雷鹏技术支持部V1.0版本领域模块备注3/5目录SQL效率优化案例-直方图.......................................................................................4一、系统环境............................................................................................................4二、问题描述............................................................................................................4三、解决方法............................................................................................................54/5SQL效率优化案例-直方图注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！一、系统环境ORACLE10.2.0.4二、问题描述sql执行缓慢：selectdistinctcmp_settlement.pk_settlement,cmp_settlement.pk_tradetype,cmp_settlement.busi_billdate,cmp_settlement.billcode,cmp_settlement.tsfromcmp_detail,cmp_settlementwherecmp_settlement.pk_settlement=cmp_detail.pk_settlementandcmp_detail.pk_corp='1383'andcmp_detail.pk_currtype='00010000000000000001'andcmp_detail.pk_account='0001A11000000001U384'andcmp_detail.dr=0andcmp_settlement.dr=0andcmp_settlement.pk_corp='1383'andcmp_settlement.settlestatusin(0,2)andcmp_settlement.isapplybill='N'andcmp_settlement.pk_ftsbillisnullandcmp_settlement.pk_tradetype<>'DR'andcmp_settlement.direction<>0andcmp_settlement.isbusieffect='Y'andcmp_settlement.def19isnullandcmp_settlement.busi_billdate>'2013-12-01';执行时间在100秒左右5/5三、解决方法Analyzetablecmp_detailcomputestatisticsforcolumnspk_accountsize254；结论：如果表比较大，其中的列倾斜度很高，查询时如果条件中有此列，很可能oracle执行计划不恰当，可以考虑在此列上建立直方图。
