A reader, November 12, 2001 - 12:50 pm UTC
Can't incrementation of sequence be controlled?
Godwin, January 09, 2002 - 2:17 pm UTC
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.
January 09, 2002 - 2:21 pm UTC
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.
Saif, January 10, 2002 - 6:57 am UTC
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?
January 10, 2002 - 7:37 am UTC
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
Tyrone, October 04, 2005 - 8:42 pm UTC
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 !!!!!!!
October 04, 2005 - 9:20 pm UTC
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
Marc Blum, October 05, 2005 - 2:00 am UTC
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,
A reader, October 05, 2005 - 5:56 am UTC
did you ever *try*
www.google.nz
?
October 05, 2005 - 7:41 am UTC
www.google.nz could not be found. Please check the name and try again.
Ahem.
Mick in UK, October 05, 2005 - 6:41 am UTC
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
auto-generating
Piyush, June 16, 2007 - 6:51 am UTC
Hi Tom,
If we don't need to auto-generate a number globally across a table but if it is dependent on some other attribute like branch_id, then should we use autonomous function to achieve this in a web environment.
I meant...(hypothetical example)
Hi Tom,
If we don't need to auto-generate a number globally across a table but if it is dependent on some other attribute like branch_id, then should we use autonomous function to achieve this in a web environment.
I meant...
create table sample_gen
(
seq_type varchar2(10),
last_number number
)
/
create or replace function auto_gen(seq_type varchar2)
return number
is
curr_num number;
pragma autonomous_transaction;
begin
update sample_gen
set last_number = last_number + 1
where seq_type=seq_type;
begin
select last_number into curr_num from sample_gen where seq_type=seq_type;
exception
when no_data_found then
insert into sample_gen
values('BRANCH',1);
curr_num := 1;
end;
commit;
return curr_num;
end;
/
select auto_gen('BRANCH') from dual;
select auto_gen('BRANCH') from dual;
select auto_gen('BRANCH') from dual;
Given the business requirement, I think the above code should not reproduce any concern in the multi-user web environment.
If there is anything wrong, then please do suggest some alternative solution to me.
Thanking you,
Regards,
Piyush