Re: problem about Sequence

You could select user_id_seq.nextval from dual to get the 'next' number giving an indication of the max in the table but this isn't very reliable. With sequences it's not uncommon to have gaps (sometimes large) in the values used and indeed, using this method will lose one of hte values itself.

The only reliable way to find the largest id used is to

SELECT MAX(user_id) FROM registration.

This is always exact and as you have a nice unique index on this column the query should drive just from that and not hit the table at all. It's shouldn;t take long.

--
Alan D. Mills

Ken Ho Kwok Fai wrote in message <7176a1$5kg_at_eng-ser1.erg.cuhk.edu.hk>...
> I made a sequence in a table "test" by the following SQL:
>
>============================================================
>create table registration
> (user_id number NOT NULL primary key,
> user_name varchar2(11)
> );
>
>create sequence user_id_seq start with 1 increment by 1 nocycle;
>=============================================================
>
> After I insert some values to the table, how could I know the
>max sequence number? Is there a table to store the sequence? I don't want
>to select it from the table and the compare the largest number as it
>needs a lot of time. Any method that could allow me to get the max
>sequence number? Thanks a lot.
>
>--
>Regards,
>Ken Ho
Received on Wed Oct 28 1998 - 10:32:28 CST