Re: PL/SQL statement for QUICKLY getting the "next available value"

Hi Demian,

The problem is that you're selecting the result of an operation on your primary key. If you select using min, max, sum, etc. then Oracle doesn't use the index anymore. I'm not really an expert on performance, but I know this is the reason why the performance is bad. I know there are very good DBA's in this newsgroup who probably know a good solution or work-around. Maybe you could add another column e.g. ID (filled by a sequence on insert) and index this as the primary key. Then in your query, select the ID and the min(pvi).. I'm not sure though..

Cheers,

Bastiaan Schaap
Oracle web development,
Desyde BV - Baarn
http://www.desyde.nl/
tel. +31355411711

Derrian Jones <derrian.jones_at_doit.wisc.edu> wrote in message news:8kqj17$avk$1_at_news.doit.wisc.edu...
> Hello all,
>
> First time posting to this group.
>
> I have what seems to be a simple question, but we can't seem to find a
 simple
> answer. We have a big table (about 3 million rows) that consist of only
 two
> cols. Here is the describe of table PVI_UUID...
>
> Name Null? Type
> ------------------------------- -------- ----
> PVI NOT NULL VARCHAR2(9)
> UUID VARCHAR2(38)
> USED_FLAG VARCHAR2(1)
>
> All we're trying to do is select from the table the next available row
 that
> has a Used_Flag = 'N'. We've tried:
>
> SELECT MIN(PVI)
> FROM PVI_UUID
> WHERE USED_FLAG = 'N'
>
> This works fine, but the execution time is over 1 minute! Is there any
 way we
> can optimize this select?
>
> Thanks in advance...
>
> DJ
>
Received on Mon Jul 17 2000 - 00:00:00 CDT