Re: problem about Sequence

In article <717gtb$3h1$1_at_schbbs.mot.com>,   "Alan D. Mills" <alanm_at_uk.europe.mcd.mot.com> wrote:
> 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
>
>

If the application requires numbers that are exactly in sequence (e.g. 1, 2, 3, 4, ...) then SELECT MAX(user_id) FROM registration is the best solution since if a statetment that references a sequence is rolled back (canceled) that sequence number is lost (cause for a sequence gap). Likewise, any cached sequence numbers are lost each time a database is shut down, however, you can always specify the nocache option when creating the sequence.

Have fun

--
Edward Awad
Oracle Developer

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own