Re: Optimizing max function

In article <379470d9.139601346_at_news.iac.net>,   mwagoner_at_no.spam.iac.net (Mark Wagoner) wrote:
> I have a stored procedure that needs to determine the max value of a
> column at insert time. I have tried creating an non-unique index on
> this column (and using a hint to force the optimizer to use it) but
> this only results in a full index scan. With over 500,000 rows in the
> table, this results in 2 sec response time. I need to tweek this to
> under 1 sec.
>
> Is there some way to optimize a query containing the max() function?
>
> Thanks.
>
> --
> Mark Wagoner
> To reply, remove no.spam from my e-mail address
>

Use a cursor in PL/SQL to reduce the I/O. Given you are already in a PL/SQL procedure, this should work for you:

in the Declare section set up the cursor and result variable-- cursor c1 is
  select /*+index_desc(your_index) */

      column_needed
   from your_tablename
   order by column_needed desc;

mymax NUMBER;

then in the code section open the cursor and fetch the result:

  open c1;
  fetch c1 into :mymax;
  close c1;

If you need to use the value in a query directly, just make a function out of the necessary steps. The finction will be tied to the specific table, but that's OK. Using PL/SQL is much faster for getting MIN and MAX values.

NOTE: I did NO ERROR checking. You can add that yourself.

      The cursor is static (table and column specific). To make a more flexible function, you'll have to use the dynamic SQL package.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 20 1999 - 10:59:45 CDT