Re: create view performance problem

From: Craig <craigl_at_gte.net>
Date: Fri, 07 May 1999 06:04:12 GMT
Message-ID: <01be9794$b05b9ae0$351bfed0@Csledbetter-rem.Hdsc.com>

Or try...
SELECT

  a.symbol AS symbol,
  a.st_date AS st_date_for_max_value,
  a.value AS max_value

FROM
  yourtable a,
  (SELECT

     symbol, MAX(value) AS value
     FROM yourtable
     GROUP BY symbol

   ) b
WHERE
  a.symbol = b.symbol AND
  a.value = b.value
GROUP BY
symbol
HAVING
MAX(st_date);

which is logically similar, but for any given (symbol, max_value) pair, there may be more than one date on which it occurred. (e.g., If the value can ever be the same for a symbol on two different dates) This statement will select the most recent record which has the highest value for a given symbol. I am assuming that you would want the most recent. Use MIN(st_date) if you want the first. This statement also puts the subquery in the FROM clause. You may get better performance keeping subqueries out of complex WHERE clause expressions. (Always look at the optimizer's plan to see.) This will be faster than using PL/SQL, especially if you use parallel query.

CSL      dnotari_at_yahoo.com wrote in article <3733b124.24571491_at_newshost.us.oracle.com>...
> Vagelis,
> try this
>
> select symbol, st_date, value
> from test a
> where (a.value) = (select max(b.value)
> from test b where a.symbol = b.symbol group by symbol)
> /
>
> Dante
>
> On Sun, 2 May 1999 16:40:46 +0300, "vagelis Hristidis"
> <exrist_at_cc.ece.ntua.gr> wrote:
>
> >I have a table with columns :symbol, st_date,value
> >and I would like to create a view with columns:
> >symbol,st_date_for_max_value,max_value
> >where there is one row for each stock symbol.
> >How can I do it so that it performs well?
> >The problem is that sql won't let me use st_date in select clause when i
> >don't also put it in group by clause.If I use a pl/sql function it's slow.
> >Thanks in advance.
> >
> >vagelis
> >
>
>
Received on Fri May 07 1999 - 01:04:12 CDT