查看: 3110|回复: 13

一条sql语句优化 ,请大家帮个忙

[复制链接]
论坛徽章:
27
会员2007贡献徽章
日期:2007-09-26 18:42:102011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:排球
日期:2011-03-03 12:19:332010广州亚运会纪念徽章:篮球
日期:2011-03-10 14:25:06ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15灰彻蛋
日期:2011-12-28 16:56:322012新春纪念徽章
日期:2012-01-04 11:50:44迷宫蛋
日期:2012-03-09 15:14:20蜘蛛蛋
日期:2012-03-26 09:46:32
发表于 2009-7-29 16:30 | 显示全部楼层 |阅读模式
数据分布:
select count(*) from  TMO_EQU_EVENT;
select count(*) from  TMO_EQU_EVENT where EVENT_STATUS = 3;
select count(*) from TMO_EQU_EVENT where TAG_NAME like 'MET%';
select count(*) from TMO_EQU_EVENT where EVENT_STATUS = 3 and TAG_NAME like 'MET%';
EVENT_STATUS  列位number型的
SQL> select count(*) from  TMO_EQU_EVENT;

  COUNT(*)
----------
     28681

SQL> select count(*) from  TMO_EQU_EVENT where EVENT_STATUS = 3;

  COUNT(*)
----------
     19497

SQL> select count(*) from TMO_EQU_EVENT where TAG_NAME like 'MET%';

  COUNT(*)
----------
      9140

SQL> select count(*) from TMO_EQU_EVENT where EVENT_STATUS = 3 and TAG_NAME like 'MET%';

  COUNT(*)
----------
         0
sql 执行计划

SQL> select tmoequeven0_.EVENT_ID       as EVENT1_63_,
  2                 tmoequeven0_.NODE_ID        as NODE2_63_,
  3                 tmoequeven0_.LINE_ID        as LINE3_63_,
  4                 tmoequeven0_.STATION_ID     as STATION4_63_,
  5                 tmoequeven0_.EQU_TYPE       as EQU5_63_,
  6                 tmoequeven0_.COMPONENT_TYPE as COMPONENT6_63_,
  7                 tmoequeven0_.TAG_NAME       as TAG7_63_,
  8                 tmoequeven0_.TAG_VALUE      as TAG8_63_,
  9                 tmoequeven0_.STATUS_LEVEL   as STATUS9_63_,
10                 tmoequeven0_.EVENT_TYPE     as EVENT10_63_,
11                 tmoequeven0_.EVT_DESC       as EVT11_63_,
12                 tmoequeven0_.OCCUR_TIME     as OCCUR12_63_,
13                 tmoequeven0_.EVENT_STATUS   as EVENT13_63_,
14                 tmoequeven0_.REMARK         as REMARK63_
15            from TMO_EQU_EVENT tmoequeven0_
16           where tmoequeven0_.LINE_ID = 3
17             and tmoequeven0_.EVENT_STATUS = 3
18             and tmoequeven0_.TAG_NAME like 'MET%'
19             and rownum <= 500;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1874654760

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

| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time
   |

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

|   0 | SELECT STATEMENT   |               |   500 | 41000 |    19   (0)| 00:00:
01 |

|*  1 |  COUNT STOPKEY     |               |       |       |            |
   |

|*  2 |   TABLE ACCESS FULL| TMO_EQU_EVENT |   500 | 41000 |    19   (0)| 00:00:
01 |

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


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

   1 - filter(ROWNUM<=500)
   2 - filter("TMOEQUEVEN0_"."TAG_NAME" LIKE 'MET%' AND
              "TMOEQUEVEN0_"."EVENT_STATUS"=3 AND "TMOEQUEVEN0_"."LINE_ID"=3)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        809  consistent gets
          0  physical reads
          0  redo size
       1059  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


建立索引
create index I_TMO_EQU_E_EVENT_STATUS on TMO_EQU_EVENT (EVENT_STATUS, TAG_NAME);
execute  dbms_stats.gather_table_stats(ownname=>upper('test'),tabname=>upper('TMO_EQU_EVENT'),estimate_percent=>10,cascade=>true,METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');        

执行计划还是跟原来一样

我加了索引提示的执行计划:

SQL> select /*+index(tmoequeven0_ I_TMO_EQU_E_EVENT_STATUS)*/  tmoequeven0_.EVENT_ID       as EVENT1_63_,
  2                 tmoequeven0_.NODE_ID        as NODE2_63_,
  3                 tmoequeven0_.LINE_ID        as LINE3_63_,
  4                 tmoequeven0_.STATION_ID     as STATION4_63_,
  5                 tmoequeven0_.EQU_TYPE       as EQU5_63_,
  6                 tmoequeven0_.COMPONENT_TYPE as COMPONENT6_63_,
  7                 tmoequeven0_.TAG_NAME       as TAG7_63_,
  8                 tmoequeven0_.TAG_VALUE      as TAG8_63_,
  9                 tmoequeven0_.STATUS_LEVEL   as STATUS9_63_,
10                 tmoequeven0_.EVENT_TYPE     as EVENT10_63_,
11                 tmoequeven0_.EVT_DESC       as EVT11_63_,
12                 tmoequeven0_.OCCUR_TIME     as OCCUR12_63_,
13                 tmoequeven0_.EVENT_STATUS   as EVENT13_63_,
14                 tmoequeven0_.REMARK         as REMARK63_
15            from TMO_EQU_EVENT tmoequeven0_
16           where tmoequeven0_.LINE_ID = 3
17             and tmoequeven0_.EVENT_STATUS = 3
18             and tmoequeven0_.TAG_NAME like 'MET%'
19             and rownum <= 500;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3664368511

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

| Id  | Operation                    | Name                     | Rows  | Bytes
| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                          |   500 | 41000
|   337   (0)| 00:00:05 |

|*  1 |  COUNT STOPKEY               |                          |       |
|            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| TMO_EQU_EVENT            |   500 | 41000
|   337   (0)| 00:00:05 |

|*  3 |    INDEX RANGE SCAN          | I_TMO_EQU_E_EVENT_STATUS |  6157 |
|     3   (0)| 00:00:01 |

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


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

   1 - filter(ROWNUM<=500)
   2 - filter("TMOEQUEVEN0_"."LINE_ID"=3)
   3 - access("TMOEQUEVEN0_"."EVENT_STATUS"=3 AND "TMOEQUEVEN0_"."TAG_NAME" LIKE
'MET%')

       filter("TMOEQUEVEN0_"."TAG_NAME" LIKE 'MET%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1059  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
         
     
oracle 怎么就不用索引呢? 有什么办法吗?
论坛徽章:
27
会员2007贡献徽章
日期:2007-09-26 18:42:102011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:排球
日期:2011-03-03 12:19:332010广州亚运会纪念徽章:篮球
日期:2011-03-10 14:25:06ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15灰彻蛋
日期:2011-12-28 16:56:322012新春纪念徽章
日期:2012-01-04 11:50:44迷宫蛋
日期:2012-03-09 15:14:20蜘蛛蛋
日期:2012-03-26 09:46:32
 楼主| 发表于 2009-7-29 16:31 | 显示全部楼层
还需要什么内容,跟我说哦

使用道具 举报

回复
论坛徽章:
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
发表于 2009-7-29 16:35 | 显示全部楼层
“INDEX RANGE SCAN”, 这不是用了吗?

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
16
生肖徽章:猴
日期:2006-09-07 17:03:51沸羊羊
日期:2015-03-04 14:43:43比亚迪
日期:2013-08-16 16:21:192013年新春福章
日期:2013-02-25 14:51:242011新春纪念徽章
日期:2011-02-18 11:43:36生肖徽章2007版:虎
日期:2009-03-10 21:13:27生肖徽章2007版:鼠
日期:2008-01-02 17:35:53生肖徽章2007版:牛
日期:2008-01-02 17:35:53生肖徽章2007版:虎
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2008-01-02 17:35:53
发表于 2009-7-29 16:37 | 显示全部楼层
是啊,你的index不是用起来了吗?

使用道具 举报

回复
论坛徽章:
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
发表于 2009-7-29 16:38 | 显示全部楼层
select /*+ dynamic_sampling(4) */
                   tmoequeven0_.EVENT_ID       as EVENT1_63_,
                   tmoequeven0_.NODE_ID        as NODE2_63_,
                   tmoequeven0_.LINE_ID        as LINE3_63_,
                   tmoequeven0_.STATION_ID     as STATION4_63_,
                   tmoequeven0_.EQU_TYPE       as EQU5_63_,
                  tmoequeven0_.COMPONENT_TYPE as COMPONENT6_63_,
                   tmoequeven0_.TAG_NAME       as TAG7_63_,
                   tmoequeven0_.TAG_VALUE      as TAG8_63_,
                   tmoequeven0_.STATUS_LEVEL   as STATUS9_63_,
                 tmoequeven0_.EVENT_TYPE     as EVENT10_63_,
                 tmoequeven0_.EVT_DESC       as EVT11_63_,
                 tmoequeven0_.OCCUR_TIME     as OCCUR12_63_,
                 tmoequeven0_.EVENT_STATUS   as EVENT13_63_,
                 tmoequeven0_.REMARK         as REMARK63_
            from TMO_EQU_EVENT tmoequeven0_
           where tmoequeven0_.LINE_ID = 3
             and tmoequeven0_.EVENT_STATUS = 3
             and tmoequeven0_.TAG_NAME like 'MET%'
             and rownum <= 500;

使用道具 举报

回复
论坛徽章:
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
发表于 2009-7-29 16:39 | 显示全部楼层
解决方法:

1.hint走索引
2.dynamic_sampling level 4以上
3.升级到11g,组合列统计信息

使用道具 举报

回复
论坛徽章:
27
会员2007贡献徽章
日期:2007-09-26 18:42:102011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:排球
日期:2011-03-03 12:19:332010广州亚运会纪念徽章:篮球
日期:2011-03-10 14:25:06ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15灰彻蛋
日期:2011-12-28 16:56:322012新春纪念徽章
日期:2012-01-04 11:50:44迷宫蛋
日期:2012-03-09 15:14:20蜘蛛蛋
日期:2012-03-26 09:46:32
 楼主| 发表于 2009-7-29 16:45 | 显示全部楼层
谢谢棉花糖
解决方法:

1.hint走索引
2.dynamic_sampling level 4以上
3.升级到11g,组合列统计信息

1.在考虑中,能不用就不用的
2. 这个也是要加提示的  
3.这个还是放弃吧,涉及面太广,哈哈 ,还是谢谢了

还真是难以取舍,商量一下

使用道具 举报

回复
论坛徽章:
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
发表于 2009-7-29 17:14 | 显示全部楼层

看一下这个语句的执行计划?

select * from
(select tmoequeven0_.EVENT_ID  as EVENT1_63_,
        tmoequeven0_.NODE_ID        as NODE2_63_,
        tmoequeven0_.LINE_ID        as LINE3_63_,
        tmoequeven0_.STATION_ID     as STATION4_63_,
        tmoequeven0_.EQU_TYPE       as EQU5_63_,
        tmoequeven0_.COMPONENT_TYPE as COMPONENT6_63_,
        tmoequeven0_.TAG_NAME       as TAG7_63_,
        tmoequeven0_.TAG_VALUE      as TAG8_63_,
        tmoequeven0_.STATUS_LEVEL   as STATUS9_63_,
        tmoequeven0_.EVENT_TYPE     as EVENT10_63_,
        tmoequeven0_.EVT_DESC       as EVT11_63_,
        tmoequeven0_.OCCUR_TIME     as OCCUR12_63_,
        tmoequeven0_.EVENT_STATUS   as EVENT13_63_,
        tmoequeven0_.REMARK         as REMARK63_
from TMO_EQU_EVENT tmoequeven0_
where tmoequeven0_.LINE_ID = 3
   and tmoequeven0_.EVENT_STATUS = 3
   and tmoequeven0_.TAG_NAME like 'MET%')
where rownum <= 500;

使用道具 举报

回复
论坛徽章:
27
会员2007贡献徽章
日期:2007-09-26 18:42:102011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:排球
日期:2011-03-03 12:19:332010广州亚运会纪念徽章:篮球
日期:2011-03-10 14:25:06ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15灰彻蛋
日期:2011-12-28 16:56:322012新春纪念徽章
日期:2012-01-04 11:50:44迷宫蛋
日期:2012-03-09 15:14:20蜘蛛蛋
日期:2012-03-26 09:46:32
 楼主| 发表于 2009-7-29 17:22 | 显示全部楼层
to: ZALBB
sql 语句 原来是这样的
执行计划是一样的

使用道具 举报

回复
论坛徽章:
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
发表于 2009-7-29 17:31 | 显示全部楼层
原帖由 carcase 于 2009-7-29 17:22 发表
to: ZALBB
sql 语句 原来是这样的
执行计划是一样的


因为ROWNUM<=500的条件和上面的条件并排放一起,优化器执行时,先取500记录,再在这500记录里寻找符合另外条件的记录。
因此,你看到的执行计划是,FULL TABLE SCAN    500.

这个写法,应该不符合你的原意。我猜测,你的原意应该是,找出符合条件的,不超过500的记录。因此,我那写法才是正确的。

使用道具 举报

回复

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

本版积分规则 发表回复

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