ITPub博客

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

关于Update语句的调整(2)

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

(2)


         1.         通常的调整方法

-- 通常来说都先把update语句中的where子句提取出来进行调整。

A.调整前的执行过程:

SQL> select *from pa

  2  where exists (select 1

  3  from p,c

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

  5  )

  6  and not exists (select 1

  7  from d

  8  where id=pa.id)

  9  /

已选择200000行。

已用时间:  00: 00: 36.06

Execution Plan

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

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

          s=60000)                                                              

                                                                               

   1    0   FILTER                                                             

   2    1     TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=2500 Bytes=60          

          000)                                                                 

                                                                               

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

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

          tes=24)                                                              

                                                                               

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

          1)                                                                   

                                                                               

   6    3       INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1          

          Bytes=12)                                                            

                                                                               

   7    1     INDEX (UNIQUE SCAN) OF 'PK_D' (UNIQUE) (Cost=2 Card=1 By         

          tes=12)                                                               

                                                                               

Statistics

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

        340  recursive calls                                                   

          0  db block gets                                                     

    6517005  consistent gets                                                    

          7  physical reads                                                    

          0  redo size                                                         

    6182538  bytes sent via SQL*Net to client                                   

     546928  bytes received via SQL*Net from client                            

      13335  SQL*Net roundtrips to/from client                                 

          0  sorts (memory)                                                    

          0  sorts (disk)                                                      

     200000  rows processed                                                    

B.对sql语句进行加提示调整

-- 鉴于用于连接的字段均存在NOT NULL约束,可以考虑使用hash_aj提示调整查询

-- 注意:如果d.id/pa.id没有not null约束,hash_aj将不起作用.可以加d.id is not null/pa.id is not null条件避免这种情况

SQL>

SQL> select *from pa

  2  where exists (select 1

  3  from p,c

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

  5  )

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

  7  from d)

  8  /

已选择200000行。

已用时间:  00: 00: 32.00

Execution Plan

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

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

   1    0   FILTER                                                              

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

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

          =1200000)                                                            

                                                                               

   4    2       INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284 Ca         

          rd=1000000 Bytes=12000000)                                           

                                                                                

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

   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 By         

          tes=24)                                                               

                                                                               

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

          1)                                                                    

                                                                               

   8    5       INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1          

          Bytes=12)                                                             

                                                                               

Statistics

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

          0  recursive calls                                                    

          0  db block gets                                                     

    2906545  consistent gets                                                   

          3  physical reads                                                     

          0  redo size                                                         

    6275882  bytes sent via SQL*Net to client                                  

     546928  bytes received via SQL*Net from client                            

      13335  SQL*Net roundtrips to/from client                                 

          0  sorts (memory)                                                    

          0  sorts (disk)                                                      

     200000  rows processed                                                    

SQL>

虽然从执行时间时间上来看,不加提示和加提示分别为00: 00: 36.0600: 00: 32.00,相差不大,但从统计信息上来看,由原来的6517005  consistent gets变为2906545  consistent gets,调整收到一定效果。

 

 

 

 

 

 

 

 

 

 

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

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

注册时间:2007-12-28

  • 博文量
    1152
  • 访问量
    3623484