Re: selecting a column according to a minimum

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 20 Oct 2004 22:21:11 +0200
Message-ID: <cl6hbo$fr9$1@news.BelWue.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 Received on Wed Oct 20 2004 - 15:21:11 CDT