ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于Update语句的调整(3)

关于Update语句的调整(3)

原创 Linux操作系统 作者:husthxd 时间:2005-03-14 00:00:00 0 删除 编辑

关于Update语句的调整(3)


我们来比较一下调整前后update语句的执行效率如何:

调整前:

SQL> update pa

  2  set pa.col1=

  3  (select pa.col1*c.col1

  4  from p, c

  5  where p.id=pa.id and p.cid=c.cid

  6  )

  7  where exists (select 1

  8  from p,c

  9  where p.id=pa.id and p.cid=c.cid

 10  )

 11  and not exists (select 1

 12  from d

 13  where id=pa.id)

 14  /

 

已更新200000行。

 

已用时间:  00: 00: 37.04

 

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=15347 Card=2500 Byte

          s=60000)

   1    0   UPDATE OF 'PA'

   2    1     FILTER

   3    2       TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=2500 Bytes= 60000)

   4    2       NESTED LOOPS (Cost=4 Card=1 Bytes=36)

   5    4         TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 Bytes=24)

   6    5           INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=1)

   7    4         INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1 Bytes=12)

   8    2       INDEX (UNIQUE SCAN) OF 'PK_D' (UNIQUE) (Cost=2 Card=1 Bytes=12)

   9    1     NESTED LOOPS (Cost=5 Card=1 Bytes=46)

  10    9       TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 Bytes=24)

  11   10         INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=1)

  12    9       TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=2 Card=1 Bytes=22)

  13   12         INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card= 1)

 

Statistics

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

         99  recursive calls

     204763  db block gets

    7703664  consistent gets

        197  physical reads

   48989172  redo size

        374  bytes sent via SQL*Net to client

        486  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

     200000  rows processed

调整后:

SQL>

SQL> update pa

  2  set pa.col1=

  3  (select pa.col1*c.col1

  4  from p, c

  5  where p.id=pa.id and p.cid=c.cid

  6  )

  7  where exists (select 1

  8  from p,c

  9  where p.id=pa.id and p.cid=c.cid

 10  )

 11  and id not in (select /*+hash_aj*/ id

 12  from d)

 13  /

 

已更新200000行。

 

已用时间:  00: 00: 28.03

 

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=963 Card=1 Bytes=36)         

   1    0   UPDATE OF 'PA'                                                      

   2    1     FILTER                                                           

   3    2       HASH JOIN (ANTI) (Cost=959 Card=1 Bytes=36)                    

   4    3         TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=50000 Bytes=1200000)                                                                                                                                        

   5    3         INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284 Card=1000000 Bytes=12000000)                                                                                                                          

   6    2       NESTED LOOPS (Cost=4 Card=1 Bytes=36)                          

   7    6         TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 Bytes=24)                                                            

                                                                               

   8    7           INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=1)                                                                                                                                           

   9    6         INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1 Bytes=12)                                                                                                                                       

  10    1     NESTED LOOPS (Cost=5 Card=1 Bytes=46)                            

  11   10       TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 Bytes=24)                                                                                                                                               

  12   11         INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=1)                                                                                                                                                    

  13   10       TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=2 Card=1 Bytes=22)                                                                                                                                               

  14   13         INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1)                                                                   

 

 

Statistics

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

         42  recursive calls                                                   

     204670  db block gets                                                     

    4106564  consistent gets                                                    

          0  physical reads                                                    

   48968804  redo size                                                         

        481  bytes sent via SQL*Net to client                                  

        534  bytes received via SQL*Net from client                            

          3  SQL*Net roundtrips to/from client                                 

          2  sorts (memory)                                                    

          0  sorts (disk)                                                      

200000  rows processed                                                    

 

调整前后的执行时间分别是00: 00: 37.0400: 00: 28.03,效率有所提高。

 下面介绍几种调整(代替)update语句的方法。

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

上一篇: 有点郁闷了
请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1156
  • 访问量
    3625118