查看: 5082|回复: 24

这个语句请帮忙优化看看

[复制链接]
论坛徽章:
15
数据库板块每日发贴之星
日期:2008-06-30 01:01:54奥运会纪念徽章:羽毛球
日期:2012-06-26 15:21:24ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26数据库板块每日发贴之星
日期:2011-07-15 01:01:01ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512010年世界杯参赛球队:加纳
日期:2010-07-27 08:59:132010年世界杯参赛球队:智利
日期:2010-07-14 16:06:302010年世界杯参赛球队:斯洛伐克
日期:2010-07-10 02:35:492010年世界杯参赛球队:英格兰
日期:2010-07-09 18:54:212010年世界杯参赛球队:瑞士
日期:2010-01-22 13:33:24
发表于 2010-3-26 11:04 | 显示全部楼层 |阅读模式
select distinct(freevalueid) from gl_freevalue a where (checktype='00010000000000000073'
and ( checkvalue in (select pk_cubasdoc from bd_cumandoc where (pk_corp = '1074' or pk_corp = '0001') ) or checkvalue is null) )
group by freevalueid having count(*)>=1




gl_freevalue 36万条 ,上面有3个索引


1        I_GL_FREEVALUE        FREEVALUEID
2        I_GL_FREEVALUE        CHECKTYPE
3        I_GL_FREEVALUE2        CHECKVALUE
4        I_GL_FREEVALUE2        CHECKTYPE
5        I_GL_FREEVALUE2        CHECKCOUNT
6        PK_GL_FREEVALUE        PK_FREEVALUE


bd_cumandoc 60万条,上面有13个索引





1        I_BD_CUMANDOC_1        PK_CORP
2        I_BD_CUMANDOC_1        PK_CUBASDOC
3        I_BD_CUMANDOC_1        CUSTFLAG
4        I_BD_CUMANDOC_1        PK_CUBASDOC
5        I_BD_CUMANDOC_1        PK_CORP
6        I_BD_CUMANDOC_1        CUSTFLAG
7        I_BD_CUMANDOC_1        PK_CUBASDOC
8        I_BD_CUMANDOC_1        CUSTFLAG
9        I_BD_CUMANDOC_1        PK_CORP
10        I_BD_CUMANDOC_10        PK_PAYTERM
11        I_BD_CUMANDOC_10        PK_PAYTERM
12        I_BD_CUMANDOC_10        PK_PAYTERM
13        I_BD_CUMANDOC_11        PK_SENDTYPE
14        I_BD_CUMANDOC_11        PK_SENDTYPE
15        I_BD_CUMANDOC_11        PK_SENDTYPE
16        I_BD_CUMANDOC_12        PK_PRICEGROUPCORP
17        I_BD_CUMANDOC_12        PK_PRICEGROUPCORP
18        I_BD_CUMANDOC_12        PK_PRICEGROUPCORP
19        I_BD_CUMANDOC_13        CREDITLEVEL
20        I_BD_CUMANDOC_13        CREDITLEVEL
21        I_BD_CUMANDOC_13        CREDITLEVEL
22        I_BD_CUMANDOC_14        PK_CUBASDOC
23        I_BD_CUMANDOC_14        PK_CUBASDOC
24        I_BD_CUMANDOC_14        PK_CUBASDOC
25        I_BD_CUMANDOC_3        PK_CORP
26        I_BD_CUMANDOC_3        PK_CORP
27        I_BD_CUMANDOC_3        PK_CORP
28        I_BD_CUMANDOC_5        PK_CALBODY
29        I_BD_CUMANDOC_5        PK_CALBODY
30        I_BD_CUMANDOC_5        PK_CALBODY
31        I_BD_CUMANDOC_6        PK_SALESTRU
32        I_BD_CUMANDOC_6        PK_SALESTRU
33        I_BD_CUMANDOC_6        PK_SALESTRU
34        I_BD_CUMANDOC_7        PK_RESPDEPT1
35        I_BD_CUMANDOC_7        PK_RESPDEPT1
36        I_BD_CUMANDOC_7        PK_RESPDEPT1
37        I_BD_CUMANDOC_8        PK_RESPPSN1
38        I_BD_CUMANDOC_8        PK_RESPPSN1
39        I_BD_CUMANDOC_8        PK_RESPPSN1
40        I_BD_CUMANDOC_9        PK_STORDOC2
41        I_BD_CUMANDOC_9        PK_STORDOC2
42        I_BD_CUMANDOC_9        PK_STORDOC2
43        PK_BD_CUMANDOC        PK_CUMANDOC
44        PK_BD_CUMANDOC        PK_CUMANDOC
45        PK_BD_CUMANDOC        PK_CUMANDOC








Execution Plan
----------------------------------------------------------
Plan hash value: 17730850

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |   486 | 30618 |  1675   (2)| 00:00:21 |
|*  1 |  FILTER              |                 |       |       |            |          |
|   2 |   HASH GROUP BY      |                 |   486 | 30618 |  1675   (2)| 00:00:21 |
|*  3 |    FILTER            |                 |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| GL_FREEVALUE    |   137K|  8489K|  1662   (2)| 00:00:20 |
|   5 |     INLIST ITERATOR  |                 |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| I_BD_CUMANDOC_1 |     1 |    26 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)>=1)
   3 - filter( EXISTS (SELECT /*+ */ 0 FROM "BD_CUMANDOC" "BD_CUMANDOC" WHERE
              "PK_CUBASDOC"=:B1 AND ("PK_CORP"='0001' OR "PK_CORP"='1074')) OR "CHECKVALUE"
              IS NULL)
   4 - filter("CHECKTYPE"='00010000000000000073')
   6 - access(("PK_CORP"='0001' OR "PK_CORP"='1074') AND "PK_CUBASDOC"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     513785  consistent gets
          0  physical reads
          0  redo size
     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client
        983  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14728  rows processed
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2010-3-26 11:12 | 显示全部楼层
...

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2010-3-26 11:15 | 显示全部楼层
group by freevalueid having count(*)>=1 这是什么条件 有必要吗">=1"

而且 从

     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client

可以看出结果集有点大

可以试一下

select /*+index(a I_GL_FREEVALUE2 )*/distinct(freevalueid) from gl_freevalue a where (checktype='00010000000000000073'
and ( checkvalue in (select pk_cubasdoc from bd_cumandoc where (pk_corp = '1074' or pk_corp = '0001') ) or checkvalue is null) )
group by freevalueid having count(*)>=1

[ 本帖最后由 catchwo 于 2010-3-26 11:19 编辑 ]

使用道具 举报

回复
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2010-3-26 11:16 | 显示全部楼层
USE_CONCAT  提示看看

CHECKTYPE,CHECKVALUE上来个组合索引,多好

[ 本帖最后由 zergduan 于 2010-3-26 11:18 编辑 ]

使用道具 举报

回复
论坛徽章:
15
数据库板块每日发贴之星
日期:2008-06-30 01:01:54奥运会纪念徽章:羽毛球
日期:2012-06-26 15:21:24ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26数据库板块每日发贴之星
日期:2011-07-15 01:01:01ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512010年世界杯参赛球队:加纳
日期:2010-07-27 08:59:132010年世界杯参赛球队:智利
日期:2010-07-14 16:06:302010年世界杯参赛球队:斯洛伐克
日期:2010-07-10 02:35:492010年世界杯参赛球队:英格兰
日期:2010-07-09 18:54:212010年世界杯参赛球队:瑞士
日期:2010-01-22 13:33:24
 楼主| 发表于 2010-3-26 11:21 | 显示全部楼层
原帖由 catchwo 于 2010-3-26 11:15 发表
group by freevalueid having count(*)>=1 这是什么条件 有必要吗">=1"

而且 从

     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client

可以看出结果集有点大

可以试一下

select /*+index(a I_GL_FREEVALUE2 )*/distinct(freevalueid) from gl_freevalue a where (checktype='00010000000000000073'
and ( checkvalue in (select pk_cubasdoc from bd_cumandoc where (pk_corp = '1074' or pk_corp = '0001') ) or checkvalue is null) )
group by freevalueid having count(*)>=1









没啥变化



Execution Plan
----------------------------------------------------------
Plan hash value: 583208260

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |   486 | 30618 | 29856   (1)| 00:05:59 |
|*  1 |  FILTER                        |                 |       |       |            |          |
|   2 |   SORT GROUP BY NOSORT         |                 |   486 | 30618 | 29856   (1)| 00:05:59 |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| GL_FREEVALUE    |   137K|  8489K| 29856   (1)| 00:05:59 |
|*  5 |      INDEX FULL SCAN           | I_GL_FREEVALUE  |   137K|       |  2696   (1)| 00:00:33 |
|   6 |     INLIST ITERATOR            |                 |       |       |            |          |
|*  7 |      INDEX RANGE SCAN          | I_BD_CUMANDOC_1 |     1 |    26 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)>=1)
   3 - filter( EXISTS (SELECT /*+ */ 0 FROM "BD_CUMANDOC" "BD_CUMANDOC" WHERE
              "PK_CUBASDOC"=:B1 AND ("PK_CORP"='0001' OR "PK_CORP"='1074')) OR "CHECKVALUE" IS NULL)
   5 - access("CHECKTYPE"='00010000000000000073')
       filter("CHECKTYPE"='00010000000000000073')
   7 - access(("PK_CORP"='0001' OR "PK_CORP"='1074') AND "PK_CUBASDOC"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     499008  consistent gets
          0  physical reads
          0  redo size
     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client
        983  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14728  rows processed

使用道具 举报

回复
论坛徽章:
15
数据库板块每日发贴之星
日期:2008-06-30 01:01:54奥运会纪念徽章:羽毛球
日期:2012-06-26 15:21:24ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26数据库板块每日发贴之星
日期:2011-07-15 01:01:01ITPUB9周年纪念徽章
日期:2010-10-08 09:28:512010年世界杯参赛球队:加纳
日期:2010-07-27 08:59:132010年世界杯参赛球队:智利
日期:2010-07-14 16:06:302010年世界杯参赛球队:斯洛伐克
日期:2010-07-10 02:35:492010年世界杯参赛球队:英格兰
日期:2010-07-09 18:54:212010年世界杯参赛球队:瑞士
日期:2010-01-22 13:33:24
 楼主| 发表于 2010-3-26 11:25 | 显示全部楼层
原帖由 catchwo 于 2010-3-26 11:15 发表
group by freevalueid having count(*)>=1 这是什么条件 有必要吗">=1"

而且 从

     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client

可以看出结果集有点大

可以试一下

select /*+index(a I_GL_FREEVALUE2 )*/distinct(freevalueid) from gl_freevalue a where (checktype='00010000000000000073'
and ( checkvalue in (select pk_cubasdoc from bd_cumandoc where (pk_corp = '1074' or pk_corp = '0001') ) or checkvalue is null) )
group by freevalueid having count(*)>=1






好了不少,CBO自己为什么不知道这样做?



SQL> select /*+index(a I_GL_FREEVALUE2 )*/distinct(freevalueid) from gl_freevalue a where (checktype
='00010000000000000073'
  2  and ( checkvalue in (select pk_cubasdoc from bd_cumandoc where (pk_corp = '1074' or pk_corp = '
0001') ) or checkvalue is null) )
  3  group by freevalueid having count(*)>=1
  4  ;

14728 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 772622427

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |   486 | 30618 |   102K  (1)| 00:20:35 |
|*  1 |  FILTER                        |                 |       |       |            |          |
|   2 |   HASH GROUP BY                |                 |   486 | 30618 |   102K  (1)| 00:20:35 |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| GL_FREEVALUE    |   137K|  8489K|   102K  (1)| 00:20:35 |
|*  5 |      INDEX FULL SCAN           | I_GL_FREEVALUE2 |   137K|       |  2821   (1)| 00:00:34 |
|   6 |     INLIST ITERATOR            |                 |       |       |            |          |
|*  7 |      INDEX RANGE SCAN          | I_BD_CUMANDOC_1 |     1 |    26 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)>=1)
   3 - filter("CHECKVALUE" IS NULL OR  EXISTS (SELECT /*+ */ 0 FROM "BD_CUMANDOC"
              "BD_CUMANDOC" WHERE "PK_CUBASDOC"=:B1 AND ("PK_CORP"='0001' OR "PK_CORP"='1074')))
   5 - access("CHECKTYPE"='00010000000000000073')
       filter("CHECKTYPE"='00010000000000000073')
   7 - access(("PK_CORP"='0001' OR "PK_CORP"='1074') AND "PK_CUBASDOC"=:B1)


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

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-3-26 11:31 | 显示全部楼层

回复 #6 hdydmichael 的帖子

nice

使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2010-3-26 11:33 | 显示全部楼层
你对你的原始sql有哪些地方不满意吗?

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |   486 | 30618 |  1675   (2)| 00:00:21 |
|*  1 |  FILTER              |                 |       |       |            |          |
|   2 |   HASH GROUP BY      |                 |   486 | 30618 |  1675   (2)| 00:00:21 |
|*  3 |    FILTER            |                 |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| GL_FREEVALUE    |   137K|  8489K|  1662   (2)| 00:00:20 |
|   5 |     INLIST ITERATOR  |                 |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| I_BD_CUMANDOC_1 |     1 |    26 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     499008  consistent gets
          0  physical reads
          0  redo size
     410758  bytes sent via SQL*Net to client
      11129  bytes received via SQL*Net from client
        983  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14728  rows processed

执行时间不长 只是logical read大一点 如果减小LOGICAL READ 根据你的sql 需要一个 FREEVALUEID CHECKTYPE CHECKVALUE 三列上的索引

使用道具 举报

回复
论坛徽章:
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-3-26 11:33 | 显示全部楼层


试一试如下的语句如何, 贴出Execution Plan和Statistics!


select  distinct(freevalueid)
from
(select freevalueid
  from gl_freevalue
where checktype='00010000000000000073'
   and checkvalue in(select pk_cubasdoc from bd_cumandoc where pk_corp in('1074', '0001'))
   and checkvalue is not null
union all
select freevalueid
  from gl_freevalue
where checktype='00010000000000000073'
   and checkvalue is null);


使用道具 举报

回复
论坛徽章:
10
授权会员
日期:2007-08-09 15:37:26会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB元老
日期:2007-10-15 21:12:09ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2013-06-24 14:20:02
发表于 2010-3-26 11:35 | 显示全部楼层
cbo 也不是全能的 哈哈

使用道具 举报

回复

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

本版积分规则 发表回复

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