Re: query issue

From: kibeha <kibeha_at_post6.tele.dk>
Date: 5 Feb 2004 23:52:41 -0800
Message-ID: <444b180d.0402052352.455f7bfc@posting.google.com>

> anyway, to explain myself better, the desidered behaviour is the one
> that comes out from the following:
>
> select n_id,n_number,fk1 FROM numbers
> where n_id in
> (select max(n_id) from numbers group by n_number)
>
> I'm just worried about performances:
> my question was :'is there any way to avoid nestes queries'?

I posted this answer in comp.databases.oracle - but just found out that was problably wrong... so here goes again :

In Oracle 9 there is a way :

select

   n_number,
   max(fk1) keep (dense_rank last order by n_id) last_fk1 from numbers
group by n_number;

What this means is, that it will only do a max(fk1) on those records with the last n_id value within each group. As n_id is unique, the max(fk1) will only operate on the one record within each n_number group that has the largest n_id.

In Oracle 8 you won't avoid the nested query (which in itself does not affect performance), but you can avoid
accessing numbers table twice (which might (just might) affect performance) :

select distinct n_number, last_fk1
from
(

   select

      n_number,
      last_value(fk1) over (partition by n_number order by n_id rows

between unbounded preceding and unbounded following) last_fk1

   from numbers
);

The analytic function will give you the fk1 value for the last n_id within each n_number partition as you go along, but as that inner select will return a row for each row in numbers, you select distinct to only get the relevant information.

Hope these may help.

KiBeHa Received on Fri Feb 06 2004 - 01:52:41 CST