To: ocurbelo 
On Fri, 3 Sep 1999, ocurbelo wrote:
> Hi Mr Kaplan:
> 
> We are writing you because we have visited your site looking for help. We
> have a problem of performance in the execution of the following query where
> the table ter.ter_master have 22231 records. So the results are obtained
> after hours. 
> 
> Cursor  rem_master(brepno VARCHAR2) IS
> select a.* from ter.ter_master a
> where NOT a.repno in (select repno from ermast) and
> (brepno = 'ALL' or a.repno > brepno)
> order by a.repno
> 
> Could you please help us to improve this SQL statement? 
> 
> Orlando Curbelo 

Do you have an index on TER_MASTER.REPNO and one on ERMAST.REPNO? If not
add one.

Be sure to get familiar with EXPLAIN PLAN. This can help you determine the
execution path that Oracle takes. If you are using Cost Based Optimizer
mode, then be sure that your statistics on TER_MASTER are up-to-date.

Also, you can change your SQL to:

SELECT a.*
FROM ter.ter_master a
WHERE NOT EXISTS (SELECT b.repno FROM ermast b
                  WHERE a.repno=b.repno) AND
      (a.brepno = 'ALL' or a.repno > a.brepno)
ORDER BY a.repno;

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 315+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page htm"> Back to Ari Kaplan's Home Page