Take a look at the following example:

table T(i INTEGER, j INTEGER)

I want to get the value of i where j is minimal and some conditions apply.

(1)
SELECT i FROM T
WHERE [condition]
AND j
IN (SELECT min(j) FROM T WHERE [condition])

The best would be if such a syntax was allowed (which it isn't):
SELECT i, min(j) FROM T WHERE [condition]

How can I rewrite the SELECT statement in (1) such that [condition] doesn't
have to be typed in twice? Or how can I achieve the same thing in some other
way? (I am open to other solutions as well as long as they are inside the
limits of Oracle PL/SQL.)

SELECT i FROM
(SELECT MIN(j) OVER() as minj, j , i FROM T
WHERE [condition]) AS X WHERE minj = j

Whether that is faster or slower than your original depends on the
Oracle's internals which I don't know.

Cheers
Serge

"Agoston Bejo" <gusz1 (AT) freemail (DOT) hu> wrote:


Select i.min(j) from T
where [condition].
GROUP BY i;

Turkbear <john.g (AT) dot (DOT) spamfree.com> wrote:

min(j).
value.

Sorry...

Actually, here is what I could come up with:

select i from
(SELECT i, j FROM T WHERE [condition] ORDER BY j )
WHERE ROWNUM = 1

Is this very inefficient?

"Agoston Bejo" <gusz1 (AT) freemail (DOT) hu> wrote

"Agoston Bejo" <gusz1 (AT) freemail (DOT) hu> wrote

According to oracle doc the rownum is computed before the "order by" statement
is applied.

Perhaps better:

select i,j from
(select i,j,row_number() over (order by j) rn)
where rn=1;

Bye
Martin

Martin Dachselt wrote:
Not if the order by is in a inline view:

baer@DEMO10G>create table test (i integer, j integer);

Table created.

baer@DEMO10G>begin
2 for x in 1..100 loop
3 insert into test values (x, 100-x);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

baer@DEMO10G>commit;

Commit complete.

baer@DEMO10G>select i from (select i,j from test order by j)
2 where rownum=1;

I
----------
100

baer@DEMO10G>select min (j) from test;

MIN(J)
----------
0

baer@DEMO10G>select i from test where j=0;

I
----------
100



baer@DEMO10G>select i,j from
2 (select i,j,row_number() over (order by j) rn from test)
3* where rn=1


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=390
0)

1 0 VIEW (Cost=3 Card=100 Bytes=3900)
2 1 WINDOW (SORT PUSHED RANK) (Cost=3 Card=100 Bytes=500)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=100
Bytes=500)




baer@DEMO10G>select i from (select i,j from test order by j) where rownum=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=13)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=3 Card=100 Bytes=1300)
3 2 SORT (ORDER BY STOPKEY) (Cost=3 Card=100 Bytes=500)
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1
00 Bytes=500)




Note the appearence of STOPKEY: The optimizer nows that with rownum=XY
you will likely not take all of the result. This might lead the optimizer
to favour a first_rows plan.

Regards,

Holger

Yeah, you are right.
tables.
I thought the optimizer is guessing disk and cpu usage.

select * from (
select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss') from fdetailrecord
order by id
) where rownum < 5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82560 Card=4 Bytes=2
10937680)

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=82560 Card=8789070 Bytes=210937680)
3 2 SORT (ORDER BY STOPKEY) (Cost=82560 Card=8789070 Bytes
=123046980)

4 3 TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 C
ard=8789070 Bytes=123046980)


select * from (
select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss'),row_number() over
(order by id) rn from fdetailrecord
) where rn < 5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82560 Card=8789070 B
ytes=325195590)

1 0 VIEW (Cost=82560 Card=8789070 Bytes=325195590)
2 1 WINDOW (SORT PUSHED RANK) (Cost=82560 Card=8789070 Bytes
=123046980)

3 2 TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 Car
d=8789070 Bytes=123046980)


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;

Cu

Martin

Martin Dachselt wrote:
[..]

(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.

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.

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