sql 优化

Question

SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> select *  from CJ_FULL_DETAIL_MAT a
  2   left outer join cj_mat_related b
  3    on a.email=b.email
  4     and b.status = 200
  5     and b.adName = 'kq'
  6      where 1=1 and b.used_date is  null
  7       order by dbms_random.random;已选择9行。
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=234 Card=409 Bytes=1          
          299802)                                                               
                                                                                
   1    0   SORT (ORDER BY) (Cost=234 Card=409 Bytes=1299802)                   
   2    1     FILTER                                                            
   3    2       NESTED LOOPS (OUTER)                                            
   4    3         TABLE ACCESS (FULL) OF 'CJ_FULL_DETAIL_MAT' (Cost=2           
          Card=409 Bytes=1118615)                                               
                                                                                
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'CJ_MAT_RELATED' (C          
          ost=14 Card=1 Bytes=443)                                              
                                                                                
   6    5           BITMAP CONVERSION (TO ROWIDS)                               
   7    6             BITMAP AND                                                
   8    7               BITMAP CONVERSION (FROM ROWIDS)                         
   9    8                 INDEX (RANGE SCAN) OF 'REL_EMAL_ADNAME_IDX'           
          (UNIQUE)                                                              
                                                                                
  10    7               BITMAP CONVERSION (FROM ROWIDS)                         
  11   10                 INDEX (RANGE SCAN) OF 'REL_STATUS_IDX' (NON-          
          UNIQUE)                                                               
                                                                                
Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
       9008  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
       3374  bytes sent via SQL*Net to client                                   
        503  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
          9  rows processed                                                     SQL> spool off;
上面是执行计划
cj_mat_related  有四万多条记录，CJ_FULL_DETAIL_MAT 有几百条记录
执行上面的语句，时间是1.4秒左右想要得效果是:随机从CJ_FULL_DETAIL_MAT 选出一条记录 ,这条记录的邮箱不能是在cj_mat_related 表中状态是200且adname等于kq 的已存在的邮箱

Answer 1

加上frist rows ，时间了也没什么变化select /*+ first_rows  */ *  from CJ_FULL_DETAIL_MAT a
 left outer join cj_mat_related b  
  on a.email=b.email 
   and b.status = 200 
   and b.adName = 'kq' 
    where 1=1 and b.used_date is  null  
     order by dbms_random.random上面的执行计划，因为之前执行过，所以没有physical reads   但consistent gets  实存太多了

Answer 2

环境是 os:win2003  db :oracle9iR2

Answer 3

   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=234 Card=409 Bytes=1299802)                                                                                                                           
   1    0   SORT (ORDER BY) (Cost=234 Card=409 Bytes=1299802)                   
   2    1     FILTER                                                            
   3    2       NESTED LOOPS (OUTER)                                            
   4    3         TABLE ACCESS (FULL) OF 'CJ_FULL_DETAIL_MAT' (Cost=2  Card=409 Bytes=1118615)                                                                                                           
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'CJ_MAT_RELATED' (Cost=14 Card=1 Bytes=443)                                                      
   6    5           BITMAP CONVERSION (TO ROWIDS)                               
   7    6             BITMAP AND                                                
   8    7               BITMAP CONVERSION (FROM ROWIDS)                         
   9    8                 INDEX (RANGE SCAN) OF 'REL_EMAL_ADNAME_IDX'   (UNIQUE)                                                                                                                                    
  10    7               BITMAP CONVERSION (FROM ROWIDS)                         
  11   10                 INDEX (RANGE SCAN) OF 'REL_STATUS_IDX' (NON-UNIQUE)      格式化了一楼的sql执行计划