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

        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)