Re: Optimizing max function

On Tue, 20 Jul 1999 15:59:45 GMT, Ed Prochak <prochak_at_my-deja.com> wrote:

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

Thanks for the advice, but I should have mentioned that it is already defined as a cursor. However, my initial testing after recreating the index in descending order look very promising.

Thanks again for the help.
--
Mark Wagoner
To reply, remove no.spam from my e-mail address Received on Tue Jul 20 1999 - 12:15:01 CDT