same sql, 160ms with rownum 13968, 25s with rownum 13969

From: seb <seb_at_h3d.com>
Date: Fri, 16 Nov 2001 15:25:55 +0100
Message-ID: <3BF521F3.90007@h3d.com>

hi,

without add a rownum in my sql query, i have in result '17731' and execution time is 1m26s .
when i add a rownum (and rownum <= xx) to 13968 or inferior, it take 160 ms to execute. But when i put 13969 to rownum, it take 25s !!

here statistic for each query :

with 'and rownum <= 13968' :

           0  recursive calls
           4  db block gets
         135  consistent gets
           0  physical reads
           0  redo size
         379  bytes sent via SQL*Net to client
         424  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           6  sorts (memory)
           0  sorts (disk)
           1  rows processed

with 'and rownum <= 13969' :

           0  recursive calls
        3916  db block gets
       62341  consistent gets
           0  physical reads
           0  redo size
         379  bytes sent via SQL*Net to client
         424  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
         984  sorts (memory)
           0  sorts (disk)
           1  rows processed

the executin plan is the same for both query.

With sql analyse, i have ask to generate a index script. after execute the generated sql script for index, executions times with or without rownum are very fast.

Now, i would like to know exactly why there is a big execution time difference between these 2 sql query. How determine the problem ? analyse tools ? How know if a table or index need a rebuild ?

I use oracle 8.1.6, size of my base is only 80M.

sql:
select count(idapplicant) from applicants WHERE   applicants.idapplicantstatus != '3' AND applicants.idapplicantstatus !='6' AND applicants.idapplicantstatus != '12' AND Applicants.Typeofjobkey3 = '1'   AND Applicants.Applicantentrydate >= '03-AOU-2001'   AND (
  Applicants.Solicitationtype ='0' OR
  Applicants.Idapplicant IN
   (SELECT Applications.Idapplicant FROM Applications WHERE Applications.Applicationtreated ='2'

   MINUS
   SELECT Applications.Idapplicant FROM Applications,Applicants    WHERE Applicants.Idapplicant=Applications.Idapplicant

   and Applicants.Solicitationtype ='1' and 
Applications.Applicationtreated !='2')
  OR ( Applicants.Solicitationtype ='1' AND Applicants.Idapplicantstatus 

='12' )
)

thx
seb Received on Fri Nov 16 2001 - 08:25:55 CST