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

What is the distribution of the values of USED_FLAG ? If say 90% has value 'Y', and only 10% has value 'N', you might consider creating an index on USED_FLAG and using histograms (without those histograms, Oracle will probably always do a full table scan):

create index i_pu_used_flag on pvi_uuid(used_flag); analyze table pvi_uuid compute statistics for columns used_flag;

Marc

Derrian Jones wrote in message <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