XPost: comp.databases.oracle.misc, comp.databases.oracle.server   
   From: holger.baer@science-computing.de   
      
   Martin Dachselt wrote:   
   [..]   
      
   >   
   > Strange: costs and execution time seems bo be equal, even for big   
   > tables.   
   Not in my tests. The difference was small, but analytics always came last   
   (same table as in my previous post, but this time with 10 million rows).   
   The runtime was about 6.5 s for the rownum solution and 7s for the analytics   
   solution.   
      
   > I thought the optimizer is guessing disk and cpu usage.   
   >   
   But they won't make any difference. A full scan is a fullscan, no matter   
   what you try. But note the difference in the cardinality and bytes between   
   the your plans. With rownum, the plan is expected to return exactly the   
   number of rows you asked for. With analytics, oracle cannot now how much   
   data you're going to retrieve, so it's pessimistic and expects the worst.   
      
   >   
   > One advantage of the solution with analytic functions, is that you can   
   > do:   
   > select * from (   
   > select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss'),row_number() over   
   > (order by id) rn from fdetailrecord   
   > ) where rn between 5 and 10;   
      
   But not really what the OP asked for, is it? And this one is equivalent   
   in every respect (except that it's slightly faster on my machine):   
      
   select * from (   
            select i, rownum rn from (select i,j from test order by j)   
            where rownum<= 10)   
   where rn between 5 and 10   
   /   
      
   To return to the original question:   
      
      
   select i from test where i between 10 and 15   
     and j in (select min(j) from test where i between 10 and 15)   
      
   is a working example of what the OP regarded as not possible (at least in 10g   
   it works).   
   And with proper indexes in place this is pretty fast, too.   
      
   Perhaps we can get the OP to explain his problem better?   
      
   Regards,   
   Holger   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)