(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.06和00: 00: 32.00，相差不大，但从统计信息上来看，由原来的6517005  consistent gets变为2906545  consistent gets，调整收到一定效果。