ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 两个TABALE在联合查询很慢的问题

两个TABALE在联合查询很慢的问题

原创 Linux操作系统 作者：kewin 时间：2009-02-26 22:30:04 0 删除 编辑

开发人员反馈如果在语句中加上下面的一个查询条件的话，一个小时都没有结果。

select part.s1partnumber 父项编码,
       part.revision  父项PDM版本,
       part.s1partrevision 父项ERP版本,
       part1.s1partnumber 子项,      
       part1.s1partrevision 子项ERP版本,
       substr(doc.h3phyfilerev,2,1) 物理版本拆分的子项版本,
       doc.h3phyfilerev   物理版本,
       doc.documentname   文档对象,
       doc.revision       文档对象版本
      
from
(
select bj.obid,
       bj.s1partnumber,
       bj.revision,
       bj.s1partrevision,
       bj.lifecyclestate
from sum50a.s1assem bj
) part,
(
select bj1.obid,
       bj1.s1partnumber,
       bj1.revision,
       bj1.s1partrevision,
       bj1.lifecyclestate,
       mst.obid mstobid
from sum50a.s1AsmMtr mst--部件
     ,sum50a.s1assem  bj1
where bj1.itemmstrobid=mst.obid

) part1,
sum50a.partdoc rel,
sum50a.h3litdoc doc,
sum50a.assmstrc str

where substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision ---加上这句很慢，一个小时没有跑完，不加的话只需要30秒左右

       and part.obid=rel.left
      and doc.obid=rel.right
      and part.obid=str.left
      and part1.mstobid=str.right
      and str.preciserevision=part1.revision 
      and part1.s1partnumber like '0301%'  
      and doc.documenttype='单板'
      and doc.h3documentsubtype='焊接操作指导书'

先去掉那个“有问题” 的查询条件查看结果：

SQL>    select part.s1partnumber col1,
  2         part.revision  col2,
  3         part.s1partrevision col3,
  4         part1.s1partnumber col4,      
  5         part1.s1partrevision col5,
  6         substr(doc.h3phyfilerev,2,1) col5,
  7         doc.h3phyfilerev   col6,
  8         doc.documentname   col7,
  9         doc.revision       col8
 10  from
 11  (
 12  select bj.obid,
 13         bj.s1partnumber,
 14         bj.revision,
 15         bj.s1partrevision,
 16         bj.lifecyclestate
 17  from sum50a.s1assem bj
 18  ) part,
 19  (
 20  select bj1.obid,
 21         bj1.s1partnumber,
 22         bj1.revision,
 23         bj1.s1partrevision,
 24         bj1.lifecyclestate,
 25         mst.obid mstobid
 26  from sum50a.s1AsmMtr mst--部件
 27       ,sum50a.s1assem  bj1
 28  where bj1.itemmstrobid=mst.obid
 29  ) part1,
 30  sum50a.partdoc rel,
 31  sum50a.h3litdoc doc,
 32  sum50a.assmstrc str
 33  where part.obid=rel.left
 34        and doc.obid=rel.right
 35        and part.obid=str.left
 36        and part1.mstobid=str.right
 37        and str.preciserevision=part1.revision 
 38        and part1.s1partnumber like '0301%'  
 39        and doc.documenttype='dd'
 40        and doc.h3documentsubtype='dd'
 41  ;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=8 Card=1 Bytes=209)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost=3 Card=1
          Bytes=37)

   2    1     NESTED LOOPS (Cost=8 Card=1 Bytes=209)
   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=172)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=158)
   5    4           NESTED LOOPS (Cost=3 Card=1 Bytes=127)
   6    5             NESTED LOOPS (Cost=2 Card=1 Bytes=90)
   7    6               INDEX (FULL SCAN) OF 'PARTDOC_RLTLEFTNRIGHTIND
          EX' (UNIQUE)

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'H3LITDOC' (C
          st=1 Card=1 Bytes=62)

   9    8                 INDEX (UNIQUE SCAN) OF 'H3LITDOC_CLUSTEREDUN
          IQUEINDEX' (UNIQUE)

  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost
          =1 Card=499065 Bytes=18465405)

  11   10               INDEX (UNIQUE SCAN) OF 'S1ASSEM_CLUSTEREDUNIQU
          EINDEX' (UNIQUE)

  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'ASSMSTRC' (Cost=
          1 Card=1 Bytes=31)

  13   12             INDEX (RANGE SCAN) OF 'ASSMSTRC_RLTLEFTINDEX' (N
          ON-UNIQUE)

  14    3         INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDE
          X' (UNIQUE)

  15    2       INDEX (RANGE SCAN) OF 'S1ASSEM_PARTNUMBER_REVISION' (N
          ON-UNIQUE) (Cost=1 Card=4492)

 

 

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

把关联查询的子查询部分单独剥离出来，查看执行效果：
SQL> select bj1.obid,
  2         bj1.s1partnumber,
  3         bj1.revision,
  4         bj1.s1partrevision,
  5         bj1.lifecyclestate,
  6         mst.obid mstobid
  7  from sum50a.s1AsmMtr mst--部件
  8       ,sum50a.s1assem  bj1
  9  where bj1.itemmstrobid=mst.obid;

59843 rows selected.

Elapsed: 00:00:01.98

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'S1ASSEM'
   3    1     INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDEX' (
          UNIQUE)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      73552  consistent gets
        832  physical reads
          0  redo size
    2811464  bytes sent via SQL*Net to client
      44534  bytes received via SQL*Net from client
       3991  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      59843  rows processed

 查询条件： substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision
SQL> select count(*) from sum50a.h3litdoc;

  COUNT(*)
----------
     20588
可以看到关键的联合语句对于的数据源都是“大表”，可通过查询发现对于的字段都没有索引。先尝试创建INDEX，再执行SQL看下变化：
这时语句可以顺利查询出来：
SQL>    select part.s1partnumber col1,
  2         part.revision  col2,
  3         part.s1partrevision col3,
  4         part1.s1partnumber col4,      
  5         part1.s1partrevision col5,
  6         substr(doc.h3phyfilerev,2,1) col5,
  7         doc.h3phyfilerev   col6,
  8         doc.documentname   col7,
  9         doc.revision       col8
 10  from
 11  (
 12  select bj.obid,
 13         bj.s1partnumber,
 14         bj.revision,
 15         bj.s1partrevision,
 16         bj.lifecyclestate
 17  from sum50a.s1assem bj
 18  ) part,
 19  (
 20  select bj1.obid,
 21         bj1.s1partnumber,
 22         bj1.revision,
 23         bj1.s1partrevision,
 24         bj1.lifecyclestate,
 25         mst.obid mstobid
 26  from sum50a.s1AsmMtr mst--部件
 27       ,sum50a.s1assem  bj1
 28  where bj1.itemmstrobid=mst.obid
 29  ) part1,
 30  sum50a.partdoc rel,
 31  sum50a.h3litdoc doc,
 32  sum50a.assmstrc str
 33  where substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision
 34        and part.obid=rel.left
 35        and doc.obid=rel.right
 36        and part.obid=str.left
 37        and part1.mstobid=str.right
 38        and str.preciserevision=part1.revision 
 39        and part1.s1partnumber like '0301%'  
 40        and doc.documenttype='dd'
 41        and doc.h3documentsubtype='dd'
 42  ;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=1 Bytes=193)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost=1 Card=1
          Bytes=30)

   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=193)
   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=163)
   4    3         NESTED LOOPS (Cost=4 Card=1 Bytes=149)
   5    4           NESTED LOOPS (Cost=3 Card=1 Bytes=87)
   6    5             NESTED LOOPS (Cost=2 Card=1 Bytes=56)
   7    6               INDEX (FULL SCAN) OF 'PARTDOC_RLTLEFTNRIGHTIND
          EX' (UNIQUE)

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Co
          st=1 Card=1 Bytes=28)

   9    8                 INDEX (UNIQUE SCAN) OF 'S1ASSEM_CLUSTEREDUNI
          QUEINDEX' (UNIQUE)

  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'ASSMSTRC' (Cos
          t=1 Card=1 Bytes=31)

  11   10               INDEX (RANGE SCAN) OF 'ASSMSTRC_RLTLEFTINDEX'
          (NON-UNIQUE)

  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'H3LITDOC' (Cost=
          1 Card=1 Bytes=62)

  13   12             INDEX (UNIQUE SCAN) OF 'H3LITDOC_CLUSTEREDUNIQUE
          INDEX' (UNIQUE)

  14    3         INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDE
          X' (UNIQUE)

  15    2       INDEX (RANGE SCAN) OF 'S1ASSEM_ITEMMSTROBID' (NON-UNIQ
          UE)

 

 

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

说明： 在两个大表做联合查询时，联合的字段如没有字段，那首先尝试下建立INDEX。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/40239/viewspace-557979/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： ORACLE 10G不再SUPPORT RBO
下一篇： 启动ASM 实例报错
请登录后发表评论 登录
全部评论
kewin

注册时间：2008-03-10

  • 博文量
    125
  • 访问量
    618299

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593