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

you could create a bitmap index on used_flag and use the query below. watch out for bitmap indexes if this table is to be updated often.

SELECT PVI
FROM PVI_UUID
WHERE USED_FLAG = 'N' and rownum = 1

-Drey.

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 Sun Jul 16 2000 - 00:00:00 CDT