查看: 6437|回复: 32

一条SQL的优化

[复制链接]
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
发表于 2010-6-21 14:38 | 显示全部楼层 |阅读模式
1、SQL
SELECT
CUSTOMER_CODE, SUM(AMOUNT)
  FROM BILL_LIST A
WHERE CHARGE_ITEM IN (SELECT CHARGE_ITEM
          FROM CHARGE_ITEM_DEF B
         WHERE B.CHARGE_GROUP = 2
           AND B.CHARGE_ITEM_REF < 50)
   AND ACCT_MONTH = 201006
GROUP BY CUSTOMER_CODE;

执行计划:

执行计划
----------------------------------------------------------
Plan hash value: 1930791010

----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | Inst   |IN-O
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   130 |     3  (34)| 00:00:01 |       |       |        |      |
|   1 |  HASH GROUP BY           |                 |     1 |   130 |     3  (34)| 00:00:01 |       |       |        |      |
|   2 |   NESTED LOOPS           |                 |     1 |   130 |     2   (0)| 00:00:01 |       |       |        |      |
|   3 |    PARTITION RANGE SINGLE|                 |     1 |    94 |     2   (0)| 00:00:01 |    24 |    24 |        |      |
|*  4 |     TABLE ACCESS FULL    | BILL_LIST       |     1 |    94 |     2   (0)| 00:00:01 |    24 |    24 |        |      |
|   5 |    REMOTE                | CHARGE_ITEM_DEF |     1 |    36 |     0   (0)| 00:00:01 |       |       | LINK_~ | R->S |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ACCT_MONTH"=201006)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "CHARGE_ITEM","CHARGE_GROUP","CHARGE_ITEM_REF" FROM "BBOSS"."CHARGE_ITEM_DEF" "B" WHER
       "CHARGE_GROUP"=2 AND TO_NUMBER("CHARGE_ITEM_REF")<50 AND :1="CHARGE_ITEM" (accessing 'LINK_BL



注:
1)CHARGE_ITEM_DEF 该表是同义词,在另外一个数据库上。
2)该语句执行30分钟不出结果
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-6-21 14:40 | 显示全部楼层
另外一种实现方法:

1)不使用同义词,首先将那张表同步到该数据库上

CREATE TABLE tmp_CHARGE_ITEM_DEF
AS
SELECT * FROM CHARGE_ITEM_DEF;

2) 用本地的表替换那个同义词,SQL执行20s出结果

SELECT
CUSTOMER_CODE, SUM(AMOUNT)
  FROM BILL_LIST A
WHERE CHARGE_ITEM IN (SELECT CHARGE_ITEM
          FROM tmp_CHARGE_ITEM_DEF B
         WHERE B.CHARGE_GROUP = 2
           AND B.CHARGE_ITEM_REF < 50)
   AND ACCT_MONTH = 201006
GROUP BY CUSTOMER_CODE;

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-6-21 14:42 | 显示全部楼层
子查询的结果只有6条数据:
SELECT CHARGE_ITEM
          FROM tmp_CHARGE_ITEM_DEF B
         WHERE B.CHARGE_GROUP = 2
           AND B.CHARGE_ITEM_REF < 50



不知道为什么会差这么多?

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-6-21 15:22 | 显示全部楼层
BILL_LIST评估出来Card是1?统计信息准确么?

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-6-21 16:10 | 显示全部楼层
统计信息准;
数据库版本时oracle 10g 开启了自动收集。

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-6-21 16:27 | 显示全部楼层
select count(*) from bill_list where ("ACCT_MONTH"=201006)
返回多少行,统计信息肯定有问题

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-6-21 16:41 | 显示全部楼层
原帖由 棉花糖ONE 于 2010-6-21 16:27 发表
select count(*) from bill_list where ("ACCT_MONTH"=201006)
返回多少行,统计信息肯定有问题



to 棉花糖ONE,sundog315:

2位老大,从哪里可以看出统计信息不对呀? 请教下,呵呵。

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2010-6-21 16:57 | 显示全部楼层
原帖由 MicroJoey 于 2010-6-21 16:41 发表



to 棉花糖ONE,sundog315:

2位老大,从哪里可以看出统计信息不对呀? 请教下,呵呵。


显示的执行计划有问题,这个只真的是1?

|*  4 |     TABLE ACCESS FULL    | BILL_LIST       |    1 |    94 |     2   (0)| 00:00:01 |    24 |    24 |        |      |

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-6-21 16:57 | 显示全部楼层
执行计划+执行时间,执行计划上看评估成0行了, 这个分区是不是新加的,load了数据后没分析吧

[ 本帖最后由 棉花糖ONE 于 2010-6-21 16:58 编辑 ]

使用道具 举报

回复
论坛徽章:
14
季节之章:冬
日期:2010-01-04 13:39:56季节之章:春
日期:2010-03-22 16:42:29ITPUB知识分享者
日期:2010-06-28 10:02:43季节之章:夏
日期:2010-07-16 09:20:442010世博会纪念徽章
日期:2010-07-22 11:35:022010世博会纪念徽章
日期:2010-08-09 10:41:19ITPUB季度 技术新星
日期:2010-08-31 10:47:25季节之章:秋
日期:2010-12-10 16:23:30ITPUB官方微博粉丝徽章
日期:2011-07-20 17:06:48
 楼主| 发表于 2010-6-21 17:01 | 显示全部楼层
统计信息确实不对:
1)SELECT  COUNT(1) FROM BILL_LIST a;--72190795

2)
SELECT table_name,num_rows, blocks, empty_blocks, avg_space, avg_row_len FROM User_Tables a
WHERE a.table_name='BILL_LIST';
           TABLE_NAME        NUM_ROWS        BLOCKS        EMPTY_BLOCKS        AVG_SPACE        AVG_ROW_LEN
                BILL_LIST                      58018353                      1256494                           0        0                           152

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

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