 November 12, 2001
Reviewer:
A reader
 Can't incrementation of sequence be controlled? January 09, 2002
Reviewer:
Godwin
from Ghana
Hi Tom,
With this example given
i.e create table T(x int primary key...);
create sequence t_seq;
and then;
insert into t(x,...) values (t_seq_nextval, ...);
Now i did this but after i inserted like 3 rows into table t
and later deleted all the 3 rows in table t, I again insert another row but this
time the sequence continued from 4 instead of starting again from 1 since all
the previous 3 rows were deleted.
How can i ensure that the sequence starts from 1 again when the rows have been
deleted.
|
Followup:
|
You cannot, you do not want it to, it would be a totally inefficient thing to
do.
Sequences do not, will not, cannot give you a "gap free" set of numbers.
Sequences do one thing - they give you a unique number in a highly scalable
fashion.
|
January 10, 2002
Reviewer:
Saif
Hi GodWin
You can also do it as following
create trigger t_trigger
before insert on table_name
for each row
begin
select max(nvl(col_seq,0))+1
into :new.x
from table_name ;
end ;
/
If any number value is already present in it, it will autometically generate
next number in sequence. If there is no value, as in your case, it will generate
first sequence number and with this method, there is no chance to loose any
sequence number
I think, this will also help you.
AM I right Tom?
|
Followup:
|
try that in a multi user environment some day and see what happens. (hint -- if
you and I insert at the same time, we'll generate the SAME primary key value).
Do this only if you want to build the least scalable, slowest possible system.
If scaling and performance are not any concern to you -- this is perhaps
acceptable.
Also, insert three rows, delete where x = 2; and see the resulting "gap".
The quest for the "holy grail" of a gap free sequence of numbers in a relational
database that is suppose to perform and scale has always (and forever will)
confuse the heck out of me.
|
Autosequencing October 04, 2005
Reviewer:
Tyrone
from Tauranga New Zealand
Fantastic help, Im currently doing my degree and this site has been one heck of
a help in my Oracle papers, every question I could think of had already been
answered, BETTER THAN GOOGLEING IT !!!!!!!
|
Followup:
|
ok, you just said I'm better than "google"
that is the nicest thing anyone ever said to me :)
(google rocks though, it is in general 'much better')
|
Impact of nice looking data October 05, 2005
Reviewer:
Marc Blum
from Aachen, Germany
by the way:
in my experience, developers tend to design data models to deliver "nice
looking" data. Examples are
- gapless IDs
- ascending IDs
- new columns to be placed in the middle of the table definition
- columns with redundant/derived data
"Requierements" like these render the application unscalabe or at least consume
coding resources with no benefit to the application at all.
Tom, October 05, 2005
Reviewer:
A reader
did you ever *try*
www.google.nz
?
|
Followup:
|
www.google.nz could not be found. Please check the name and try again.
|
Ahem. October 05, 2005
Reviewer:
Mick in UK
from London UK
I think you mean www.google.co.nz
which I have quickly tested against www.google.co.uk
and the results look identical.
But Toms site is still an excellent resource.
This is the first time I have posted here but
Thanks tom for all your help over the years.
I always search your site first then google.
With sequences I tend to encourage our developers to use a stored procedure for
inserts and have the values clause use seq_idcol.nextval ... and
most times the insert has a returning clause. So that the developer can get
their id back from the procedure call.
Cheers
p.s. Tom, I think the way you handle Mikito is always very
professional. And while some times wacky, his questions always present an
opportunity to test another view point.
He is advancing the science of wacky words at least.
thanks again to you both
|
|