ITPub博客

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

关于Update语句的调整(4)

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

调整方法一:以空间换时间


        1.         调整方法一:以空间换时间

观察到存在select 1 from p,c where p.id=pa.id and p.cid=c.cid这样的sql语句,不妨在表c上增加id列,减少与p的连接。

SQL> alter table c add id char(10)

  2  /

表已更改。

已用时间:  00: 00: 00.00

SQL> update c set id = (select id from p where p.cid = c.cid) where exists (select 1 from p where p.cid = c.cid)

  2  /

已更新1000000行。

已用时间:  00: 01: 32.00

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=1499 Card=1000000 Bytes=34000000)                                                        

   1    0   UPDATE OF 'C'                                                      

   2    1     HASH JOIN (SEMI) (Cost=1499 Card=1000000 Bytes=34000000)         

   3    2       TABLE ACCESS (FULL) OF 'C' (Cost=320 Card=1000000 Bytes=22000000)                                                          

   4    2       INDEX (FAST FULL SCAN) OF 'IDX_P_CID' (NON-UNIQUE) (Cost=297 Card=1000000 Bytes=12000000)                                  

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

   6    5       INDEX (RANGE SCAN) OF 'IDX_P_CID' (NON-UNIQUE) (Cost=3 Card=4000)                                                          

 

Statistics

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

       1548  recursive calls                                                    

    2425257  db block gets                                                     

    3227258  consistent gets                                                   

          4  physical reads                                                    

  412946152  redo size                                                         

        480  bytes sent via SQL*Net to client                                  

        419  bytes received via SQL*Net from client                            

          3  SQL*Net roundtrips to/from client                                 

          8  sorts (memory)                                                    

          0  sorts (disk)                                                      

    1000000  rows processed                                                    

SQL> commit

  2  /

提交完成。

已用时间:  00: 00: 00.00

SQL> create index idx_c_id on c(id)

  2  /

索引已创建。

已用时间:  00: 00: 06.09

SQL> analyze table c compute statistics for table for all indexes

  2  /

表已分析。

已用时间:  00: 00: 13.07

SQL>

SQL> select *from pa

  2  where exists (select 1

  3  from c

  4  where c.id = pa.id

  5  )

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

  7  from d where id is not null)

  8  and pa.id is not null

  9  /

已选择200000行。

已用时间:  00: 00: 25.00

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1545 Card=1 Bytes=48         

          )                                                                    

                                                                                

   1    0   NESTED LOOPS (SEMI) (Cost=1545 Card=1 Bytes=48)                    

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

   3    2       TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=1000000 Byt          

          es=24000000)                                                         

                                                                               

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

          rd=1000000 Bytes=12000000)                                           

                                                                               

   5    1     INDEX (RANGE SCAN) OF 'IDX_C_ID' (NON-UNIQUE) (Cost=2 Ca         

          rd=1000000 Bytes=12000000)                                            

Statistics

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

          0  recursive calls                                                   

          0  db block gets                                                     

    1421450  consistent gets                                                   

          2  physical reads                                                    

          0  redo size                                                          

    6262548  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                                                     

-- update时间要优于先前的update语句

SQL> update pa

  2  set pa.col1=

  3  (select pa.col1*c.col1

  4  from c

  5  where pa.id=c.id

  6  )

  7  where exists (select 1

  8  from c

  9  where c.id = pa.id

 10  )

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

 12  from d where id is not null)

 13  and pa.id is not null

 14  /

已更新200000行。

已用时间:  00: 00: 22.00

Execution Plan

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

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=1545 Card=1 Bytes=48         

          )                                                                    

                                                                               

   1    0   UPDATE OF 'PA'                                                      

   2    1     NESTED LOOPS (SEMI) (Cost=1545 Card=1 Bytes=48)                  

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

   4    3         TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=1000000 B          

          ytes=24000000)                                                       

                                                                               

   5    3         INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284          

          Card=1000000 Bytes=12000000)                                         

                                                                               

   6    2       INDEX (RANGE SCAN) OF 'IDX_C_ID' (NON-UNIQUE) (Cost=2          

          Card=1000000 Bytes=12000000)                                         

                                                                               

   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=4 Card=10000          

          Bytes=250000)                                                         

                                                                               

   8    7       INDEX (RANGE SCAN) OF 'IDX_C_ID' (NON-UNIQUE) (Cost=3          

          Card=4000)                                                            

                                                                               

Statistics

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

         27  recursive calls                                                    

     204684  db block gets                                                     

    2077625  consistent gets                                                   

          0  physical reads                                                     

   48986692  redo size                                                         

        480  bytes sent via SQL*Net to client                                  

        542  bytes received via SQL*Net from client                             

          3  SQL*Net roundtrips to/from client                                 

          1  sorts (memory)                                                    

          0  sorts (disk) 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

注册时间:2007-12-28

  • 博文量
    1195
  • 访问量
    3663791