| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimizing max function
Mark Wagoner wrote:
>
> 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
Even better:
select /*+ INDEX_DESC(a b) */ the_column
from table a
where rownum < 2;
will scoot to the "bottom" of the index and just get that one value
(which will be the max)
--
"Some days you're the pigeon, and some days you're the statue." Received on Tue Jul 20 1999 - 08:41:46 CDT
 |
 |