Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Reddy.

Asked: February 22, 2001 - 9:34 am UTC

Last updated: March 01, 2004 - 8:36 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

I have a batch process which inserts about 100 rows into a table every 2 hours. I don't have a problem with insert.

The problem is there is a document_id field. This field gets populated from a Oracle sequence. No gaps are allowed in this field (business rule). Recently I found gaps. The problem is there are some transactions rolled back due to some errors. Now I am looking for alternative solutions to solve this problem. Any ideas ?

and Tom said...

change the rule. No gaps = serialization = slow slow slow.

Sequences gaurantee gaps. If you shutdown and restart, you will probably find gaps as well.

Since you know have gaps, it might be a really good time to convince someone that gap free numbers is a hold over from the days when you did this processing on paper and the impact of doing it on a computer system is really bad.


Short of that, you have to create a table like:

my_seqs ( seq_name varchar2, seq_val number );


and a function like:

create function get_next_val( p_seq_name in varchar2 )
return number
as
l_val number;
begin
update my_seqs set seq_val = seq_val + 1
where seq_name = p_seq_name
returning seq_val into l_val;

return l_val;
end;

but remember -- you'll now have a level of concurrency that is basically ONE user at a time. Not a very scalable solution, but if you have one user it would work OK.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sorrily it doesn't meet the requierement

Marc Blum, February 13, 2004 - 7:59 am UTC

Sure your solution provides the possibility to retrieve gapfree numbers. But if the application throws away the retrieved number, the data in the table itself will become gappy/gapped (?). 

One solution is to add a before insert trigger, which retrieves the number.

My preferred solution is to bulletproof the datamodel itself via constraints. That way it will become impossible for any application to corrupt my business rule:

SQL> SET ECHO ON
SQL> alter session set nls_language=american;

Session altered.

SQL> 
SQL> CREATE TABLE t 
  2  (text           VARCHAR2(10),
  3   rg#            NUMBER, 
  4   rg#_previous   NUMBER,
  5   CONSTRAINT uk_rg# 
  6      UNIQUE(rg#),
  7   CONSTRAINT uk_rg#_previous 
  8      UNIQUE(rg#_previous),
  9   CONSTRAINT rg#_previous_fk
 10      FOREIGN KEY (rg#_previous)
 11      REFERENCES t(rg#),
 12   CONSTRAINT rg#_gapfree 
 13      CHECK(
 14             (
 15                  rg#                IS NOT NULL 
 16              AND rg#_previous       IS NOT NULL 
 17              AND rg# - rg#_previous = 1
 18             )
 19            OR
 20            (    rg#          IS NULL 
 21             AND rg#_previous IS NULL)
 22            OR
 23            (    rg#          = 1 
 24             AND rg#_previous IS NULL)
 25            ),
 26   CONSTRAINT rg#_positiv
 27      CHECK(sign(rg#) = 1)
 28   )
 29  /

Table created.

SQL> 
SQL> INSERT INTO t VALUES ('dummy',NULL,NULL);

1 row created.

SQL> INSERT INTO t VALUES ('first',1,NULL);

1 row created.

SQL> INSERT INTO t VALUES ('second',2,1);

1 row created.

SQL> INSERT INTO t VALUES ('third',3,2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> INSERT INTO t VALUES ('fourth',-1,NULL);
INSERT INTO t VALUES ('fourth',-1,NULL)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_POSITIV) violated


SQL> INSERT INTO t VALUES ('fourth',7,NULL);
INSERT INTO t VALUES ('fourth',7,NULL)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_GAPFREE) violated


SQL> INSERT INTO t VALUES ('fourth',7,6);
INSERT INTO t VALUES ('fourth',7,6)
*
ERROR at line 1:
ORA-02291: integrity constraint (VSM_OWNER.RG#_PREVIOUS_FK) violated - parent key not found


SQL> INSERT INTO t VALUES ('fourth',7,3);
INSERT INTO t VALUES ('fourth',7,3)
*
ERROR at line 1:
ORA-02290: check constraint (VSM_OWNER.RG#_GAPFREE) violated


SQL> INSERT INTO t VALUES ('fourth',4,3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> 
SQL> DROP TABLE t;

Table dropped.
 

Tom Kyte
February 13, 2004 - 10:31 am UTC

gap free numbers are just a "bad" idea.

how many ways can you spell "serialization"

the requirement is generally found "to not be really a requirement" in *all* of my experiences. even where people tell me "its the law" (found not really to be true).

tell me -- what happened in the day of paper (to which people point to and say this is why we must, because we did in the past) when you

a) lost a tablet with some invoice numbers
b) spilled coffee on some, destroying them before using them

well -- you just accounted for them. that is all that ever needs be done.

but anyway -- if you want to build slow non-scalable systems, this certainly works. have fun with all that.


You need to show all of the code -- you have to

a) select max(rg#) out first
b) before you can insert rg# and rg#+1
c) only to get blocked cause someone else was doing a) at about the same time
d) to go back to a again once you get the "unique constriant violated" to try all over again.




Zlatko Sirotic, February 13, 2004 - 2:23 pm UTC

Tom Kyte
February 13, 2004 - 3:06 pm UTC

just as yucky and un-necessary.

code could be alot more efficient as:


update sg
set id = id+1
where table_name = l_table_name
returning id into l_id;

if (sql%rowcount = 0) then
set it and
insert it
end if;

be a ton ton less code in anycase.

But -- i really dislike both approaches.

Dislike your attitude

Marc Blum, March 01, 2004 - 7:10 am UTC

Tom,

like many many others I really appreciate your deep knowledge und your commitment to educate the developer-community about efficient usage of the product "ORACLE database server".

What I dislike is your growing harshness and rude tone. And it's constantly growing. I don't understand, why your big success and growing popularity results in being unfriendly!? Do you think, all your effords are wasted? Have you the feeling of: "All my teaching, all my praying doesn't have any effect."?

I'll keep on buying your books and reading your answers, but I prefer not to communicate directly with you because your attitude towards people seems a little bit disturbed.

bye
Marc


Tom Kyte
March 01, 2004 - 8:36 am UTC

huh?

this is a pet peeve of mine, I've always blasted this

a) serial
b) slow
c) non-scalable

"implementation" -- everytime, going back to day 1 of my RDBMS career.

Every time, each time, equally as vocal. What you did "works" but it is a, b, and c. (and I might add d) when actually calmly looked at and explained -- not necessary. I have yet to find an actual case where by it was)


If you tell me which part of this was "harsh or rude", I'll gladly address.

I've always stated my opinion when I believe what is being done is wrong. I don't want people to use this approach -- leaving a "solution" here without a comment if I feel the solution is a "bad idea" wouldn't be a good thing either.