查看: 3470|回复: 17

请教如下sql的优化

[复制链接]
论坛徽章:
6
在线时间
日期:2007-05-20 04:01:01授权会员
日期:2007-06-01 08:14:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512012新春纪念徽章
日期:2012-01-04 11:51:22
发表于 2010-10-22 15:35 | 显示全部楼层 |阅读模式
oracle 8.17 + solaris 9

这条sql执行了很久都没有结果
select xdr_id as idrid,a.external_number as user_account,a.nas_ip, nas_type,
session_time,login_time,logout_time,
floor(c.node_id/10000) as nodeid,terminate_cause
from t_neva_idr_5_1 a, t_nas_info@hndb2 b ,t_product@hndb2 c
where a.external_number=c.external_number and
a.product_spec_id=c.product_spec_id and
a.nas_ip=b.nas_ip and
c.node_id not in (990000,870000) and
nas_type in (51,52,57) and
a.product_spec_id = 81;

以下是执行计划
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22166 Card=199421 By
          tes=35098096)

   1    0   HASH JOIN (Cost=22166 Card=199421 Bytes=35098096)
   2    1     REMOTE* (Cost=1 Card=98 Bytes=3920)                      HNDB2
   3    1     MERGE JOIN (Cost=22112 Card=199421 Bytes=27121256)
   4    3       SORT (JOIN) (Cost=5232 Card=131345 Bytes=5647835)
   5    4         REMOTE* (Cost=3631 Card=131345 Bytes=5647835)        HNDB2
   6    3       SORT (JOIN) (Cost=16881 Card=312326 Bytes=29046318)
   7    6         TABLE ACCESS (FULL) OF 'T_NEVA_IDR_5_1' (Cost=9472 C
          ard=312326 Bytes=29046318)



   2 SERIAL_FROM_REMOTE            SELECT "NAS_IP","NAS_TYPE" FROM "T_NAS_INFO"
                                    "B" WHERE "NAS_TYPE"=51 OR "NAS_TYP

   5 SERIAL_FROM_REMOTE            SELECT "PRODUCT_SPEC_ID","NODE_ID","EXTERNAL
                                   _NUMBER" FROM "T_PRODUCT" "C" WHERE

数量分布
SQL> select count(*) from t_neva_idr_5_1;

  COUNT(*)
----------
   1561626

Elapsed: 00:00:01.88
SQL> select count(*) from t_nas_info@hndb2;

  COUNT(*)
----------
       427

Elapsed: 00:00:00.01
SQL> select count(*) from t_product@hndb2;

  COUNT(*)
----------
   1058593

Elapsed: 00:00:00.83

索引分布
select table_name, index_name, column_name from user_ind_columns where table_name in('T_NAS_INFO','T_PRODUCT');
SQL>
TABLE_NAME           INDEX_NAME                     COLUMN_NAME
-------------------- ------------------------------ --------------------
T_NAS_INFO           PK_T_NAS_INFO                  NAS_ID
T_PRODUCT            IDX_P_EXTERNAL_NUMBER          EXTERNAL_NUMBER
T_PRODUCT            PK_T_PRODUCT                   PRODUCT_ID

t_neva_idr_5_1 暂时没有索引,因为这只是个测试库,正式生产库上的数量应该将近4000万
论坛徽章:
6
在线时间
日期:2007-05-20 04:01:01授权会员
日期:2007-06-01 08:14:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512012新春纪念徽章
日期:2012-01-04 11:51:22
 楼主| 发表于 2010-10-22 15:56 | 显示全部楼层
刚才又在 t_neva_idr_5_1 的 external_number 创建了一个索引,计划没有改变

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6112 Card=199441 Byt
          es=35101616)

   1    0   HASH JOIN (Cost=6112 Card=199441 Bytes=35101616)
   2    1     REMOTE* (Cost=1 Card=98 Bytes=3920)                      HNDB2
   3    1     MERGE JOIN (Cost=6058 Card=199441 Bytes=27123976)
   4    3       SORT (JOIN)
   5    4         TABLE ACCESS (FULL) OF 'T_NEVA_IDR_5_1' (Cost=826 Ca
          rd=312326 Bytes=29046318)

   6    3       SORT (JOIN) (Cost=1601 Card=131345 Bytes=5647835)
   7    6         REMOTE* (Cost=3631 Card=131345 Bytes=5647835)        HNDB2


   2 SERIAL_FROM_REMOTE            SELECT "NAS_IP","NAS_TYPE" FROM "T_NAS_INFO"
                                    "B" WHERE "NAS_TYPE"=51 OR "NAS_TYP

   7 SERIAL_FROM_REMOTE            SELECT "PRODUCT_SPEC_ID","NODE_ID","EXTERNAL
                                   _NUMBER" FROM "T_PRODUCT" "C" WHERE

使用道具 举报

回复
论坛徽章:
70
ITPUB元老
日期:2007-07-19 08:57:15乌索普
日期:2016-06-24 14:29:16沸羊羊
日期:2015-02-12 09:15:562014年世界杯参赛球队:喀麦隆
日期:2014-05-20 16:06:36马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11迷宫蛋
日期:2013-04-24 13:52:55茶鸡蛋
日期:2013-04-19 13:54:282013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-02-19 14:05:00
发表于 2010-10-22 16:06 | 显示全部楼层
select count(*)   from t_neva_idr_5_1 a, t_nas_info@hndb2 bwhere a.nas_ip = b.nas_ip  and nas_type in (51, 52, 57)
  
jieguo 发来看看!
  select count(*) from t_neva_idr_5_1 a where   a.product_spec_id = 81;
   select count(*) from t_product@hndb2 c where c.node_id not in (990000, 870000)
select count(*) from t_nas_info@hndb2 b where nas_type in (51, 52, 57)

使用道具 举报

回复
论坛徽章:
4
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29优秀写手
日期:2014-04-15 06:00:112015年新春福章
日期:2015-03-04 14:53:162015年新春福章
日期:2015-03-06 11:58:39
发表于 2010-10-22 16:09 | 显示全部楼层
关注,帮你顶

使用道具 举报

回复
论坛徽章:
70
ITPUB元老
日期:2007-07-19 08:57:15乌索普
日期:2016-06-24 14:29:16沸羊羊
日期:2015-02-12 09:15:562014年世界杯参赛球队:喀麦隆
日期:2014-05-20 16:06:36马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11迷宫蛋
日期:2013-04-24 13:52:55茶鸡蛋
日期:2013-04-19 13:54:282013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-02-19 14:05:00
发表于 2010-10-22 16:12 | 显示全部楼层
select /*+ DRIVING_SITE(c) LEADING(b a c)  */
       xdr_id as idrid,
       a.external_number as user_account,
       a.nas_ip,
       nas_type,
       session_time,
       login_time,
       logout_time,
       floor(c.node_id / 10000) as nodeid,
       terminate_cause
  from t_neva_idr_5_1 a, t_nas_info@hndb2 b, t_product@hndb2 c
where a.external_number = c.external_number
   and a.product_spec_id = c.product_spec_id
   and a.nas_ip = b.nas_ip
   and c.node_id not in (990000, 870000)
   and nas_type in (51, 52, 57)
   and a.product_spec_id = 81;

[ 本帖最后由 lorikyo 于 2010-10-22 16:17 编辑 ]

使用道具 举报

回复
论坛徽章:
1
数据库板块每日发贴之星
日期:2010-10-24 01:01:01
发表于 2010-10-22 16:20 | 显示全部楼层
路过学习

使用道具 举报

回复
论坛徽章:
6
在线时间
日期:2007-05-20 04:01:01授权会员
日期:2007-06-01 08:14:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512012新春纪念徽章
日期:2012-01-04 11:51:22
 楼主| 发表于 2010-10-22 16:24 | 显示全部楼层
== 马上发

使用道具 举报

回复
论坛徽章:
6
在线时间
日期:2007-05-20 04:01:01授权会员
日期:2007-06-01 08:14:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512012新春纪念徽章
日期:2012-01-04 11:51:22
 楼主| 发表于 2010-10-22 16:25 | 显示全部楼层
SQL> select count(*)   from t_neva_idr_5_1 a, t_nas_info@hndb2 b where a.nas_ip = b.nas_ip  and nas_type in (51, 52, 57);

  COUNT(*)
----------
    203061

Elapsed: 00:00:03.65
SQL> select count(*) from t_neva_idr_5_1 a where   a.product_spec_id = 81;

  COUNT(*)
----------
   1511349

Elapsed: 00:00:02.90
SQL> select count(*) from t_product@hndb2 c where c.node_id not in (990000, 870000);

  COUNT(*)
----------
   1058593

Elapsed: 00:00:02.82
SQL> select count(*) from t_nas_info@hndb2 b where nas_type in (51, 52, 57);

  COUNT(*)
----------
       116

Elapsed: 00:00:00.01

使用道具 举报

回复
论坛徽章:
6
在线时间
日期:2007-05-20 04:01:01授权会员
日期:2007-06-01 08:14:30会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512012新春纪念徽章
日期:2012-01-04 11:51:22
 楼主| 发表于 2010-10-22 16:28 | 显示全部楼层
原帖由 lorikyo 于 2010-10-22 16:12 发表
select /*+ DRIVING_SITE(c) LEADING(b a c)  */
       xdr_id as idrid,
       a.external_number as user_account,
       a.nas_ip,
       nas_type,
       session_time,
       login_time,
       logout_time,
       floor(c.node_id / 10000) as nodeid,
       terminate_cause
  from t_neva_idr_5_1 a, t_nas_info@hndb2 b, t_product@hndb2 c
where a.external_number = c.external_number
   and a.product_spec_id = c.product_spec_id
   and a.nas_ip = b.nas_ip
   and c.node_id not in (990000, 870000)
   and nas_type in (51, 52, 57)
   and a.product_spec_id = 81;



执行计划如下(执行了6分钟左右,结果暂时没有出来):
----------------------------------------------------------
   0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=5156 Card=1
          99441 Bytes=52253542)

   1    0   HASH JOIN (Cost=5156 Card=199441 Bytes=52253542)
   2    1     TABLE ACCESS (FULL) OF 'T_NAS_INFO' (Cost=1 Card=98 Byte NEVA2DB
          s=1568)

   3    1     MERGE JOIN (Cost=5064 Card=199441 Bytes=49062486)
   4    3       SORT (JOIN)
   5    4         REMOTE* (Cost=9472 Card=312326 Bytes=70273350)       !
   6    3       SORT (JOIN) (Cost=607 Card=131345 Bytes=2758245)
   7    6         TABLE ACCESS (FULL) OF 'T_PRODUCT' (Cost=3631 Card=1 NEVA2DB
          31345 Bytes=2758245)



   5 SERIAL_FROM_REMOTE            SELECT "XDR_ID","PRODUCT_SPEC_ID","EXTERNAL_
                                   NUMBER","NAS_IP","SESSION_TIME","LOG

使用道具 举报

回复
论坛徽章:
51
ITPUB十周年纪念徽章
日期:2011-11-01 16:25:22铁扇公主
日期:2012-02-21 15:03:13最佳人气徽章
日期:2012-03-13 17:39:18ITPUB季度 技术新星
日期:2012-05-22 15:10:11ITPUB 11周年纪念徽章
日期:2012-10-09 18:13:332013年新春福章
日期:2013-02-25 14:51:24ITPUB社区12周年站庆徽章
日期:2013-08-12 09:34:36itpub13周年纪念徽章
日期:2014-09-28 10:55:55
发表于 2010-10-22 16:30 | 显示全部楼层
a.product_spec_id = 81
这个字段没索引说不过去吧

使用道具 举报

回复

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

本版积分规则 发表回复

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