ITPub博客

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

关于Update语句的调整(5-2)

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

调整方法二:建立临时表+Merge

(2)


-- 回到本例,设置pa.col2为非空

-- 验证merge没有往pa插入数据

 

SQL> alter table pa modify col2 not null

  2  /

 

表已更改。

 

已用时间:  00: 00: 01.08

SQL> drop table temp_table

  2  /

 

表已丢弃。

 

已用时间:  00: 00: 00.00

SQL> create global temporary table temp_table

  2  (id char(10),new_col1 number(10,2))

  3  /

 

表已创建。

 

已用时间:  00: 00: 00.00

SQL> insert /*+parallel(4)*/ into temp_table

  2  select /*+use_hash(pa p)*/

  3  pa.id,pa.col1*c.col1

  4  from pa,p,c

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

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

  7  from d)

  8  /

 

已创建200000行。

 

已用时间:  00: 00: 09.06

 

Execution Plan

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

   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=1973 Card=1 Bytes=85)                                                                     

   1    0   NESTED LOOPS (Cost=1973 Card=1 Bytes=85)                           

   2    1     HASH JOIN (Cost=1971 Card=1 Bytes=60)                            

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

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

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

          Card=1000000 Bytes=12000000)                                                                                                                       

   6    2       TABLE ACCESS (FULL) OF 'P' (Cost=425 Card=1000000 Bytes=24000000)                                                           

   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'C' (Cost=2 Card=1 Bytes=25)                                                                

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

 

 

Statistics

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

         44  recursive calls                                                   

       3348  db block gets                                                     

     680381  consistent gets                                                   

          0  physical reads                                                    

     570536  redo size                                                         

        482  bytes sent via SQL*Net to client                                  

        494  bytes received via SQL*Net from client                            

          3  SQL*Net roundtrips to/from client                                 

          2  sorts (memory)                                                     

          0  sorts (disk)                                                      

     200000  rows processed                                                    

 

SQL> merge into pa

  2  using temp_table

  3  on (pa.id = temp_table.id)

  4  when matched then

  5  update set pa.col1 = temp_table.new_col1

  6  when not matched then

  7  insert /*+append*/ (pa.id) values(temp_table.id)

  8  /

 

200000 行已合并。

 

已用时间:  00: 00: 07.06

 

Execution Plan

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

   0      MERGE STATEMENT Optimizer=CHOOSE (Cost=1004 Card=1000002 Bytes=49000098)                                                         

   1    0   MERGE OF 'PA'                                                       

   2    1     VIEW                                                             

   3    2       HASH JOIN (OUTER) (Cost=1004 Card=1000002 Bytes=49000098)                                                                  

   4    3         TABLE ACCESS (FULL) OF 'TEMP_TABLE' (Cost=11 Card=175005 Bytes=4375125)                                                  

   5    3         TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=1000000 Bytes=24000000)                                                        

                                                                               

 

Statistics

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

         28  recursive calls                                                    

     204650  db block gets                                                     

       4190  consistent gets                                                   

          0  physical reads                                                     

   48986184  redo size                                                         

        481  bytes sent via SQL*Net to client                                  

        500  bytes received via SQL*Net from client                            

          3  SQL*Net roundtrips to/from client                                 

          2  sorts (memory)                                                    

          0  sorts (disk)                                                      

     200000  rows processed                                                    

 

SQL>

我们看到,插入临时表中花费的时间为00: 00: 09.06merge语句执行的时间为00: 00: 07.06,总共的时间大致为17秒左右,调整的效果不错。

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

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1395
  • 访问量
    3841820