Home » SQL & PL/SQL » SQL & PL/SQL » sequence
sequence [message #304878] Fri, 07 March 2008 00:55 Go to next message
dba_01
Messages: 8
Registered: January 2008
Junior Member
hi,
suppose i delete few rows from the table in which sequence has been generated, how will it effect on sequence.
Will the last value in sequence be decremented or remain same.

Please help

Regards
Re: sequence [message #304881 is a reply to message #304878] Fri, 07 March 2008 00:59 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
will remain same.

regards,
Re: sequence [message #304882 is a reply to message #304878] Fri, 07 March 2008 01:01 Go to previous messageGo to next message
dba_01
Messages: 8
Registered: January 2008
Junior Member
Are you sure about it
Re: sequence [message #304887 is a reply to message #304878] Fri, 07 March 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them.

Regards
Michel
Re: sequence [message #304889 is a reply to message #304878] Fri, 07 March 2008 01:27 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member

Quote:
Are you sure about it


watch it:-

SQL> ed
Wrote file afiedt.buf

  1  create sequence n
  2  minvalue 1
  3  start with 1
  4  increment by 1
  5* nocache
SQL> /

Sequence created.


  1* create table test (si_no number,name varchar2(20))
SQL> /

Table created.



SQL> ed
Wrote file afiedt.buf

  1* insert into test values(n.nextval,'&name')
SQL> /
Enter value for name: hjhgh
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'hjhgh')

1 row created.

SQL> /
Enter value for name: yuftweuft
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'yuftweuft')

1 row created.

SQL> /
Enter value for name: yftweuif
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'yftweuif')

1 row created.

SQL> /
Enter value for name: gufuiqwdfy
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'gufuiqwdfy')

1 row created.

SQL> /
Enter value for name: ufiweyfw
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'ufiweyfw')

1 row created.

SQL> /
Enter value for name: hfiwey
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'hfiwey')

1 row created.

SQL> /
Enter value for name: hifiweui
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'hifiweui')

1 row created.

SQL> /
Enter value for name: iofuweou
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'iofuweou')

1 row created.

SQL> select * from test;

     SI_NO NAME
---------- --------------------
         1 hjhgh
         2 yuftweuft
         3 yftweuif
         4 gufuiqwdfy
         5 ufiweyfw
         6 hfiwey
         7 hifiweui
         8 iofuweou

8 rows selected.

SQL> delete from test where si_no<5;

4 rows deleted.

SQL> select * from test;

     SI_NO NAME
---------- --------------------
         5 ufiweyfw
         6 hfiwey
         7 hifiweui
         8 iofuweou

SQL> insert into test values(n.nextval,'&name')
  2  /
Enter value for name: gfasdufgt
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'gfasdufgt')

1 row created.

SQL> select * from test;

     SI_NO NAME
---------- --------------------
         5 ufiweyfw
         6 hfiwey
         7 hifiweui
         8 iofuweou
         9 gfasdufgt

SQL> delete from test;

5 rows deleted.

SQL> insert into test values(n.nextval,'&name')
  2  /
Enter value for name: gfasdufiyd
old   1: insert into test values(n.nextval,'&name')
new   1: insert into test values(n.nextval,'gfasdufiyd')

1 row created.

SQL> select * from test;

     SI_NO NAME
---------- --------------------
        10 gfasdufiyd



regards,
Re: sequence [message #304891 is a reply to message #304878] Fri, 07 March 2008 01:30 Go to previous messageGo to next message
dba_01
Messages: 8
Registered: January 2008
Junior Member
thank u
Re: sequence [message #304892 is a reply to message #304889] Fri, 07 March 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@mshrkshl

An example does prove NOTHING
(just that in your example when you execute it it happens what you think it happens)

Regards
Michel

[Updated on: Fri, 07 March 2008 01:39]

Report message to a moderator

Re: sequence [message #304894 is a reply to message #304878] Fri, 07 March 2008 01:45 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
This is a common mis-understanding: there is NO PHYSICAL - I repeat NO PHYSICAL - link between a sequence and a table or column!

You get a new value from a sequence; you do with that value whatever you want: use it in a SQL-statement if you like or just disregard it.
The SEQUENCE-concept in Oracle is totally different from the IDENTITY-property within that other major database-system that's floating around Wink


@ Michel: lighten up a bit or you might get an ulcer Wink
Previous Topic: Add amounts for similar grouped items?
Next Topic: DBMS_SQL
Goto Forum:
  


Current Time: Sat Dec 03 00:56:58 CST 2016

Total time taken to generate the page: 0.16420 seconds