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

I haven't tried this particular construction myself but it seems logical to me that this would be pretty quick.

create index idx_pvi_uuid on pvi_uuid(used_flag, pvi);

then select your value like this:

select pvi from pvi_uuid where used_flag = 'N' and rownum = 1 order by used_flag, pvi;

//Jonas

In article <8kqj17$avk$1_at_news.doit.wisc.edu>,   derrian.jones_at_doit.wisc.edu (Derrian Jones) wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jul 17 2000 - 00:00:00 CDT