关于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.04和00: 00: 28.03，效率有所提高。

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