查看: 2840|回复: 12

sql优化请教一下!

[复制链接]
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-10-28 15:22 | 显示全部楼层 |阅读模式
sql优化请教一下!

此语句一旦执行cpu长时间100%

17603 oracle    25   0 11.1g 1.8g 1.8g R 100.1  7.9  37:57.77 oracle                                                                        
19358 oracle    15   0 12872 1320  824 R  0.3  0.0   0:04.06 top                                                                           
    1 root      15   0 10348  680  576 S  0.0  0.0   1:40.24 init   

语句:
/* Formatted on 2010/10/28 14:41 (Formatter Plus v4.8.8) */
UPDATE yuff_cdma_3g_new a
   SET a.net_08 =
          (SELECT   b.app_3g_net_down_kbyte
                  + b.app_3g_net_up_kbyte
                  + b.net_3g_down_kbyte
                  + b.net_3g_up_kbyte
             FROM net_asset_ds_mon_e b
            WHERE a.asset_row_id = b.asset_row_id AND b.bil_month = '201008')

问题主要是sub query中select中的+号

这类如何优化呢?


表 yuff_cdma_3g_new 索引健全,表net_asset_ds_mon_e列 asset_row_id上有索引,并且在 bil_month上也有local分区索引。

yuff_cdma_3g_new 上有7729条记录
net_asset_ds_mon_e上有8113986条记录
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-10-28 15:27 | 显示全部楼层
用meger或update inline试试,列下计划看看

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-10-28 15:29 | 显示全部楼层
哎!请教一下具体你怎么改的?

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2010-10-28 15:41 | 显示全部楼层


由于表net_asset_ds_mon_e的数据量很大, 你直接用UPDATE效率肯定不好, 另外你的UPDATE语句少了where子句, 语句有问题!

select asset_row_id,
             app_3g_net_down_kbyte + app_3g_net_up_kbyte + net_3g_down_kbyte + net_3g_up_kbyte  sum_col
        from net_asset_ds_mon_e bil_month = '201008';

入上的语句效率如何?

试一下如下的语句:

merge into yuff_cdma_3g_new a
using(select asset_row_id,
             app_3g_net_down_kbyte + app_3g_net_up_kbyte + net_3g_down_kbyte + net_3g_up_kbyte  sum_col
        from net_asset_ds_mon_e bil_month = '201008') b
on(a.asset_row_id = b.asset_row_id)
when matched then
     update a.net_08 = b.sum_col


使用道具 举报

回复
论坛徽章:
47
2011新春纪念徽章
日期:2011-01-04 10:24:02奥迪
日期:2013-11-09 23:09:27保时捷
日期:2013-10-15 20:14:48阿斯顿马丁
日期:2013-10-12 09:11:59三菱
日期:2013-09-14 16:45:56雪铁龙
日期:2013-08-21 12:50:25马自达
日期:2013-08-14 12:51:35ITPUB社区千里马徽章
日期:2013-06-09 10:15:34蓝锆石
日期:2013-04-12 00:10:42劳斯莱斯
日期:2013-11-09 23:09:27
发表于 2010-10-28 15:43 | 显示全部楼层
SELECT   b.app_3g_net_down_kbyte
                  + b.app_3g_net_up_kbyte
                  + b.net_3g_down_kbyte
                  + b.net_3g_up_kbyte
             FROM net_asset_ds_mon_e b,yuff_cdma_3g_new a
            WHERE a.asset_row_id = b.asset_row_id AND b.bil_month = '201008'
光执行这个要多久?如果不是很快可以考虑先把这句的执行结果放入一个临时表(会比net_asset_ds_mon_e小很多),临时表需要加上asset_row_id 字段,然后再根据这个临时表更新yuff_cdma_3g_new表。

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-10-28 15:45 | 显示全部楼层
原帖由 yyp2009 于 2010-10-28 15:29 发表
哎!请教一下具体你怎么改的?

类似这种,试试,要保证被更新的每行唯一对应提供数据的那个表中的行,也就是不能更新多次,如果不能保证,可以
drop table a;
drop table b;
create table a(id number,name varchar2(10));
create table b(id number primary key,name varchar2(10),ext varchar2(10));

update (select a.name aname,b.name bname from a,b
             where a.id=b.id and b.ext='x')
set aname=bname;

--用了primary key,能保证唯一对应,不能的话,用
update  /*+ bypass_ujvc */  (select a.name aname,b.name bname from a,b
             where a.id=b.id and b.ext='x')
set aname=bname;

--merge的方法,也要保证上面的唯一对应,只能10g才有的功能
merge into a using b
on (a.id=b.id and b.ext='x')
when matched then
update set a.name = b.name;

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2010-10-28 15:53 | 显示全部楼层
原帖由 dingjun123 于 2010-10-28 15:45 发表

--用了primary key,能保证唯一对应,不能的话,用
update  /*+ bypass_ujvc */  (select a.name aname,b.name bname from a,b
             where a.id=b.id and b.ext='x')
set aname=bname;






我从来不用这种方法, 即使目前满足条件可以用, 但万一表的关系变了, 那就不得不改代码的。
使用“update inline-view”的条件限制太多, 尤其是对“参与连接的2个表的连接条件字段必须要保证唯一对应”!
另外使用“/*+ bypass_ujvc */  ”, 这个hints, optimizer并不能保证该一定会使用该hints, 因此会有风险, 还是用常规方法吧!




[ 本帖最后由 bell6248 于 2010-10-28 15:56 编辑 ]

使用道具 举报

回复
论坛徽章:
47
2011新春纪念徽章
日期:2011-01-04 10:24:02奥迪
日期:2013-11-09 23:09:27保时捷
日期:2013-10-15 20:14:48阿斯顿马丁
日期:2013-10-12 09:11:59三菱
日期:2013-09-14 16:45:56雪铁龙
日期:2013-08-21 12:50:25马自达
日期:2013-08-14 12:51:35ITPUB社区千里马徽章
日期:2013-06-09 10:15:34蓝锆石
日期:2013-04-12 00:10:42劳斯莱斯
日期:2013-11-09 23:09:27
发表于 2010-10-28 15:54 | 显示全部楼层
原帖由 dingjun123 于 2010-10-28 15:45 发表

类似这种,试试,要保证被更新的每行唯一对应提供数据的那个表中的行,也就是不能更新多次,如果不能保证,可以
drop table a;
drop table b;
create table a(id number,name varchar2(10));
create table b(id number primary key,name varchar2(10),ext varchar2(10));

update (select a.name aname,b.name bname from a,b
             where a.id=b.id and b.ext='x')
set aname=bname;

--用了primary key,能保证唯一对应,不能的话,用
update  /*+ bypass_ujvc */  (select a.name aname,b.name bname from a,b
             where a.id=b.id and b.ext='x')
set aname=bname;

--merge的方法,也要保证上面的唯一对应,只能10g才有的功能
merge into a using b
on (a.id=b.id and b.ext='x')
when matched then
update set a.name = b.name;



merge在9i也有,只是必须加上when not matched then  
在一般的业务环境中,应该是能保证唯一性的,多次更新毫无意义嘛~~

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-10-28 15:56 | 显示全部楼层
谢谢!人心指教!

select asset_row_id,
             app_3g_net_down_kbyte + app_3g_net_up_kbyte + net_3g_down_kbyte + net_3g_up_kbyte  sum_col
        from net_asset_ds_mon_e bil_month = '201008';

入上的语句效率如何?



SELECT STATEMENT, GOAL = ALL_ROWS                        6485        653577        18953733        1347340311        6395        DOUQQ        78                                        0                        0                                        SELECT STATEMENT        ALL_ROWS                                                2647        6485                                        2010-10-28 下午 04:08:18
PARTITION RANGE SINGLE                        6485        653577        18953733        1347340311        6395        DOUQQ        78        SEL$F5BB74E1        1        0                1                        1                                        PARTITION RANGE                SINGLE                        16        16        2647        1        "ASSET_ROW_ID"[VARCHAR2,30], "NET_3G_DOWN_KBYTE"[NUMBER,22], "NET_3G_UP_KBYTE"[NUMBER,22], "APP_3G_NET_DOWN_KBYTE"[NUMBER,22], "APP_3G_NET_UP_KBYTE"[NUMBER,22]                                2010-10-28 下午 04:08:18
  TABLE ACCESS FULL        xxxx        NET_ASSET_DS_MON_E        6485        653577        18953733        1347340311        6395        DOUQQ        78        SEL$F5BB74E1        2        1                2                "BIL_MONTH"='201008'        2        NET_ASSET_DS_MON_E@SEL$2        2                TABLE        TABLE ACCESS        ANALYZED        FULL                        16        16        2647        1        "ASSET_ROW_ID"[VARCHAR2,30], "NET_3G_DOWN_KBYTE"[NUMBER,22], "NET_3G_UP_KBYTE"[NUMBER,22], "APP_3G_NET_DOWN_KBYTE"[NUMBER,22], "APP_3G_NET_UP_KBYTE"[NUMBER,22]                                2010-10-28 下午 04:08:18

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-10-28 15:59 | 显示全部楼层
原帖由 zhoujiongfield 于 2010-10-28 15:54 发表



merge在9i也有,只是必须加上when not matched then  
在一般的业务环境中,应该是能保证唯一性的,多次更新毫无意义嘛~~

我知道啊,10g才有单个update功能,多次更新无意义,所以才要有约束的

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表