Sequences
June 22, 2002 - 3pm Central time zone
Reviewer: Venkatesa Prasad from India
Tom,
Suppose I cannot use sequences for my PK generator, then what is the best way to generate.
Well, I cannot use sequences for basically 3 reasons.
1. My numbers have to be generated financial year-wise.
- Then I have to create sequences 1 for each financial year. isn't it?
2. I have a master table which tells what are the various voucher types and when user selects a
given voucher type in the transcation, system has to generate a number for that type and that too
it should start afresh for every month. I cannot create sequences before itself, as I dont know how
many voucher types will get added each month.
3. Third is related to gaps. Suppose in the front end (we user Developer 2000 - Forms 4.5), the
user saves the data but leaves the session uncommitted (I mean 'POST' is issued). Then sequence is
ost without the data getting committed. I mean the saved data is rolled back. Then how to avoid
gaps.
Can you clarify, please.
Venkatesa Prasad
Followup June 22, 2002 - 4pm Central time zone:
1) why -- why not just know that "1-100323" are fy1. 100324-2431223 are fy2 and so on. Since you
"reset" the numbers for each year, this obviously isn't a primary key by itself -- the pk must be
"FY + ID" (two columns). This id is just to keep it unique within the year.
2) why again? why?
3) well, if you use a gap free approach -- you know what'll happen in this case? NO ONE ELSE IN
YOUR ENTIRE SYSTEM WILL BE ABLE TO WORK, that is what. Why? cause this session with uncommitted
data will have the id generating table locked.
Sigh, I hesitate to show you this, but here goes. Gap free, non-concurrent, highly NON-scalable:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table ids ( name varchar2(30), id number );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function get_nextval( p_name in varchar2 )
return number
2 as
3 l_id number;
4 begin
5 update ids set id = id+1 where name = upper(p_name)
6 returning id into l_id;
7
8 if ( sql%rowcount = 0 )
9 then
10 raise_application_error( -20001, 'No such id name ' || p_name );
11 end if;
12 return l_id;
13 end;
14 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo' ) )
BEGIN dbms_output.put_line( get_nextval( 'foo' ) ); END;
*
ERROR at line 1:
ORA-20001: No such id name foo
ORA-06512: at "OPS$TKYTE.GET_NEXTVAL", line 10
ORA-06512: at line 1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into ids values ( 'FOO', 0 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo' ) )
1
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( get_nextval( 'foo' ) )
2
PL/SQL procedure successfully completed.
Now, when you want to reset, just update ids set id = 0 where name = 'whatever'.
Yuck.
sequnces
November 12, 2002 - 7am Central time zone
Reviewer: mo
Tom:
I have a production instance that started to skip sequnce numbers. The first 15 records created
were fine and created with number 1000 - 1015 but the nafter oct 1 I statrted noticing I got 1020,
1043 and 1060.
I am only doing a select on the sequence.next val in my save procedure. First I select ito into a
variable and the ni use the variable in my insert statement. The reason I use a variable instead
of seq.nextval is that I insert into multiple tables with same sequnce numbers.
What do you think may be happening that causing this? This is a web application using stored
procedures.
Followup November 12, 2002 - 10am Central time zone:
There is no such thing as a sequence "skipping numbers". In order for a sequence to "skip numbers"
it would have to first assure gap free number sequences and they explicitly DO NOT.
Sounds like your shared pool is way too small and the sequence is getting aged out (not used
enough).
But -- I would just *NOT WORRY ABOUT IT AT ALL* (unless your shared pool is really too small, fix
that of course but don't worry about the sequence)
a sequence is used to generate a primary key/unique value. Nothing more, nothing less. any
assumptions about "gap free" or "lost numbers" is in vain.
sequence
November 12, 2002 - 10am Central time zone
Reviewer: mo
TOm:
<Sounds like your shared pool is way too small and the sequence is getting aged
out (not used enough). >
Can you elaborate on how can a sequence get aged out if it is not used. How does it increment if
you are nor running (select seq.nextval from dual).
2. The primary key is used by client as a reference number. It is really awkward to have then refer
to PO 1000 and the npo 1050. It makes you think that there are 50 po's in the system when in fact
there are only 2.
Thank you,
Followup November 12, 2002 - 1pm Central time zone:
things in the shared pool (where sequences are cached) are aged out using an LRU (least recently
used). Hence, if you select s.nextval and then don't touch S for a while and we need space in the
shared pool -- out goes S and out goes any cached values for S.
2) no it isn't. why is it? count(*) tells them how many po's are in the system. A po number
doesn't, cannot.
Think about this. Its the days before computers. Everything is on paper. You have a stack of
numbered PO's in a tablet. The current number is 1 on the top. You fill out PO #1 and put it in
the OUT box. You bump your coffee onto this tablet spilling it. Whoops. The coffee soaks the
next 20 sheets which you tear off and through away. You are on PO 21 -- are you confused? Do you
think there are 20 po's in your out box? No -- you see there is one PO there.
Getting back to today. If you've told your users to look at the PO number to figure out how many
there are -- they've been mislead. There is a fact about sequences -- an UNDENIABLE, UNESCAPABLE
fact -- they are not gap free, will never be gap free, they will have gaps!. No matter WHAT
you do, you cannot look at the sequence number (your po number) and make any conclusions about the
count of PO's in your system -- none.
How does sequences avoid serialization?
December 18, 2002 - 4pm Central time zone
Reviewer: Sam from San Mateo, CA
Tom,
As usual, your answers are most helpful.
I read this in the Oracle9i Application Developers Guide:
"Sequences eliminate serialization and improve the concurrency of your application". Is this
really true? Or do they just mean "sequences minimizes serialization because it's native in Oracle
and is really fast especially when the numbers are cached"? What gets locked when I do
my_sequence_s.nextval?
Also, I saw this statement in my trace file:
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9
where
obj#=:1
Could you tell me what operation caused this SQL?
Followup December 18, 2002 - 6pm Central time zone:
there is a lightweight latch used to serialize access for a split instant. 100% de-serialization,
no, 99.99% -- yes.
The alternative is to serialize entire TRANSACTIONS...
that is recursive SQL, everytime you advance the sequence in the SGA beyond the cache size, we need
to update a database table in a recursive (autonomous) transaction
Strong reason
December 19, 2002 - 1am Central time zone
Reviewer: Tony from India
Tom, You say that gap free numbers are not required at all. I hope you will agree with the
following case:
Generated numbers should have built-in intelligence so that end users will easily identify the
counts and other business related thing. For example, prefixing purchase order number with vendor
code will defintely help the end users to know the vendor by merely seeing the PO number. Only
numbers (PO number, Request number, voucher number...etc) with lot of gaps will confuse the users.
There are valid reasons , as above, to have gap free numbers. We can't ignore the importance of gap
free numbers just becoz Oracle doesn't have the feature.
Followup December 19, 2002 - 7am Central time zone:
I disagree totally, 100%
Your argument will be "well, they used to use paper and the paper was sequentially numbered. they
relied on that number".
My counter argument will be "well, I just spilled my coffee on your tablet of paper destroying the
top 20 pieces of paper. I put them in the garbage. Did the business shutdown? No, of course
not".
We void checks all of the time. Same thing.
If your end users rely on "counts" embedded in a number, they are getting BAD information. Period.
There are cancelled orders. There will be undone transactions. The counts will be *wrong*.
Knowing the vendor by looking at the "po" number -- trivial. the PO number is obviously a compound
field on a printed report -- eg:
select vendor_id || '-' || primary_key PO_NUMBER, ....
from table
If gaps confuse users -- it is only because you told them there was some implied meaning to it.
There is no other reason -- cause the old paper based system sure couldn't have taught them this.
December 19, 2002 - 8am Central time zone
Reviewer: Jeff Larimore from Miami, Florida
Tom,
Is Oracle considering creating a column type of IDENTITY whereby Oracle will automatically assign a
unique value to a column?
This would create a sequence tied to the column automatically and would not require the user to
define a sequence for the column, and then create an insert trigger to call it, which you I know
you recommend against.
I've seen this in other DBMS servers and have found it extremely user-friendly, self-documenting
and convienient.
Thank you,
Jeff
Followup December 19, 2002 - 9am Central time zone:
I will submit this to the people that can make it happen...
That nasty word "embedded"
December 19, 2002 - 11am Central time zone
Reviewer: Connor McDonald from UK
The problem with "informational" columns, for example, the PO number telling you how many there
are, is that it breaks a lot of the rules for relational databases. Scalar columns represent an
attribute (singular not plural).
There is nothing worse than being forced to work on a system where column P contains "02X3751"
because it was
- create in the year '02'
- is of type 'X'
- is the 3751'st recording of such
Ugh!! It doesn't take long before your start seeing hideous queries like:
where substr(P,4,4) = '3751'
and voila! Welcome to function-based-index heaven...
What's wrong with a year, type, and occurrence column ?
Followup December 19, 2002 - 11am Central time zone:
exactly -- and then a view to synthesize these "human friendly" things
select vendor_id || '-' || primary_key PO_NUMBER, ....
from table
people do seem to have an irrational fear of views for some reason....
store the data in its component pieces, glue them together however you see fit on OUTPUT.
December 19, 2002 - 12pm Central time zone
Reviewer: Tianhua Wu from Atlanta, GA
I agree in most case the gap should not be a problem at all. However, in some country (e.g.
Italy), you can not have the gap by law (if I understood correctly).
Followup December 19, 2002 - 1pm Central time zone:
what did they do when the coffee got spilt?
what do they do when a customer cancels an order?
what would be the purpose of such a law? I mean -- what is to stop me from just creating a dummy
order for $0 or just to go back and put the orders in out of sequence.
I would just create a view:
select a.*, rownum LAW_ABIDING_STUFF from t;
I guess.... no gaps there and just as meaningful!
Sequence generation
December 19, 2002 - 4pm Central time zone
Reviewer: pk from alaska
For sequence generation why don't you do this :-
PK>create table temp1(id number(12));
Table created.
Elapsed: 00:00:00.51
PK>select * from temp1;
no rows selected
Elapsed: 00:00:00.10
PK>select nvl(max(id),0) + 1 seq_num from temp1;
SEQ_NUM
----------
1
Elapsed: 00:00:00.31
PK>
Try this then...
December 19, 2002 - 5pm Central time zone
Reviewer: Connor McDonald from UK
session 1:
select max(...)+1
session 2:
select max(...)+1
Ooops....data corruption.
December 20, 2002 - 12am Central time zone
Reviewer: Dave from Ohio
Tianhua's point is an important one, and the legal requirement to avoid gaps in sequences is a real
one in many countries.
Commonly it applies to tax-related "documents" such as invoices -- if a tax authority finds a
missing value in a sequence of invoices it raises a red flag to them that there is something fishy
going on (although I have to add that even if there were no apparant rationale for the law, it has
to be complied with if only because a company will be prosecuted and fined for not being in
compliance. They will then sue their software vendor.) For all the scenarios of spilt coffee,
cancelled orders, dummy orders etc. there is a legally sanctioned resolution.
The view-method would be legally unacceptable as the invoice number must not be subject to change.
However that doesn't mean that you have to cripple the performance of your application by
serializing the generation of invoices, for example. Consider whether you could PK the invoice with
a regular (performant) methodology, then update the records later on as a batch process, such as
...
update invoices set invoice_number=inv_seq.next_val;
... or ...
select max(invoice_number) from invoices into l_max_inv_number;
update invoices set invoice_number=l_max_inv_number+rownum
where invoice_number is null;
You can put a unique constraint on the nullable invoice_number field, and maybe a function-based
index also to give fast identification of the null invoice number rows.
Tom, I'm not familiar with Oracle Applications, but perhaps you could get someone from the
Applications team to address how they deal with requirements like this -- I bet that they are
familiar with the scenario, and have a way of coping with it.
Followup December 20, 2002 - 8am Central time zone:
But my point seems to be getting totally missed.
anyway.
you next_val -- not gap free. 100% not gap fere.
Your select max(), not very concurrent (eg: leads to duplicates out the wahzoo)...
They do it like you would with your checkbook. It is called a "void". fill in dummy records.
Assign the numbers when you close the books, not when filling them.
Tell me -- what happened when someone spilt coffee on the invoice forms???
December 20, 2002 - 12am Central time zone
Reviewer: Tianhua Wu from Atlanta, GA
select a.*, rownum LAW_ABIDING_STUFF from t;
...
Unfortunately, the rownum will be different for the same record in different queries.
The law is law, there are not too much we can do about it. And it indeed makes life much
difficult. On the other hand, it may generate new positions. :)
Hmmmm
December 20, 2002 - 8am Central time zone
Reviewer: George Johnson from UK
I totally accept the gap-free argument, I can't see what the problem is, if numbers are skipped due
to cancellations, employee incompetance (split coffee), then surely simply keeping an official
record that missed numbers are "voided" should satisfy TAX officials, but I guess my real question
would be, what on earth is a "whazoo"? (I'm from the UK you see.)
Tks in adv
Followup December 20, 2002 - 8am Central time zone:
I guess I spelled it wrong ;)
Main Entry: wa·zoo
Pronunciation: (")wä-'zü
Function: noun
Etymology: origin unknown
Date: 1983
- up the wazoo also out the wazoo : in excess <we've got lawyers up the wazoo -- Steven Bochco>
http://www.m-w.com/cgi-bin/dictionary?va=wazoo
December 20, 2002 - 8am Central time zone
Reviewer: Dave from Ohio
I understand your point, which is that gap-free sequences are logically meaningless and highly
non-concurrent, no? However the point that I was helping to make is even more simple -- it is a
legal requirement in many cases to have gap-free sequences. Full-stop, that's it. We may not like
it, it may not be logical, it may not be performant, but it is the law. What software house or
development team can afford to have a policy that legal requirements can be ignored?
It was my intention that the methods I suggested should allow concurrency in generating the new
records by delaying the attempt to generate gap-free sequences until after the insert. The methods
were to be implemented as a post-creation batch job (say DBMS_JOB every few minutes) which would
not have to compete with other sessions for the invoice_number sequence, or would not have to use a
sequence at all. In either case it would be a single update process.
When someone spilt coffee on the form, you kept the damaged form to show the authorities. If an
invoice is generated incorrectly it is kept, marked as void, and regenerated under a new number.
Followup December 20, 2002 - 8am Central time zone:
The "gap free" numbers can (and should be) assigned after the fact, during a bulk process, the
closing of the books. I did not realize that is what you were also proposing. I see the next_val
in there -- that'll never work. sequences by definition promise NOT to be gap free.
Ok, someone drops the cigarrette on the form...
The form was left in the rain, totally obliterating it...
The forms blew away in a wind storm...
<any one of 1,000 other scenarios>
Legal reasons
December 20, 2002 - 9am Central time zone
Reviewer: Tony
Tom, in our country also, gap-free invoice, voucher numbers are mandatory in legal terms. Even if
we happen to generate wrong voucher, it can be cancelled. But the cancelled vouchers should be in
the system during auditing. If the voucher is hand written one , we can atleast try to convince the
auditors to some extent saying that its lost. As we use computers, we can't convince the auditors.
we can't say that performance/concurrency is the reason for not having gap-free numbers.
Followup December 20, 2002 - 9am Central time zone:
assign the numbers in a batch process AFTER the fact.
does it matter if the number is assigned right now, this instant. Or tonight at midnight.
(funny, the fact that computer wise we can assign these numbers *after the fact* makes them *ever
less meaningless*, they are useless for any purpose -- except to consume disk space of course)
Federal Regulations and Sequences
December 20, 2002 - 10am Central time zone
Reviewer: Pedro Diaz from Dominican Republic
Well, here I go...
In my country You have to match the pre-printed document number with the computer generated number,
it is restricted by laws to have a gap in any case. To make things more dificult EVERY branch
office need its own sequence and it most be GAP FREE.
We tray many ways but no one gets it done. We have:
A. SELECT MAX( NVL( invoice_num, 0) ) +1
FROM invoices
WHERE branch_office = :branch_num
B. SELECT Last_invoice + 1
FROM branch_sequences
Where branch_office = :branch_num
C. The Trigger / Package Approach:
create or replace package body invoice_sequence_pkg is
type sequence_type is table of invoices.invoice_num%type
index by binary_integer;
type branch_office_type is table of invoices.branch_num%type
index by binary_integer;
sequence_table sequence_type;
branch_table branch_office_type;
CURSOR last_sequence is
SELECT A.branch_num, NVL( max( A.invoice_num ), 0 ) last_invoice
FROM invoices A
WHERE invoice_date >='01-Jul-01'
GROUP BY a.branch_num;
procedure Init_tables is
begin
actual_sequence := 0;
sequence_table.delete;
branch_table.delete;
end;
procedure Sequence_Branch_Office is
begin
FOR branch_sequence IN last_sequence LOOP
sequence_table( branch_sequence.branch_num ) := branch_sequence.last_invoice;
END LOOP;
end Sequence_Branch_Office;
function netx_sequence( branch_num_in in invoices.branch_num%type) return
invoices.invoice_num%type is
begin
return ( NVL ( sequence_table( cbranch_num_in ), 0 ) + 1 );
end;
end invoice_sequence_pkg;
Calling it from Statement lavel and Row Level triggers in invoice table.
Its one of this correct, helpfull, or does exists any better way to do it.
Followup December 20, 2002 - 11am Central time zone:
short of moving?
you should do it like this:
UPDATE branch_sequences
set last_invoice = last_invoice+1
where branch_offset = :branch_num
RETURNING last_invoice into :invoice_number;
if (sql%rowcount = 0)
then
do the insert of 1
end if;
you want to use the UPDATE, not a select, in order to impose serialization and avoid artificial
"dups" that will cause transactions to fail.
Sequences are just that Sequential
December 20, 2002 - 4pm Central time zone
Reviewer: Jeff Larimore from Sunny Miami Florida
Great topic!
It seems clear that there will be a requirement for a geometric sequence generator (no gaps) in
some app somewhere.
Now if gaps or serialization are not important, then simply a unique random number would do.
1. Sequence - generates a series of numbers in order. -Oracle has this, its called a Sequence.
2. Geometric sequence - generates a series of numbers whereby each term differs from previous term
by same amount, ie 1,2,3 or 10,20,30., no gaps - We need this from Oracle.
Tom,
I tried once to put something like seq.nextval in the default value of a column, hoping that this
would fool Oracle into automatically grabbing the next value of the sequence if none was specified
for the column on insert.
I believe it didn't like that, but felt this was a smooth way of creating the equivalent of an
IDENTITY column.
Have you got a way to make that work?
Thanks,
Jeff
Followup December 20, 2002 - 8pm Central time zone:
you use a trigger + sequence to accomplish that.
"geometric sequence" as you call it -- you already got it:
create table gseqs ( name varchar2(30), val int );
create or replace function gseq_nextval( p_name in varchar2 ) return number
as
l_val number;
begin
update gseqs
set val = val+1
where name = p_name
returning val into l_val;
if ( sql%rowcount = 0 )
then
raise_application_error( -20001, 'No such sequence exists' );
end if;
return l_val;
end;
What is this system sequence IDGEN1$.
February 26, 2004 - 3am Central time zone
Reviewer: Saibabu Devabhaktuni from San Jose, CA USA.
Hi Tom,
I was hoping may be you could shed some light on this question:
What is this Sequence "IDGEN1$" is used for. I think
it has some relation with "ID_GENS$" table.
"ID_GENS$" table has a single row telling how many of
total id generators are in effect.
I did a google search on it, not even a single useful link, and metalink is more of a make up to
Oracle documentation. How does Oracle use this sequence IDGEN1$ and for what purpose. I understand,
we don't need to dig too deep in to internals, but it won't take anything away from Oracle. Every
other software vendors provide documentation/support or some forums way too deep in to details than
Oracle does. Is this a strategy of keeping complexity unexposed attract more people bind to it?
Followup February 26, 2004 - 10am Central time zone:
Umm, every other vendor? Really. Where is the MS internals website for sqlserver? and IBM's for
DB2 (well, they'd need quite a few actually given they have so many flavors of DB2 and then
informix and redbrick and so on -- something like 7 or 8 RDBMS's).
Let me ask you -- why do you need to know? Is it causing some issue on your system?
sequence and remote table changes
July 22, 2004 - 4am Central time zone
Reviewer: bn
Hello Tom,
Pls suggest something for our situation.
Situation:
We have same dabase schema on 2 different servers.
Loadings happen on server S1 and the schema tables are then copied onto Server S2(truncate n reload
with one-to-one mapping).
In a general situation Web interfaces work with S2 and log table entries happen from interfaces.
Our plan is to be able to use S1 when S2 cant be available.
To achieve this we have to make sure that
1. Log table entries are to be made in both S1 and S2.
2. Sequence numbers of the log table entries should be same on both S1 and S2.
3. When one goes down and comes up, the changes on the other server during the down time are to be
copied to keep the data in sync.
My plans is
1) I use insert/update over db link.
2) In my opinion i can use the following .
update gseqs
set val = val+1
where name = p_name
returning val into l_val;
I think if i use database sequences i cant keep them in sync.
3) Run some stored procedures to copy the changes occured during the down time.
Pls make corrections/suggestions or suggest alternatives.
Also throw light on potential pitfalls.
TIA
Followup July 22, 2004 - 7am Central time zone:
you want to use data guard if your concept is to have a DR/failover site.
do not attempt to "do it yourself", this technology is well known, long in the tooth, and already
written.
use database sequences -- don't even consider doing it yourself.
sequence and remote table changes
July 22, 2004 - 9am Central time zone
Reviewer: bn
Thanks for the comments TOM.
As per the little understanding i have "data guard" needs to have standby database and we are going
in that direction soon.
Currently our application( office hours only) is not a very critical one.
Both datawarehouse and datamart are on the same database (9ir1).For some reasons our strategy of
loading Datamart is truncate/reload which takes 2-3 hours.If the loading batch starts late users
cant have data until its complete.
To avoid such long delay we plan to have one another database for Datamarts on a different
server.This will be loaded from the existing datamart. The loading is one to one as business logic
is already applied and it may take 10 minutes to truncate and reload.
In this way the time is cut down from 2-3 hours to 10 minutes.
I hope i made you understand our environment.
Since we will have data available in two places we thought of designing something to keep the them
entirely in sync, i.e by ensuring that all the changes done
by the Web interface are written into both servers.
This kind of standby system( in some sense) is not very critical to our system but as an add-on.
So i think i will have to build some logic to achieve this.
Pls provide your comments on my last posting in new scenario.
I failed to understand why you said "use database sequences -- don't even consider doing it
yourself."
When should we use
update gseqs set val = val+1
where name = p_name
returning val into l_val;
TIA
Followup July 23, 2004 - 7am Central time zone:
if you build logic, you are on your own.
if you want it done for you instantly, dataguard. you have totally described to me "i want
dataguard, i want a standby database for failover"
using your own database sequences = i will serialize all of my transactions making Oracle appear to
be the slowest database on the planet. It is, will be, and has been the worst idea -- use
sequences, use proper techniques -- seriously. standby technology exists, you own it, it is there
- just start using it.
short of that, use read only replication -- and if sequences are an "issue", simply:
create sequence s start with 1 increment by 10; <=== at site 1
create sequence s start with 2 increment by 10; <=== at site 2
and then they will generate their own unique streams of numbers without overlaps.
If no gaps are permitted for legal reasons...
July 22, 2004 - 10am Central time zone
Reviewer: Adam Musch from Omaha, NE, USA
... then fill them in as part of the close process.
Assuming the example of invoice numbers, one would know both the first and the last valid invoices
for the financial period. It should then be simple to fill in the gaps.
SQL> create table invoices (invoice_id number, invoice_type varchar2(10))
2 /
Table created.
SQL>
SQL> insert into invoices
2 select rownum invoice_id, 'VALID' as invoice_type
3 from all_objects
4 /
13820 rows created.
SQL>
SQL> alter table invoices
2 add constraint pk_invoices
3 primary key (invoice_id)
4 /
Table altered.
SQL>
SQL> -- find valid ranges of invoices
SQL> select min(invoice_id), max(invoice_id) from invoices
2 /
MIN(INVOICE_ID) MAX(INVOICE_ID)
--------------- ---------------
1 13820
SQL>
SQL> -- create gaps in invoice range
SQL> delete from invoices
2 where mod(invoice_id, 20) = 0
3 and invoice_id != (select min(invoice_id) from invoices)
4 and invoice_id != (select max(invoice_id) from invoices)
5 /
690 rows deleted.
SQL>
SQL> -- fill gaps in invoice range
SQL> declare
2 l_min_invoice_id number;
3 l_max_invoice_id number;
4 begin
5 select min(invoice_id), max(invoice_id)
6 into l_min_invoice_id, l_max_invoice_id
7 from invoices;
8
9 for i in l_min_invoice_id..l_max_invoice_id
10 loop
11 begin
12 insert into invoices
13 values (i, 'VOID');
14 exception
15 when DUP_VAL_ON_INDEX then
16 null;
17 end;
18 end loop;
19 commit;
20 end;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> select invoice_type, count(*)
2 from invoices
3 group by invoice_type
4 /
INVOICE_TY COUNT(*)
---------- ----------
VALID 13130
VOID 690
SQL> select min(invoice_id), max(invoice_id), count(invoice_id)
2 from invoices;
MIN(INVOICE_ID) MAX(INVOICE_ID) COUNT(INVOICE_ID)
--------------- --------------- -----------------
1 13820 13820
Functionally gap-free. There's almost certainly a more elagant way to fill the gaps than I did,
but it suffices for the purposes of this example.
sequence and remote table changes
July 26, 2004 - 10am Central time zone
Reviewer: bn
Thanks again TOM ,
Certainly "dataguard" would be the best. But when i refresh the datamart(truncate/reload) should i
do it on two databases or "dataguard" will take care of it in standby database !!
I still dont get convinced of its necessity in our case. Out of 5 schemas in the database only one
schema needs to be kept in sync. That too 3-4 tables only. Pls comment.
You suggested "read only replication" .. is it same as "read only snapshots" !!! pls corrent if
wrong.I find several threads regarding it. May be that will fit us.
I think the solution you suggested regarding sequence will not work for us because our requirement
is that site1 and site2 should have same sequnce for the same record.Is there any other way !!
There will be hardly 1000 transactions a day.If there is no other way, then using a table record
may not be a big overhead. Pls comment on it.
I was curious about where to use the following solution which was suggested by you in this same
thread.
------
update gseqs
set val = val+1
where name = p_name
returning val into l_val;
------
TIA
Followup July 26, 2004 - 11am Central time zone:
dataguard deals with that.
logical standby can selectively do certain tables.
replication certainly does selective tables.
snapshots = readonly replication = materialized views.
site1 and site2 would have the same sequence id's for records -- it is just that a sequence
generated at site1 would be different from, unique with regards to, a sequence generated at site2
-- but if site1 created a record and it was replicated to site2 -- it would have the same id of
course.
sequence and remote table changes
July 27, 2004 - 10am Central time zone
Reviewer: bn
TOM,
Thanks for clearing sequences issue at site1 and site2.
I started going through Oracle9i Streams concepts.
Looks very good for my case and apprears to be alternative to snapshots.
Pls advise on using streams/snapshots.
TIA
Followup July 27, 2004 - 12pm Central time zone:
if you can use streams -- it is the preferred method going forward.
Assigning new field id inside trigger
October 21, 2004 - 10am Central time zone
Reviewer: Teymur Hajiyev from Azerbaijan
Dear Tom.
An application inserting records into t1. Now I want add new field and set it to seq.nextval. Below
is my trigger:
CREATE OR REPLACE TRIGGER "My"."Test_Ins" BEFORE
INSERT ON "My"."TEST" FOR EACH ROW
declare
seq_num number;
BEGIN
select f1_rowid.nextval into seq_num from dual;
update test set RID = seq_num where rid is null;
END;
--------
It work, but there is problem:
Assume that there is no any record:
Now after writing trigger, I inserted new record:
RID F
1
But RID didn't changed.
Now adding new record:
RID F
1 1
2
Oops, RID now changed, but not inserting record, record which inserted before.
Thanks in advance.
Followup October 21, 2004 - 2pm Central time zone:
boy, that is the wrong way to do this -- way way wrong way.
try this:
insert into t select null, null from dual;
opps - mutating table. (and do you really want to FULL SCAN this table each time to update?????)
(you don't really have a schema "My" do you? mixed case would be a horrible terrifically bad idea)
CREATE OR REPLACE TRIGGER test_ins before insert on test for each row
begin
select f1_rowid.nextval into :new.rid from dual;
end;
before jumping head first into Oracle from sqlserver -- you might want to read:
a) concepts guide
b) application developers guide
you won't regret the hours it takes to at least skim them -- never.
Skipping sequences
December 23, 2004 - 12pm Central time zone
Reviewer: J1
Tom,
Why does sequences skip once in a while? What is the logic behind that? Is there any init
parameter for that?
Happy Holidays!
Thanks.
Followup December 23, 2004 - 12pm Central time zone:
sequences have a default cache (10 -- you should seriously consider upping that to 100 or 1000)....
so, you say "select s.nextval from dual" -- that'll get 1..10 loaded up in the SGA. You get 1.
2..10 are still there.
If
a) you shutdown abort -- 2..10 gone
b) have the sequence get aged out of the shared pool -- 2..10 gone
c) select s.nextval, select s.nextval, rollback -- 2..3 gone.
for example.
sequences are never never never going to be gap free, just unique.
December 23, 2004 - 2pm Central time zone
Reviewer: J1
Seg.nextval
February 9, 2005 - 1am Central time zone
Reviewer: Anil from Dubai
Hi Tom
Which is better
CREATE OR REPLACE TRIGGER test_ins before insert on test for each row
begin
select f1_rowid.nextval into :new.rid from dual;
end;
or
CREATE OR REPLACE TRIGGER test_ins before insert on test for each row
begin
:new.rid:= f1_rowid.nextval;
end;
Rgds
Anil
Followup February 9, 2005 - 2pm Central time zone:
well, since only one of them will compile -- I'll pick the one that doesn't compile -- the 2cd one.
it won't compile and that is good, because the "best" way is:
insert into test_ins( rid, .... ) values ( f1_rowid.nextval, ..... );
short of that, the first one looks like it will compile...
Sequence
February 10, 2005 - 1am Central time zone
Reviewer: Anil from Dubai
Sorry for that Tom, I should have compiled it before sending it.
But to set the new values only way is
select poc.nextval into :new.XTRASNACTION_ID from dual;
since I cannot use the following to set the new value in a trigger!!
insert into test_ins( rid, .... ) values ( f1_rowid.nextval, ..... );
Sorry once again ,
Thanks & Rgds
Anil
Followup February 10, 2005 - 2am Central time zone:
i'm trying to imply that the trigger is not to be used at all
the BEST, most performant way is for the insert to actually supply the value.
It is clear in the code that does the insert what is happening
the database will do less work
the trigger is not needed or really even "desirable" here.
Sequence
February 10, 2005 - 5am Central time zone
Reviewer: Raghav from India
Hi Tom,
While the system generated sequence is used, and there is a some time gap while the next number is
generated (suppose for example 10 minutes) shared pool (where sequences are cached) are aged out.
When the next number is it has left 20 numbers and generated the next sequence number. Like in the
prior case as it has given a 5 numbers difference. Is there any specific number it lefts out. Like
in his example it is 5 and in my example it is 20?
We have got this problem in the beginning of the implementation of our software and now to come
across this problem what we are using is we are maintaining a table having transaction type and
last sequence number and while every transaction being inserted, it will be updated from the this
table using the last generated sequence number.
In your view, what is the best method. Using the system generated sequences or as we have used in
our system.
Followup February 11, 2005 - 2am Central time zone:
why is it "a problem".
fact: sequences have gaps
fact: you cannot change prior fact
fact: even if you generated or lost thousands of sequence values per second it would take many
millions of years to exhaust them
use a sequence. (actually, alter your sequences to cache 1000 and get better performance and don't
lose sleep over "gaps" - gaps are a fact)
Sequences
February 10, 2005 - 5am Central time zone
Reviewer: Anil from Dubai
Yes Tom, In this context sql is the right approach. But setting a value for audit column, say
created,by created_date etc.. when sqls are generated from a middle tier and not using store
procedure we have no other option but to use before trigger.
Thanks & Rgds
Anil
Followup February 11, 2005 - 2am Central time zone:
sure -- but you see, i was answering the other question about how best to get nextval in there, not
how best to do something unrelated.
for insert - the sequence should be in the sql.
the "created_date" would be defaulted to sysdate
created_by defaulted to user
and no trigger would be necessary for insert.
Sequence
February 11, 2005 - 6am Central time zone
Reviewer: Anil from Dubai
Right Tom, But default has a limitation. as you said I can use it for setting the created date.
"A DEFAULT expression cannot contain references to PL/SQL functions or to other columns,"
If I want to set the username(not oracle username, but application username which is in a context
variable set by the application) I can't do this since default does not support user defined
function.
Also what about update. So if the SQL is not providing the updated_date we have no other choice at
the moment other than a before trigger.
Thanks & rgds
Anil
Followup February 11, 2005 - 8pm Central time zone:
sequences
February 11, 2005 - 6am Central time zone
Reviewer: Anil from Dubai
Hi Tom
" not how best to do something unrelated.'
Sorry for posting a unrelated question. PLeas ignore my latest posting. I am very clear with that.
Thanks once again
rgds
Anil
Oki-doki, how about this one...
March 30, 2005 - 7am Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
I really agree with the most things you are talking about, but real life is throwing new & new
problems where not all can be done in the way we wish...
A little more about this "gap-free/not-gap-free stuff":
our company is working for one goverment organization, that is registering emmm... i'm a little
unsure how translate it into english, let it be "enterprises". And this organization wants to
generate numbers for these enterprises, even created standard for this: number is generated from
10-digit code of territory & 4-digit number of enterprise (yes-yes-yes, number of territory is also
stored in some other place too). This last 4-digit number is divided into two intervals 0001..8999
for physical persons & 9000..9999 for juridical (not sure about terminology) persons.
So, when we create new enterprise we even not simple look for max(num+1), we search for first
"free" number (because where can be "gaps" & they really mean something, because of tight bounds of
allowed numbers) for this territory, check physical\juridical status & create new number...
So, the question is - are there some ways to increase scalability of system even a little more &
decrease number of conflicts or there is no such way, that not involves firing half of our
developers, 90% of architectors and rewriting few laws. We can not dynamically create numbers with
the view, because number of enterprise must be unchanged "from birth to death"...
Sorry, for bothering with such perhaps stupid or just ananswarable question...
PS It is related to the previous theme by the thought like: "...perphaps some time people really
need gap-free sequences... or no?.." ;)
Followup March 30, 2005 - 8am Central time zone:
what is the business requirement behind this want.
All you can do is explain to the business the ramifications. Now given that you have but 9,999
numbers per territory, this won't be a blazingly high volume request so perhaps serialization at
the territory level is ok for them (I mean with 9,999 maximum enterprises per territory key, it
probably isn't going to be much of a scaling issue, territory is the big number -- 6 digits, within
a territory, you need to serialize)
logic:
lock territory parent record
search for 4 digit number
Non-intelligent keys, anyone??
March 30, 2005 - 9am Central time zone
Reviewer: Greg from Toronto, Canada
Just to add a follow-up to the previous post:
Dmytro, why wouldn't you create a (proper?) non-intelligent PK using a sequence .... and yes,
you'd have gaps ...
Then create another column for these "business-requirements"?? Then you can assign them just about
anything you wanted, and still be assured you have an reliable, internal id for each row of each
table ...
Not sure if that's what Tom's suggesting or not .. but I see this whole "Voucher can't have gaps"
issue as: "Hey, the voucher id shouldn't be the PK if it's being *used* .. that defies the
definition of a PK (from what I understand it ... )
Isn't a PK supposed to be a "non-intelligent" value? That is, a value that is not used by the
business? Not given out to anyone .. only used internally .. for things like Foreign Keys,
identifying a record, etc?
If needed, you can still have a unique index on that other column that the business needs ... if
the business requirements are that it must be unique .. ;)
I guess that still doesn't solve the problem of "hey we have a business requirement of this column
- it can't have gaps" .. but that's not a problem to solve with sequences, now, is it??
;)
Followup March 30, 2005 - 10am Central time zone:
(i would prefer the surrogate key and then the other attributed be assigned attributes, but you
have the same serialization issues, but at least we could move someone from territory 1 to 2
without massive cascades)
Come to think about it...
March 30, 2005 - 9am Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
...yes, you are right, of course... :) It's hard to think, that it will be much a problem if once
... let's say in few weeks ... some girl with very-very long manicure ;) will be waiting for a few
moments during registering process of the next enterprise.
Perhaps my testing environment was a little more demanding than her... :) But still there are
these awful GUID numbers as PK in almost every table (even in some not-very-big dictionaries!!!)
and many other stuff, sigh <gone complaining>...
PS Sorry for bothering you and thanks for advice, great as ever... ;)
In fact...
March 30, 2005 - 9am Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
...if there was no requirements for uniquiness, there would be no problems - no locking needed, no
"duplicate" problem - just insert it and that's all. Problem is in generating new value for this
field, that can be generated by somebody else in the same moment. But idea to have separate PK is
interesting...
Problem with Sequence After Import
August 24, 2005 - 7am Central time zone
Reviewer: Richard from Buckinghamshire, UK
Oracle8i Release 8.1.7.4.0 - Production
1. export from database "A" performed using owner=foobar - no warnings
2. import into database "B" performed using dump file from above export of "A" - no warnings
3. Upon using database "B", primary key violation occurs when an attempt is made to insert a row
into table myTable, owned by foobar. A sequence belonging to foobar is used to generate primary key
values for myTable. So, it looks like the import wrote the sequence to the dump file, and then
wrote myTable, thus leading to the imported sequence lagging behind the imported table's data.
Would using consistent=y in the export statement solve this?
Followup August 24, 2005 - 11am Central time zone:
consistent=y would give you a view of the database at a single point in time, so yes, it would be
as if all sql were run at the same exact instant.
Excellent, thank you!
August 25, 2005 - 3am Central time zone
Reviewer: Richard from Buckinghamshire, UK
Sequence no
November 26, 2005 - 7am Central time zone
Reviewer: AD from UK
Tom,
Can you please share your views on the following:
I have a requirement where by I have to produce an extract file on a regular basis(monthly), and
each time I create the file the sequence no (one of the field in the file) should be incremented by
1. I am wondering whether I shall be using a sequence no as provided by oracle or
creating/maintaining my own. The only issue is the program should be re-runnable, so in case of an
error the file should be re-created but the sequence no should be adjusted accordingly. The
sequence no should be gap free e.g. you should generate 1, 2, 5, 7 but it has to be 1, 2, 3, 4, and
so on.
Many thanks in advance
Followup November 26, 2005 - 12pm Central time zone:
sequences in Oracle (in any database that supports a concept of a surrogate key generator) and
never gap free. They will not be incremented by 1.
please clarify
January 10, 2006 - 5am Central time zone
Reviewer: Kumar from Pune,India
SQL> create sequence s
2 /
Sequence created.
1) SQL> select s.nextval,s.nextval,s.nextval from dual
2 /
NEXTVAL NEXTVAL NEXTVAL
---------- ---------- ----------
1 1 1
1 row selected.
I thought sequence values would come as 1,2,3.
But why it returns like this??
2)
SQL> select s.nextval from dual
2 union all
3 select s.nextval from dual
4 union all
5 select s.nextval from dual
6 /
select s.nextval from dual
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
why this statement fails??
Question about Oracle Projects Draft Invoices
January 19, 2006 - 6pm Central time zone
Reviewer: A reader
I read this thread about gaps and wanted to get your take on Oracle Projects and how they generate
draft invoices. I realize there is a very good chance that just because you work for Oracle you
have never used it and know nothing about it. But here goes anyway.
In Oracle Projects, the draft invoices table (pa_draft_invoices_all) has a composite primary key of
project_id and draft_invoice_num. The draft invoice number ALWAYS starts at 1 for every project,
and is ALWAYS gap free for each project. If you cancel an invoice, it just sets a flag and the
next invoice continues on with the next number. This is stored that way in the base table itself,
and not through a ranked analytical function in a view. Any views that the forms use just select
from this column directly as well (so they are not doing anything special).
Could you shed some light as to why they decided to go with a gap-free solution instead of
implementing this in views? I'm just curious because you always question the business
justification for a solution like this, and Oracle itself has implemented it. Obviously Oracle
Applications would be used in a setting with thousands of users - so why do you think they chose to
serialize this process? Since all of the forms are built off of views, why not just create a
sequence and build the gap free number in the view using analytics?
Secondly, I was curious if you knew how they actually do it. The draft invoice number gets
assigned at the time an invoice is created, so it must lock the table somehow. I was just curious
for my own knowledge more than anything.
Hopefully this made sense! Keep up the good work.
Sequences
March 7, 2006 - 12pm Central time zone
Reviewer: Steve from UK
Tom.
Like it or not, it is sometimes desirable to have a geometric sequence (in the general sense of the
word, not the Oracle sense).
Cases in question: where it is legally binding to have a continuous sequence; where you rely on a
number for serialisation purposes (e.g. over some kind of IPC) - a gap in such a sequence
indicating an error or omission.
My cheques come numbered in order, and I have never seen a gap. If a cheque is voided by me, it
still has the number. It is just never used and never appears on my statement. The number was
however initially generated.
Hypothesising, I would imagine that in most scenarios of the "coffee on paper" example, important
documents would not be allowed to just be thrown away with no kind of audit trail, even if they had
had coffee spilled on them. If they had been inadvertently burnt with a cigarette, I would imagine
this would involve a business process to account for such a missing form, and one that (a) you
wouldn't want to repeat frequently or (b) involved some kind of reprimand.
In Oracle terms, a sequence is used as you say to generate a *unique* number, not a gap-free
numerical sequence. This does not mean that every possible context in which one wishes to use such
a numerical sequence is invalid because of Oracle's implementation. If my remit is to provide a
continuous integral sequence, that is what I must do.
Out of interest, our workaround is messy and necessary, and involves us using our own sequences
which contain gaps, and having to identify when a gap is legitimate (e.g. rollback transaction) or
not legitimate (a table has been cleansed of old data leaving gaps). Using SQL Server would have
spared all these problems due to the IDENTITY type.
Followup March 8, 2006 - 4pm Central time zone:
I have never seen a true "legally binding" thing - for you see, coffee would get spilled in the
past before computers.
Have you ever lost a check book? I have.
Have you ever voided a check? I have.
Have you ever spilled something ruining some checks? I have.
You just gave the perfect example.
Double Sequences
March 8, 2006 - 6pm Central time zone
Reviewer: Martin from Columbia, MD
Hi Tom,
I have seen this question many times over the years. I thought I would give a solution I have used
over the years when record id's could not be lost.
The process is not very efficient, but is very effective. I also do not recommend this for primary
keys but works well for unique business logic type columns(check numbers, po numbers)
CREATE TABLE CHECKS
(CHECK_KEY number(10) primary key,
CHECK_NO number(10) unique,
CHECK_STATUS char(1) default 'T',
AMOUNT number(10,2));
CREATE SEQUENCE CHECK_NO_TEMP_SEQ
start with 1000000
cache 20;
CREATE SEQUENCE CHECK_NO_PERM_SEQ
start with 2000000
nocache;
How it works:
We use a before insert trigger in the checks table
to give the CHECK_NO a value from the TEMP sequence and we set the STATUS to 'T' or whatever to
indicate temp sequence was used. Let the database do what it wants. Skip whole sets of numbers,
whatever.
Part 2 can be accomplished any number of ways including a job that runs several times per hour -
or even a nightly batch job(best technique).
This job then takes the records that have a STATUS of 'T' and replaces the TEMP sequence with a
number generated by the PERM sequence. If perm process fails, dba gets notified and has to take
corrective actions.
The users can easily spot the temp and perm numbers because the order of magnitude and/or
visibility of the STATUS flag.
Followup March 9, 2006 - 1pm Central time zone:
sorry, I will continue to disagree with the very premise that gaps cannot be tolerated.
For all of HUMAN HISTORY they have been. Fact.
sequence gap free
March 9, 2006 - 12am Central time zone
Reviewer: Umesh Kasturi from Bangalore , India
I do agree with you that sequences can never be gap free.
My client has 10 different letter_type which are stored in same table and he wants to maintain the
"sequence of numbers " for each letter_type so that by looking at the number he can say how many
letters are there in a letter_type?
primary key is (letter_type,letter_number)for table "all_letters" holding all the letters.
should I go for another table "letter_seq_master" as below to capture the last seq_value for a
particular letter
"letter_seq_master"
letter_type last_seq_value
---------------------------
ordinary 11
special 9
extra 4
when he receives the next ordinary letter he wants to store ord and 12
when he receives the next special letter he wants to store spl and 10
when I insert a letter into all_letters . I pick up the value from "letter_seq_master" and populate
the next letter number
should i follow this approach or just have "gapped" sequence numbers across the letter types
Followup March 9, 2006 - 1pm Central time zone:
I'd rather either
a) compute that number as he requests it (on the firm belief they won't request it very often!!!)
b) maintain a rollup via a materialized view with the aggregate count.
clients do not design databases.
clients have business requirements (I need to know how many....), WE implement physical designs to
satisfy that.
Use a view people!! You don't need a sequence!!
March 9, 2006 - 9am Central time zone
Reviewer: Greg from Toronto, Ontario
Seriously ... if you need "gap free" ... consider using a view .. something like this:
gregs-DEV > drop table cheque;
Table dropped.
gregs-DEV >
gregs-DEV > create table cheque
2 ( cheque_id number,
3 account_num number,
4 cto varchar2(1000),
5 cfrom varchar2(1000),
6 amount number )
7 /
Table created.
gregs-DEV >
gregs-DEV > alter table cheque add constraint cheque_pk primary key ( cheque_id )
2 /
Table altered.
gregs-DEV >
gregs-DEV > -- I already created this .. no big deal . just got bigger numbers to show you ...
gregs-DEV > create sequence seq_cheque_ids nocycle nomaxvalue
2 /
create sequence seq_cheque_ids nocycle nomaxvalue
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
gregs-DEV >
gregs-DEV > create or replace view v_cheque
2 as ( select cheque_id,
3 account_num,
4 row_number() over ( partition by account_num order by cheque_id ) + 100
cheque_num,
5 cto,
6 cfrom,
7 amount
8 from cheque )
9 /
View created.
gregs-DEV >
gregs-DEV > -- populate with some random data ..
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > -- create gaps in the sequence
gregs-DEV > rollback;
Rollback complete.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > commit;
Commit complete.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > rollback;
Rollback complete.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > insert into cheque
2 values ( seq_cheque_ids.nextval, trunc ( dbms_random.value ( 100000, 999999 ) ),
3 dbms_random.string ( 'a', 20 ), dbms_random.string ( 'a',
20 ),
4 dbms_random.value ( 10, 1000 ) )
5 /
1 row created.
gregs-DEV > commit;
Commit complete.
gregs-DEV > -- duplicate a few records/accounts so we can see the cheque numbers ...
gregs-DEV > insert into cheque
2 ( select seq_cheque_ids.nextval, account_num, dbms_random.string ( 'a', 20 ), cfrom,
dbms_random.value ( 10, 1000) from cheque )
3 /
4 rows created.
gregs-DEV > insert into cheque
2 ( select seq_cheque_ids.nextval, account_num, dbms_random.string ( 'a', 20 ), cfrom,
dbms_random.value ( 10, 1000) from cheque
3 where rownum < 3 )
4 /
2 rows created.
gregs-DEV > commit;
Commit complete.
gregs-DEV >
gregs-DEV > -- and viola ... presto - chango!!
gregs-DEV > break on account_num skip 1
gregs-DEV > select * from v_cheque order by account_num, cheque_num;
CHEQUE_ID ACCOUNT_NUM CHEQUE_NUM CTO CFROM
AMOUNT
---------- ----------- ---------- ------------------------------ ------------------------------
----------
168 195618 101 NGXkjEHpNnWFWo_qaAQR G[jeGatg`ngiLP\H_WLr
910.618819
172 102 TLYIcNbF`iIUVmWpCi^a G[jeGatg`ngiLP\H_WLr
732.945593
163 845567 101 rgMdGYMtJeNofJ\tgoOW WFYs]GkfUhhYOrEiIrTL
495.269658
169 102 ^SflYRgHfI[J]kUhX]Us WFYs]GkfUhhYOrEiIrTL
873.013064
173 103 PaGNkGOQftgUeqEjkDSn WFYs]GkfUhhYOrEiIrTL
701.278641
167 931387 101 `NN_mEBK`U`tLISpGa\E mfR[PQdCHMPqoUFGUkTd
756.177798
171 102 iV]IsgE\]PpWE\_gIPKO mfR[PQdCHMPqoUFGUkTd
727.31892
164 968983 101 XLOahdWP\cUhjZnCV\MA YYcScnHoLLmgcefLcsWR
406.852995
170 102 dYIsMAQkn]_cDstGWNSI YYcScnHoLLmgcefLcsWR
241.576482
174 103 qfint_TPiB[olbC_Wtsb YYcScnHoLLmgcefLcsWR
831.687898
10 rows selected.
gregs-DEV >
Tom .. can I say it??? Can I?? Please, please, please!??
:)
Analytics Rock & Roll!!
:P
SEQUENCE
March 29, 2006 - 5am Central time zone
Reviewer: Pradikan from India
Hi TOM,
We have a typical requirement. I've a situation where only the sequences needs to be copied to a
different instnace / schema. Using export I can do at table level, user level, tablespace level or
full database level. But is there any way where i can export only the sequences and import them to
a different schema.
Thanks in Advance
Pradikan
Followup March 29, 2006 - 7am Central time zone:
not via export and import.
You could easily write a script that creates a "create sequence script" - it would take seconds to
develop.
Alternative for the insane...?
April 13, 2006 - 8pm Central time zone
Reviewer: Asher Cerka from IL USA
Create table t (t number(22))
Insert into t values (1)
Create or replace function Get_Next_T return number
IS
NextT number(22);
Begin
select max(t)+1 into NextT from t;
If NextT is null Then
return 1;
Else
return NextT;
End If;
End;
Insert into t values (Get_Next_T())
Will never skip a value, because the value that it gets is always the next one in the sequence. Not
safe for multiple users, because 2 users can both get the same value before the other manages to do
their insert. (need to lock the table to get around that, unless you can think of an alternative.)
Also, if t is unindexed then it has to do a full table scan every time you want to insert a value.
If I index it, it does a 'index full scan (min/max)' which unfortunately with my limited knowledge
of explain plans means little to me. It sounds like it's doing a full-scan of the index... which
would seem silly as the highest value is obviously going to either be at the top or the bottom.
I'm unsure of how much effect this would have performance-wise, and unless an alterative can be
found to locking the table every time you need to do such an insert... to me it's nothing short of
insanity. But... legal people make others do more insane things then this. :) So... it could be
made to work if absolutely neccessary I believe. No matter what, in order to generate a truely
gap-free number-set the next number has to be somehow depending on the current last ID on a table.
Followup April 14, 2006 - 12pm Central time zone:
that would not fly at all in a multi-user environment. serialization would be the word of the day.
it would read just the end of the index - but you would have to serialize before doing so.
Sequence number with no gaps
May 4, 2006 - 6pm Central time zone
Reviewer: A reader
Tom,
Instead of using an Oracle sequence number, can we use Oracle rownum to generate sequence numbers
with no gaps (Run an update statement on the column where a sequence number with no gap is
required)?
Thank you
Followup May 5, 2006 - 1am Central time zone:
you can come back and assign unique gap free numbers to rows, yes, if you wanted to.
Using rownum as a unique sequence
May 5, 2006 - 3pm Central time zone
Reviewer: A reader
Tom,
1) Do you know or see any limitations or scalability issues with this approach (using rownum)?
2) Does rownum implementation change with oracle db versions?
Thank you
Followup May 5, 2006 - 3pm Central time zone:
I likely would not use rownum, it could be unpredicable (same data, different rownums assigned).
You would want to use something repeatable I would think.
sql loader header
May 5, 2006 - 4pm Central time zone
Reviewer: jhon edison b.c from Colombia
Hola since as,like all: The truth almost not groin but I expect they understand me a bit. I have
the following problem, desire To load a base of datas information using sqlloader but not since,
like before the load to add her(it) Head-board of the record, in this case the date: the file is of
the following form:
clamasiva8.log; C:\clasmas; 30; Clasificacion ; 172976; 182510; 2;
8490073; ; 1; 0; 24; AAA;
clamasiva8.log; C:\clasmas; 30; Clasificacion ; 68901; 73461; 2;
8490075; ; 1; 0; 24; AAA;
30-01-2006;
clamasiva3.log; C:\clasmas; 28; Clasificacion ; 2523; 2616; 2; 8849387;
; 1; 0; 1; AAA;
clamasiva3.log; C:\clasmas; 28; Clasificacion ; 9923; 10510; 2; 8861563;
A; 1; 0; 4; AAA;
clamasiva3.log; C:\clasmas; 28; Clasificacion ; 36988; 39030; 2;
8845339; ; 1; 0; 1; AAA;
26-01-2006;
It must be this way:
clamasiva8.log; C:\clasmas; 30-01-2006; 30; Clasificacion ; 172976; 182510;
2; 8490073; ; 1; 0; 24; AAA;
clamasiva8.log; C:\clasmas; 30-01-2006; 30; Clasificacion ; 68901; 73461; 2;
8490075; ; 1; 0; 24; AAA;
clamasiva3.log; C:\clasmas; 26-01-2006; 28; Clasificacion ; 2523; 2616; 2;
8849387; ; 1; 0; 1; AAA;
clamasiva3.log; C:\clasmas; 26-01-2006; 28; Clasificacion ; 9923; 10510; 2;
8861563; A; 1; 0; 4; AAA;
clamasiva3.log; C:\clasmas; 26-01-2006; 28; Clasificacion ; 36988; 39030; 2;
8845339; ; 1; 0; 1; AAA;
Thank you:
att: jhon edison B.C Colombia
Followup May 5, 2006 - 4pm Central time zone:
Ok, I'll bite - where do the dates come from exactly - what "logic" produced them?
It is great
August 8, 2006 - 7am Central time zone
Reviewer: Subhrajit Bandyopadhyay from INDIA
I agree with Tom 200%.. there is no meaning of gap-less unique ID's numbers. Even if some
government depts like Income Tax etc are asking to keep each and every paper with serially numbered
... they should understand that it doesn't helps them ... because when papers were used then ther
may be some legal issues in case a numbered form is lost ..it may be misused ..so there was a
threat.. but in case of Computerizewd system generated forms no one can missuse it ..how can he???
because its not there actually.
Using Seuqences allows real concurrent transactions.
Sequnce value retrieval swells Rollback Segment
November 8, 2006 - 3pm Central time zone
Reviewer: Sanji from Shelton, CT
Tom,
Following is the trigger body which when invoked causes Rollback segment extend failure.
More than anything else, i'm wondering why would this cause rollback segments to go out of space.
(The env. is Oracle 9i on HP-UX 11i and the database is still configured for Rollback segments as
against to UNDO.)
CREATE OR REPLACE TRIGGER "COGNOS"."TRG_SEQ_XOCUBEFACT_ROW_ID"
BEFORE INSERT ON "COGNOS"."XOCUBEFACT" FOR EACH ROW
begin
SELECT SEQ_XOCUBEFACT_ROW_ID.NEXTVAL INTO :NEW.ROW_ID FROM DUAL;
END;
Thanks
Sanji
Followup November 8, 2006 - 8pm Central time zone:
well, i hate triggers like that...
but triggers generate extra undo
the recursive sql used to update the seq$ table will too.
Suggest you:
alter sequence seq_xocubefact_row_id cache 100000;
really suggest you:
drop trigger;
and put the seq.nextval right into the insert too.
any why of rolling back the sequence number to the previous
November 9, 2006 - 1am Central time zone
Reviewer: A reader
Followup November 9, 2006 - 8am Central time zone:
why???
sequences are not gap free
sequences are not gap free
sequences will never be gap free.
assume this order of operation:
session1: give me a sequence (they get 42)
session2: give me a sequence (they get 43)
session1: rollback
to what value could you POSSIBLY put a sequence "back" to - it doesn't even make sense in that
context.
Regarding Oracle Sequences
November 16, 2006 - 5am Central time zone
Reviewer: Hemal from India
Hello Tom,
I am creating a data entry application in which user will enter the number of Clients visited to a
nursing home
in a particular month.
I need your advice to implement the following mentioned logic for generation of client code using
Oracle Sequences.
Client code is the primary key of the database table in which client information is stored.
Suppose a client visited the nursing home in January 2007, the client code is having following
format "P07A001"
Out of this "P" is hardcoded.
07 stands for year "2007".
A - Standands for January, (If client visited in February then this would be "B",If client visited
in March then it would be "C" ..If client visited in December then it would be "L").
001 is the running number.(This number is reset back to 000 in the next month).
For the 2nd client in the January 2007 the client code will be 'P07A002', 3rd client in the
January 2007 the client code will be "P07A003".
In case of February 2007,first client who visits the nursing home will have client code as
"P07B001",
Second client who visits the nursing home will have client code as "P07B002".
I would like to know as whether I can use Oracle Sequences for generation of Client Codes in such a
format.
If not what will be the best method of implementing this logic.
Your help is highly appreciated.
Thanks and Best Regards
-Hemal
Followup November 16, 2006 - 3pm Central time zone:
you have made the most fatal mistake of your database career.
you are encoding multiple data fields into on.
Just stop that right now, don't do that.
P is not needed, it is a constant.
2007 should be a date field else where, a standalone attribute.
A, B, C, ... is beyond "not smart". 2007 and the month are a single attribute, the date
and the client id is just a surrogate, without any reseting
Please remember, this is NOT dbase, this is real life.
stop doing this, please please please - this is so wrong.
Min Value in sequences
November 16, 2006 - 4pm Central time zone
Reviewer: Maverick
Tom, what is the use of MinVal option while creating sequences? Start with, Max val,Increment by
..pretty much takes care of everything that's needed [or may be i'm missing sonething]
Thanks,
Followup November 16, 2006 - 4pm Central time zone:
ops$tkyte%ORA10GR2> create sequence s start with 9999999 increment by -1 maxvalue 9999999999
minvalue 1;
Sequence created.
ops$tkyte%ORA10GR2> select s.nextval from dual;
NEXTVAL
----------
9999999
ops$tkyte%ORA10GR2> select s.nextval from dual;
NEXTVAL
----------
9999998
they do not have to go up
Regarding Oracle Sequences
November 17, 2006 - 4am Central time zone
Reviewer: Hemal from India
Hello Tom,
This is in continuation with my question with title "Regarding Oracle Sequences November 16, 2006
" on this link.
Thanks for your reply.
I totally agree with you, but the problem is the users who needs this Nursing Home Application
requires the Client Codes be generated in the same format ("P07A001").Users are using this client
code format from years and therefore they are not ready to change it.
Can you please help me / guide me as what will be the best way of generating the client codes in
this format ("P07A001").
Is it possible by using oracle sequences or should I write a PL/SQL funtion to generate the client
codes ?
I was planning to maintain a control table which will have structure and data as follows:-
--------->
SQL> DESC CLIENT_CODE_MAST;
Name
-----------------------------
CLIENT_MONTH
MONTH_DECODED_VAL
MONTH_CURR_VAL
SQL> SELECT *
2 FROM CLIENT_CODE_MAST;
CLIENT_MONTH MONTH_DECODED_VAL MONTH_CURR_VAL
JAN A 1
FEB B 1
MAR C 1
APR D 1
MAY E 1
JUN F 1
JUL G 1
AUG H 1
SEP I 1
OCT J 1
NOV K 1
DEC L 1
--------<-
The above data is stored in the CLIENT_CODE_MAST table before the user starts using the
application for the first time.
When the user creates the first client in January 2007,in my oracle PL/SQL function I will get the
Current Value from the CLIENT_CODE_MAST.MONTH_CURR_VAL
column and create the client code as "P07A001". I will also update the CLIENT_CODE_MAST row having
CLIENT_MONTH = "JAN" to 2 so that we can use this value for the next client created in January
2007.Therfore,the data present in the CLIENT_CODE_MAST will be as follows:-
SQL> SELECT *
2 FROM CLIENT_CODE_MAST;
CLIENT_MONTH MONTH_DECODED_VAL MONTH_CURR_VAL
JAN A 2
FEB B 1
MAR C 1
APR D 1
MAY E 1
JUN F 1
JUL G 1
AUG H 1
SEP I 1
OCT J 1
NOV K 1
DEC L 1
--------
Therefore,for the second client created in January 2007 we can use the MONTH_CURR_VAL value ('2')
and create its client code
as "P07A002"
Now ,suppose the user is creating the Client in February 2007,we will reset the
CLIENT_CODE_MAST.MONTH_CURR_VAL to '1' for the
CLIENT_MONTH='JAN' and we will use the value of the MONTH_CURR_VAL for the CLIENT_MONTH='FEB'.
Therefore,the first client code created in Feb 07 will be "P07B001",second client code created in
Feb 07 will be "P07B002".
Please advice me as whether the above mentioned technique is right ?
Please advice me the best technique to generate the client codes in this format "P07A001".
Thanks and Best Regards
-Hemal
Followup November 17, 2006 - 8am Central time zone:
you will write a procedure to do this, there is nothing but nothing built in that can do this, it
is a bad requirement.
Min Value in sequences
November 17, 2006 - 1pm Central time zone
Reviewer: Maverick
Tom, To be frank, I did not understand your response on this one. your example is taken care of by
"increment by -1" option. So where does MinVal come into picture?
Thanks,
Followup November 20, 2006 - 2am Central time zone:
umm, sequences can run backwards.
so, you can set a minimum value they can run backwards to.
Min Value in sequences
November 17, 2006 - 4pm Central time zone
Reviewer: AD from UK
Fisrt of all thanks to Tom and the readers.
To Maverick,
I think Tom's point is:
If you want a sequence to be in an increasing order then you will start with some min val and then
specify the max val.
for example a sequence can starts with minval =1
max val=1000000 and may be incremet by 1.
If you want your sequence to be in a decreasing order then you can define your sequence as follows
start with =1000000
min val =1
increment by -1
and max val = 9999999999
So, you do require minval setting sometime as you would like to set the lower limit just you would
set the upper limit in an increasing sequence.
I hope Tom will correct me if I am wrong.
Regards
Followup November 20, 2006 - 2am Central time zone:
that is it.
Regarding Oracle Sequences
November 18, 2006 - 12am Central time zone
Reviewer: Hemal from India
Thanks a lot Tom.
I highly appreciate your help.
Best Regards
-Hemal
MinValue Still not sure :-)
November 18, 2006 - 9pm Central time zone
Reviewer: Maverick
I am still trying to understand about this Min Value in sequences.
AD Wrote
"If you want your sequence to be in a decreasing order then you can define your
sequence as follows
start with =1000000
min val =1
increment by -1
and max val = 9999999999"
Ok, here if we want in decreasing order, we said start with 1000000 and increment by -1. So every
iteration will be in decreasing order. So, again why Min Value [Sorry Really trying to understand
here] and AD , why a max value of 9999999999, as it never reaches that value [since it's
decreasing ]?
Thanks Again.
Followup November 20, 2006 - 2am Central time zone:
it is putting a lower bound on the sequence value, since the sequence runs "backwards" - it
DECREASES - the value will never go below that "minval", you'll get an error instead.
min value
November 19, 2006 - 9am Central time zone
Reviewer: jimk from Portland, OR
Min value is there so you can specify a minimum value. One might want to do that to give different
instances a unique sequence in a replicated environment. (eg server 1 from 0 to 1000000 server 2
from 1000001 to 2000000...)
Sequences
November 30, 2006 - 9am Central time zone
Reviewer: Frank from USA
Hi Tom,
Is there any way to reset the sequences after a certain period of time? For E.g If we need to reset
the sequence to '1' at the start of every year.
Followup November 30, 2006 - 10am Central time zone:
why?
Sequences
November 30, 2006 - 10pm Central time zone
Reviewer: Frank from USA
In our application, the data will be taken backup on the last day every year. From next day
onwards, fresh records will be inserted into database, which need to start from 1.
I am ok if the sequences are not gap free also. But at the start of every year, the sequence need
to start from 1.
Followup December 1, 2006 - 5am Central time zone:
why
Regarding Sequences
December 11, 2006 - 12pm Central time zone
Reviewer: Hemal from India
Hello Tom,
My client has a very specific format of the Transaction number.Though I love sequences but in this
situation I am not able to use it because of clients typical requirements such as there must not be
Gaps ,require to reset the running number back to Zero at start of every month.
Therefore,I had no choice but to write my own function for generation of Number (Shown Below).
But I am very much worried about the concurrency.
What will happen if the two users simultaneously access the function ?
Are there any chances that both the users will get the same value from this function ?
Will this function fail in a multiuser environment ?
Is there any better way for coding this.
Following is table structure and the corresponding function code:-
SQL> desc transaction_number_mast;
Name Null? Type
----------------------------------------- -------- -------
NEXT_TRAN_NUMBER NUMBER
SQL> SELECT * from transaction_number_mast;
NEXT_TRAN_NUMBER
----------------
36
SQL>
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION GET_NEXT_TRAN_NUMBER
RETURN NUMBER
AS
CURSOR c_transaction_number_mast IS
SELECT next_tran_number
FROM transaction_number_mast
FOR UPDATE NOWAIT;
t_next_tran_number transaction_number_mast.next_tran_number%TYPE;
BEGIN
FOR r_transaction_number_mast IN c_transaction_number_mast LOOP
t_next_tran_number := r_transaction_number_mast.next_tran_number;
UPDATE transaction_number_mast
SET next_tran_number = next_tran_number + 1
WHERE CURRENT OF c_transaction_number_mast;
END LOOP;
COMMIT;
RETURN t_next_tran_number;
END;
/
-----------------------------------------------------------
Please help me tom.
Thanks and Best Regards
-Hemal
Followup December 11, 2006 - 1pm Central time zone:
gap free = ZERO CONCURRENCY.
period, no if an or buts about it.
just
update t
set tran-number = tran-number+1
returning tran-number into l_tran_number;
there should be no select, totally unnecessary.
that update will of course serialize - obviously, so there will be NO concurrency.
and you BETTER LOSE THAT COMMIT, else you have just violated your "gap free" requirement entirely.
That COMMIT cannot be there if you want "gap free" (and hence NO CONCURRENCY)
Primary key generation
December 14, 2006 - 12pm Central time zone
Reviewer: RP from London, UK
Hi,
slightly off topic (and i thought i asked this already but can't find it anywhere on the site!):
My shop uses Java to generate all primary keys - they come out as 36 char strings.
Any performance implications to doing this Tom?
Thanks
Followup December 15, 2006 - 8am Central time zone:
besides the java bit ? :)
there is the size (36 bytes versus something less)
there is the storage associated with that of course.
from a retrieval perspective - it is likely "not materially different, it'll take a bit more cpu"
Regarding Sequences
December 14, 2006 - 12pm Central time zone
Reviewer: Hemal from India
Hello Tom,
I didnt understood the following statement:-
"that update will of course serialize - obviously, so there will be NO concurrency"
Suppose the intial value in tran-number column is 2.
Suppose two users at a same time fires the
"update t
set tran-number = tran-number+1
returning tran-number into l_tran_number;"
How oracle make sure that one user gets the value as 3
and another user gets the value of 4 ?
Also as per your advice I am not firing the COMMIT statement immidiately after the UPDATE
statement.
Therefore,if simultaneous users fires the UPDATE statement
at same time how does the Oracle assign unique number
to each user.
( In my coding standard guidelines, it is writtern that "Lock the record before performing an
update".Therefore,in my earlier code I locked the
record with 'FOR UPDATE NO WAIT' so that it will display an error message to another user trying to
UPDATE the record at the same time.)
Please advice me.
Thanks and Best Regards
-Hemal
Followup December 15, 2006 - 8am Central time zone:
that is precisely what Oracle does - it is basically its purpose - to achieve correctness of
transactions.
If you want the entire full big story - you can check out the Concepts Guide or my version of the
Concepts Guide - Expert Oracle Database Architecture where I cover transactions and how Oracle
works in general.
Update locks the record - there is no need IN THIS CASE to lock the record. You do not care about
lost updates here - in fact you DESIRE lost updates (to update data without really reading what the
other person did to it).
Regarding Sequences
December 17, 2006 - 9pm Central time zone
Reviewer: Hemal from India
Thanks Tom
I highly appreciate it.
Sequences again.... please help !
January 9, 2007 - 7am Central time zone
Reviewer: Fabio Franzotti from Brazil
Tom,
We have a table and the PK is (Customer Number(3),NUM_ID Number(8) ).
Actually we work with only one customer, the customer 001, but we have more Customers soon...
The Num_ID should be sequential, but can have small gaps.
We like to use different sequences per Customer... how the best way to implement this ?
Thanks a lot,
Fabio Franzotti
Followup January 11, 2007 - 9am Central time zone:
why would you like to use different sequences per customer - that would not be sensible at all.
if num_id can have gaps - it can have gaps - period. the size of these gaps is not relevant
Avoiding Prison... pre-generate records
January 11, 2007 - 12pm Central time zone
Reviewer: SamG from NYC
The ugly truth is that sometimes we have to match the pattern of 19th and 20th century pre-printed order forms.
So, pre-generate rows in a document tracking table that match the paper forms. No gaps.
A before statement trigger can spawn a clean new batch of rows out ahead of processing as needed.
This approach isn't all that difficult to implement. Data is entered with UPDATE instead of INSERT. Not a heavy price.
Also, Content Manager used in tandem with OID can publish available slots and support information, including diagrams and regulations, through apps servers. Accomodating different series for different customers becomes trivial.
January 21, 2007 - 9pm Central time zone
Reviewer: YL from Hong Kong
Dear Tom,
It is a good post for the discussion of sequence number.
Recently, I am working for "translating" some statements from Microsoft SQL Server to Oracle.
In SQL Server, there is a statement about the creation of table:
CREATE TABLE SBCEvents(SBCSeq int IDENTITY(1,1) NOT NULL, SrcSystem int NOT NULL)
It is obvious that Oracle does not have identity constraint. How can I translate this from SQL Server statement to Oracle statement?
Thanks a lot
YL
non-sequential sequence in RAC database
May 25, 2007 - 9am Central time zone
Reviewer: Sean from NJ, USA
Hi Tom,
We don't mind sequence gap, but with RAC database and sequence with catch, we got non-sequential sequence (21 is created before 1), which could give misleading information when we use order by.
Thanks so much for your help.
-- Sean
Followup May 26, 2007 - 12pm Central time zone:
hahahah - sequences are not ordered by time in any case. single instance or not.
if you need something ordered by time, you better - well - have a time stamp.
all a sequence is is a unique value, nothing else can be derived from it.
Generating sequence
June 26, 2007 - 5pm Central time zone
Reviewer: Priya from Los Angeles, CA USA
Hi Tom,
I have a table
create table library_Test
(
CLIENTID NUMBER (11),
PROPERTYID NUMBER (11),
DOCID NUMBER (2),
DATECREATED DATE
)
Insert into library_test values(99999,12345,1,sysdate)
Insert into library_test values(88888,45678,2,sysdate)
Now I add in a column libraryid which will identity each row
alter table library_test add libraryid number(11)
Now I want to generate libraryIDs say 1,2 and so on for the number of rows in the table. I want libraryid as 1 for the first record created
based on datecreated.
So I want my output to look like this
clientid propertyid docid datecreated libraryid
99999 12345 1 6/26/2007 2:04:30 PM 1
88888 45678 2 6/26/2007 2:04:56 PM 2
Oracle version is 9.2.0.1.0
Thanks,
Priya.
Followup July 2, 2007 - 9am Central time zone:
well, this is a bad idea - it seems you will make a future presumption that libraryid can be used to "date" a record - when in fact, subsequent records that are added to this table will use a SEQUENCE (please) and it could well be that the sequences are assigned in such a fashion that a record with a larger libraryid will have a smaller datecreated value than some other record.
but, to answer the question:
ops$tkyte%ORA9IR2> create table library_Test
2 (
3 CLIENTID NUMBER (11),
4 PROPERTYID NUMBER (11),
5 DOCID NUMBER (2),
6 DATECREATED DATE
7 )
8 /
Table created.
ops$tkyte%ORA9IR2> Insert into library_test values(99999,12345,1,sysdate);
1 row created.
ops$tkyte%ORA9IR2> exec dbms_lock.sleep(4);
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> Insert into library_test values(88888,45678,2,sysdate);
1 row created.
ops$tkyte%ORA9IR2> alter table library_test add libraryid number(11);
Table altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> merge into library_test
2 using ( select rowid rid, row_number() over (order by datecreated) rn
3 from library_test) new_data
4 on (library_test.rowid = new_data.rid)
5 when matched then update set libraryid = new_data.rn
6 when not matched /* never happens of course */ then insert (clientid) values (null)
7 /
2 rows merged.
ops$tkyte%ORA9IR2> select * from library_test;
CLIENTID PROPERTYID DOCID DATECREATED LIBRARYID
---------- ---------- ---------- -------------------- ----------
99999 12345 1 02-jul-2007 09:56:52 1
88888 45678 2 02-jul-2007 09:56:56 2
accidentally dropping sequence
July 30, 2007 - 9am Central time zone
Reviewer: A reader
Today, I accidentally drop sequence owned by SYS, but database do not going to broken. But I am
afraid it will effect on database work. So my question, could we restore the sequence just like
table could used recycle bin feature. My database is Oracle 10g R2.
Thanks
Followup July 30, 2007 - 5pm Central time zone:
you cannot accidentally drop a sequence from sys.
you shouldn't be logged in as sys.
anyway, contact support, talk to them about what your options are - database is broken, you just haven't hit the issue yet.
Gap free invoice numbers
July 31, 2007 - 3pm Central time zone
Reviewer: Wiktor Moskwa from Poland
In Poland, as in many other countries, invoice numbers have to be gap-free by law. Period.
I know that this (as many other real-life things) is stupid but after 2 years of work implementing
accounting solutions I don't even bother :-)
If you want to cancel an invoice you issue a "cancellation note". If some data changed on an
invoice you issue a "correction invoice". In all cases the original invoice has to be untouched
(and kept for 5 years - printed).
Fortunately invoice numbers can have many series and one organization can issue invoices like:
1/A/2007, 2/A/2007 ... 1/B/2007, 2/B/2007, etc.
So serialization is not global but on the level of single series. Because we can assign a single
series to a single sales point or even a single salesperson - the serialization problem is solved.
Tom, you ask "what if coffee is spilled, invoice is lost or burned".
I answer in details: you have to restore it. If you don't and you have a gap in invoice numbers -
you've just committed a financial crime. Really. Period.
You can print it for the second time (the most common case) or restore it from some notes or memory
or even make it up...
Followup August 2, 2007 - 10am Central time zone:
you should just assign your invoice numbers at "book closing time" then. Or have a process to fill in "null invoice"
This makes me laugh - I'll bet the law is not what you say, but has been over interpreted.
What happened when coffee was spilled on a tablet of paper, or some invoices JUST WENT MISSING - physical invoices - before the computer.
Really period - how do you restore destroyed pieces of paper? counterfeiting is a crime, having gaps - cannot be - doesn't even begin to make sense.
Even make it up?
August 2, 2007 - 11am Central time zone
Reviewer: Jim from Portland, OR, USA
If you lose it or spill coffee on it then you can "even make it up". I would think that would be
financial fraud.
Gaps
August 3, 2007 - 4am Central time zone
Reviewer: martina from vienna, austria
I think Wiktor is correct. if you physically lose an invoice you have to reconstruct it. if your
accounts are correct you can do that. you might even write a note "this is a copy as original was
spilled with coffee". If you cannot reconstruct it you are in trouble anyway because your accounts
are not correct. (reconstruction is not counterfiting, with or without computer).
the reason for gap-free invoices is that they may be checked easiliy. you are paying tax according
to your invoices. you are not able to just hide every other invoice and not pay tax for it. (of
course you may, but this is defraudation).
So if you have gaps in your invoices and you are audited (big companies are audited every year) you
are in trouble. it is possible that in some european countries you might be allowed to explain gaps
and prove that there never was an invoice with the missing numbers. (how does one prove that
somthing has never been there what is not there now??).
i guess that is exactly what authorities want: serialize the process of writing invoices. so
filling in null invoices is exactly what is illegal, you must not write invoice 10 before invoice
5.
best regards, martina
Followup August 5, 2007 - 1pm Central time zone:
I have a tablet of invoices.
They have NOT YET BEEN USED.
I spill coffee on them, they are destroyed.
They go missing, it fell out of my briefcase, they are lost forever.
They burnt in a small fire.
My kids drew pictures all over them with crayons while I wasn't watching, they are destroyed (in fact, the little guys ripped them into shreds!)
And so on.
Now what.
Whatever you answer - WHATEVER YOU ANSWER - we will apply that to....
gaps.
Invoices need not be sequential. If they were - well, guess what - you could never have a place like "Dell computers" or "Amazon" because you cannot sequentially generate all of that stuff.
SamG is mostly right
August 3, 2007 - 1pm Central time zone
Reviewer: Carsten from Germany
If you need gap free record numbers, do it just like you would do it with paper forms:
- Create empty forms
- distribute the forms to the users
- mark them as valid when returned
In more detail:
create table VOUCHERS
(
ID NUMBER not null primary key,
USERNAME VARCHAR2(20)
)
Creation is a 100% serializied nightly/weekly batch job (not via Trigger as SamG suggested!), which
basically inserts mostly empty records in a sufficient number based on usage and current "stock" of
empty records.
lock table vouchers in exclusive mode; /* shouldn't hurt much if timed well */
insert into vouchers (id)
SELECT ROWNUM+(select nvl(max(id),0) from vouchers) n
FROM ( SELECT 1
FROM dual
CONNECT BY LEVEL <= 10 /* add here some smart guessing to find right number */
);
commit;
Distribution means that the application requests a process number once a user opens the input mask
by calling something like:
create or replace function get_voucher_form(fuser varchar2) return number is
Result number;
begin
update vouchers set username=fuser where username is null and rownum=1 returning id into Result;
commit;
return(Result);
end get_voucher_form;
And finally you call some other function to mark it as valid (too lazy now to prepare that as well
;) ).
Advantages:
- serial part is minimized
- auditors should be able to understand it
- no chance to skip numbering with clever abort/delete (which is a problem if you do it
afterwards, e.g. fill out the form, print it, then cancel)
Caveats:
- just like with handing out forms usage is not in order. But again the paper analogy lets you
explain it: as forms are printed and distributed ahead, usage may vary how much single workers
process each day. This is the price we pay for filling out the vouchers in parallel.
Followup August 5, 2007 - 1pm Central time zone:
well, until user uses one of them to level a table
or spills coffee
or .... whatever
I give up, go for it - be serial.
August 6, 2007 - 7am Central time zone
Reviewer: Carsten from Germany
I don't like the idea much as well, but sometimes you are forced to by law.
You say what about spilling coffee ? Well I have seen it: they are forced to dry it, but a big
"void" onto it and put it to the file. If a form is really destroyed with no way to keep it in the
records, then an replacement voucher must be created with signatures of the president and the pope
on it (or as close as you can get to that) with clear reason why and how it was lost.
If your business practice is not that strict, then Tom's orginial answer is for you: use sequences
and do not rely that they are gap free.
Followup August 6, 2007 - 12pm Central time zone:
it blows out the window of a 10 story building and is gone.
Look - things disappear, they just do - what happens then
when you tell me what happens with things disappear - as they are wont to do - then I'll tell you what to do with gaps.
I've worked in stores and such before - invoices are just paper - they get ripped up, they get thrown away, they disappear - they are non-sequential (you would need precisely ONE CLERK otherwise)
Current Sequence Value
August 6, 2007 - 11am Central time zone
Reviewer: Phil from Britain
Hi Tom
I agree totally about using sequences, and is what I have always done (since I used Oracle that
is!).
However, I've got a problem with performance. I customer has a table that has a LOT of insert
activity going on against it (typically, 1-3000 inserts a second). They want to be able to report
any time there are more than a certain inserts in 5 minutes.
How to do this efficiently?
Well, current thinking is the following:
Each insert grabs a nextval from a sequence. An independent process runs each second and adds a row
into a table with the currval sequence number and the prior value which means we can do the math
and get the count of the number inserted. As well as this, a sum of the last 300 seconds or 300
rows based on the count figure allows a much more efficient count without really pounding the table
hard.
You're bound to rip this apart but the real flaw in this is establishing currval without being
connected to the session that does the inserts. It looked good until we realised that.
Thanks in advance and for the past help which has made such a difference to my work.
Phil
Followup August 6, 2007 - 12pm Central time zone:
it is as good as anything else - and not truly meaningful :)
you need a continuous function (to be alerted to a threshold being hit in a 5 minute window), but all you have is digital inputs :)
xxxxxx
zzzzzzyyyyyy
say during the 5 minutes represented by xxxxxx you had 100,000 inserts.
It is idle before and after that.
But you do your windows of observation during zzzzzz and yyyyyy
Each of those show 50,000 inserts
Your threshold is 75,000 inserts - you exceeded that by a lot, but no one will ever know...
sliding window
August 6, 2007 - 1pm Central time zone
Reviewer: A reader
Hi Tom,
Yes, the diagram you put together is perfect! But this is why we have the idea of:
... An independent process runs each second and adds a row
into a table with the currval sequence number and the prior value which means we can do the math
and get the count of the number inserted. ###As well as this, a sum of the last 300 seconds or 300
rows based on the count figure ### allows a much more efficient count without really pounding the
table
hard.
I agree it is horrible but are you aware of any better method? And can we get the currval from some
dba object without being connected as the process that writes to our big table?
Thanks as always
Phil
Followup August 7, 2007 - 9am Central time zone:
I'd be curious as to "why you need to know this"
this process, sure, it would work - but..... it would also be adding to the problem.
now you'll have to figure out how to partition this table, manage the partitions, age data out, add the additional resources needed to perform this operation and so on.
auditing gaps
August 6, 2007 - 7pm Central time zone
Reviewer: Dario from Argentina
Well, in Argentina, the numbers must be gap free or a least, the gap must be audited or informed
(You must inform: "These forms have been destroyed: xxxx...xxxx...". Sometimes, you must make it
public. It's very common to see reports on newspaper of companies declaring "the following receipts
have been" lost, stolen...)
So... a trigger could be used with an autonomous transaction inserting sequence number, user, time,
application used, etc into a table.
You can report later, numbers 'retrived and unused' (lost sounds ugly and incorrect) by the
application at the price of having one more table for each sequence to be tracked, or a table with
a "SEQUENCE_NAME" column. I would probably keep that table in the recycle_buffer, (if the tuning
and adjustment worth it).
Of course, you should run when required:
select id from seqs_retrieved
minus
select id from account_events
Question: Could this "tracking" be done using log miner and avoid to "reinvent the wheel"?
If delete operations are not expected (if it needs to be gap free, the application probably will
not do a delete),
I would audit delete operations on that table, to complete the auditing report.
And if I were reading this, I would wait for Tom to review it, before trying it.
(I totally agree: Non gap numbers have no sense, burocracy have no sense. Probably the law have no
sense too. That's why I'm not a lawyer. B-) )
Greetings.
insert activity
August 8, 2007 - 1pm Central time zone
Reviewer: Jeff from Milwaukee WI USA
--customer has a table that has a LOT of insert
--activity going on against it (typically, 1-3000 inserts --a second). They want to be able to
report
--any time there are more than a certain inserts in 5 --minutes.
-How to do this efficiently?
Is there a timestamp field in the table being inserted into? A simple count query using the
timestamp should be able to provide this.
Performance
August 9, 2007 - 4pm Central time zone
Reviewer: Phil from UK
Tom / Jeff
I agree, a simple query should provide this based on timestamp, however, how will Oracle cope with
3000 inserts a second, after 24 hours we decide to run a query that asks for average insert rate in
5 minute slots for past 24 hours? This will need to examine 259 million rows and will presumably
take a while to run. The method we have in mind will look at 86k rows and so require 1/3000 of the
effort. Not only effort, but it will require no time on the table that is still receiving 3k rows a
sec.
Is there a 3rd way of doing this? I've got a feeling there might be!
BTW, as an aside, is a trigger the best way of managing the ID or procedural code. I recall
triggers were not as efficient as procedural code a while back.
Thanks!
Followup August 14, 2007 - 10am Central time zone:
I still feel the best approach for this is:
<this space intentionally left blank>
I'm failing to see the relevance of this particular metric.
average business transactions per second <<<====
now that would be useful, but this? (and we can typically get the average tps pretty easily)
after a little more thought
August 9, 2007 - 5pm Central time zone
Reviewer: Jeff from Milwaukee WI USA
Looking at this a little closer, maybe you're over-complicating the issue (or I'm
misunderstanding).
"An independent process runs each second and adds a row
into a table with the currval sequence number and the prior value which means we can do the math
and get the count of the number inserted."
Why do you need the currval & prior value? Just burn an entry from the sequence, have a table with
two columns, timestamp and sequence_value. Have a job run every second (or every 15 seconds, or
every minute - do you really need the detail down to the second?) Insert a nextval with timestamp
and you're good to go. So you leave a 1 value gap in the original table every second (or whatever
timeperiod) - who cares? Now you can query this smaller table for any interval you choose.
More on the law
August 14, 2007 - 3pm Central time zone
Reviewer: Wiktor Moskwa from Poland
============================
Tom:
This makes me laugh - I'll bet the law is not what you say, but has been over interpreted.
[..]
counterfeiting is a crime, having gaps - cannot be - doesn't even begin to make sense.
[..]
I've worked in stores and such before - invoices are just paper - they get ripped up, they get
thrown away, they disappear - they are non-sequential (you would need precisely ONE CLERK
otherwise)
============================
Tom, with all respect, but the law here is exactly as I've written before. I've consulted a lawyer
and accountants - the act literally states it - no gaps in invoice numbers unless you've got a
really good explanaition like an office that was burn to the ground. People pay fines here during
tax audits for having such gaps, an offence is called "unreliable bookkeeping".
Please remember that there are places where the law is against logic.
I shouldn't write "even make it up" last time, that was an exaggeration.
You've said few times that you do not care about the performance of a query that gives a wrong
answer. Using sequences here would be really against the law and that way simply wrong.
I think that serialization problem is not that critical because every clerk can have his own
invoice number series.
Only for clarification: I'm talking here about invoices only, not about orders.
Of course you're right with burnt and missing invoices. Here when you sell something you print an
invoice in two copies(*) and the buyer takes one of them. If your copy is missing you've got
information to recover it (you can contact buyer, see what was sold and so on) and recovered
invoice will have to have the same number - so it will fill the gap. I think the law was meant to
protect against frauds or whatever and maybe many years ago it could work...
(*) - actually one copy is called "an original" by law and it has to be printed on that piece of
paper and you can print and have only one "original" invoice, on other copies you have to print "a
copy" (it is literally stated in the act) - another strange thing, isn't it? :-)
human subject ids need to be non-identifying
October 14, 2007 - 2pm Central time zone
Reviewer: lowest ranking person in my workgroup from wisconsin usa
Regarding the discussion about creating id numbers for
clients in nursing homes:
"Suppose a client visited the nursing home in January 2007, the client code is having following
format "P07A001"
Out of this "P" is hardcoded.
07 stands for year "2007".
A - Standands for January, (If client visited in February then this would be "B",If client visited
in March then it would be "C" ..If client visited in December then it would be "L").
001 is the running number.(This number is reset back to 000 in the next month).
For the 2nd client in the January 2007 the client code will be 'P07A002', 3rd client in the
January 2007 the client code will be "P07A003".
In case of February 2007,first client who visits the nursing home will have client code as
"P07B001",
Second client who visits the nursing home will have client code as "P07B002".
"
The problem is that coding things into id numbers comes from
managers. It is very very difficult to be a lowly dba or
developer and try to tell managers they cannot have elaborate coding schemes in ids because "it's
wrong". They do it all the time in excel and nothing bad happens. That's their standard for
goodness :-(. They didn't study computer science like we did and they don't much value it. My bs in
cs does not look impressive against their mba's, doctorates, and managerial titles. That's the real
world.
Without a very big very persuasive 'book of what is right and wrong in databases for managers
without a computing background' to show them, arguing with managers about such things and risking
insubordination is the biggest career shortener ever I'm afraid. BUT there is now a very very good
reason to not do this type of thing that one can invoke. It is potentially identifying of the
persons eg in this case the persons in the nursing home. If what the questioner meant by "client"
was people admitted to nursing homes then a raft of confidentiality principles arise. If he meant
employees visiting nursing homes it still could be the case that somehow this data could be
considered to link to some
confidential information about people in nursing homes. One can invoke visions of HIPAA and state
level confidentiality laws and if this has something to do with research, human subjects'
regulations. Anything like a date or location is definitely potentially identifying. Names,
addresses, sex, gps coordinates, zip codes, ssn numbers,drivers license numbers, medical record
numbers, phone numbers, email addresses, url's, and a lot more may be considered identifiable under
some circumstances. Because of this in a study I'm involved in we have to assign unique subject id
numbers randomly because of the possibility
of linking a range of numbers with a visited location in the state. Issues about doing that are why
I was reading this thread.
Why bother users with primary keys?
March 17, 2008 - 12pm Central time zone
Reviewer: Thomas Bender from Scotland
I have in the past worked with a database that used (year)|(month)|(number) as primary key, and it
really was painful. Primary keys are nothing that users should worry about, tamper with, or even
see. If there is a requirement for gapless numbers or composite codes, there's always the
possibility of an alternative unique key (which I wouldn't use for RI, though).
After all, users may want to change their composite codes from (year)|(month)|(number) to
(year)|(quarter)|(month)|(number) some day. Easily done if that composite code is not used for RI.
Sorry for the somewhat purist opinion.
Followup March 24, 2008 - 8am Central time zone:
It is not purist, it is just correct.
SEQUENCE CACHE SIZE
July 11, 2008 - 12pm Central time zone
Reviewer: Stan from Boston
Hi Tom,
our software is distributed to clients with different needs. All get the same schema.
Consequently each client my put a load on different sequences in the schema. Instead of trying to
identify which client uses which sequences heavily I suggest to just bump the cache on all
sequences to 1,000 across the board.
Would you agree or do you see any downside to that?
Thanks in advance.
Followup July 11, 2008 - 1pm Central time zone:
i see no downsides
a gapless sequence
August 2, 2008 - 6am Central time zone
Reviewer: rc from The Netherlands
An easy way to create a gap less sequence is to ask the users to enter the sequence numbers by
hand.
One of my customers had 10.000 boxes, all with an unique number printed on top. The users take a
box, fill it with some stuff, enter the number in our application and click on the save button.
It seems primitive but we never heard any complaints.
Followup August 3, 2008 - 2pm Central time zone:
what happens when a water pipe bursts in the room over the room the boxes are in and 50 of the boxes are completely and utterly damaged beyond use.
or someone crushes a box by accident.
or - whatever
it is not gapless in general. And it is not a system assigned, gap free sequence either. it is external data the end user inputs.
gap less
August 4, 2008 - 6am Central time zone
Reviewer: rc from The Netherlands
>What happens when a water pipe burst ... or whatever...
Their problem, not an application problem. And why should 'the system' do everything? The user can
also think and act. "In der Beschränkung zeigt sich erst der Meister!"
Followup August 4, 2008 - 1pm Central time zone:
so, it is not gap free - that is my point.
And goes back to the fact that database sequences never need be gap free
because checks get voided
because water spills
because fire burns
we've never had gap free anythings.
never
August 4, 2008 - 3pm Central time zone
Reviewer: rc from The Netherlands
I don't know if database sequences never need to be gap free. One of my teachers at high school
always said: 'never say never', he taught English (I almost wrote 'he teached English').
Knowing the answer to this question means knowing all the law systems on this planet, not easy.
(Laws don't have to be reasonable).
I just say that you don't always need a database sequence, user entry or bar code scanning
sometimes matches the physical reality much closer. My guestimate is that users understand that
fire and water can cause a little havoc, users don't always understand that you can't guarantee a
gap less database sequence.
Followup August 4, 2008 - 3pm Central time zone:
never say never
never say always
I always say
that is one I'm fond of
The fact is: database sequences ARE NOT gap free, they cannot be, they never will be, they will always have gaps
therefore, it does not compute to say "database sequences never need to be gap free" - they cannot be gap free, that is a fact.
The laws easily accommodate this stuff. You can assign the numbers when you close the books (way one), you can void records - just like you did with water, fire, loss, whatever.
All the laws?
August 4, 2008 - 3pm Central time zone
Reviewer: rc from The Netherlands
I don't know all the laws on this planet, neither do you, do you? I can't believe you know them
all.
If a gap free database sequence can't exist because it is impossible, users will have to do it
another way if they need it, so without a database sequence. Nothing wrong with that.
Whether such another way exists or not is not my problem.
Followup August 4, 2008 - 8pm Central time zone:
all i know is "gap free anything"
a) has never been possible, ever, not before computers even. water exists, water makes it impossible.
b) therefore, if there is a law against it, all citizens in that country would be in jail
It is your problem, you are tasked with building systems. when someone comes to you with something so entirely utterly and completely unreasonable - you must push back.
Ask them to see that law, then you'll see or not....
Everything wrong with trying to satisfy a requirement that does NOT EXIST and would so cripple performance as to make a system unusable.
The "reality" of gap free sequences
September 11, 2008 - 6am Central time zone
Reviewer: Dieter Oberkofler from Vienna, Austria
Hi Tom,
I have been following this discussion from the very beginning and find it most interesting and
amusing but not that useful anymore.
I know how important (2nd after binding!) this issue is for you and therefore hesitated to add my
comments but finally I just take the risk.
I truly believe that the discussion got far to emotional and is starting to miss the point.
In my opinion the facts are:
1) Most people (especially SOX auditors) very much like the idea of a gap free sequence because
they (and people in general) interpret some inherent correctness in it.
2) A 100% gap free sequence has been impossible on paper and still is today as you indicated in
your examples.
3) Trying to enforce a gap free sequence leads to all kinds of performance problems due to
serialization issues in applications.
We therefore need to find a COMPROMISE between what people "like" and what is technically
reasonable.
I still completely agree with your point and everyone should try to avoid serialization and use
database sequences whenever possible. At the same time my experience with clients shows that there
is a strong demand to have "something like" a gap free sequence and we have to "deal" with this as
well.
In my opinion the goals should be:
- Use database sequences whenever possible
- Try to avoid or explain gaps whenever needed
- Deal with the fact that we cannot completely avoid gaps
We are currently using all kind of different "sequences" for different
situations:
- We use database sequences whenever possible
- We use serialized operations whenever absolutely needed (and if concurrency allows us)
- We use MAX+1 in some cases of combined keys
- In some cases we just try to "track and explain" gaps
I'm still most interested to hears how others deal with what I would call "the reality" of
sequences.
Dieter
Followup September 16, 2008 - 12pm Central time zone:
1) they do not exist, and they never ever have.
2) true
3) correct
I saw no "solution" proposed here at all? Other then you stating "they never existed and they do not exist now"
max+1 doesn't even do it in real life.
When you close the books (that is what gets audited), you are free to assign to these rows that will never ever change a sequential number.
Nothing else really "works" or even makes sense.
Show me a third party product that does invoicing and 100% assures you of gap free numbers (not that gap free means *ANYTHING* at all, it shows NOTHING)
Gapless sequence
December 9, 2008 - 8am Central time zone
Reviewer: Mani from Chennai, India
As another reviewer mentioned, gapless sequences are mandated in many european countries (Spain,
Italy, France, Greece, Austria ), Oracle's own Applicaions product(E-Business suite) understands
this and provides solutions too for gapless sequencing. So we need to live with that. No escape
from long arm of the law. Law enforcing bodies have their own logics and rationals.
Mani
Followup December 9, 2008 - 2pm Central time zone:
no one provides for it, no one.
if you want, just update the silly numbers when you close the books - use a column called "the_special_gap_free_number" - it is maintained when you CLOSE THE BOOKS.
we have never - never had gap free numbers. Unless you come back and "free the gaps" later.
Gap free Seq
December 10, 2008 - 1am Central time zone
Reviewer: Umesh Kasturi from Bangalore
I think we could achieve the gap free sequence in a table ; but at the expense of 2 sequence for
one
May be I do all the transactions using one sequence and if succesful I update the record using the
second sequence
I know this is dirty ; but I think it helps
Followup December 10, 2008 - 9am Central time zone:
nope, that won't work either. think it through.
all it takes is one failure. So, you do a successful transaction - and commit - now what - what makes sure
a) the second transaction even happens?
b) that the second transaction succeeds?
Gapless Sequence
December 10, 2008 - 8am Central time zone
Reviewer: Mani from Chennai, India
Tom,
This is the link for the document 11i Ebiz Suite Sys Admin guide.
http://download.oracle.com/docs/cd/B25284_01/current/acrobat/115sacg.zip
The document part pertaining to Gapless sequence is pasted below.
Gapless numbering also automatically generates a unique number for each document, but ensures that
the document was successfully generated before assigning the number. With Gapless numbering, no
sequence numbers are lost due to incomplete or failed document creation.
Am not getting what you mean when you say no-one ever does. Ebiz product may be internally using
serialization for achieving this, but this certainly caters the gapless sequence requirement.
Thanks
Mani
Followup December 10, 2008 - 10am Central time zone:
do you see what they did - exactly what I said
THEY COME BACK LATER AND DO A NUMBERING
*later*
*after the fact*
*not as part of your transaction*
*as a separate thing later*
Just like you can do with anything you want to do. close the books at the end of the month? great - go back and assign this fictitious number to a column you call "this_is_the_real_invoice_number_wink_wink"
and just ignore the existence of the true primary key of the table...
Large entities do use random numbering
December 10, 2008 - 10am Central time zone
Reviewer: sPh from USA
> ) Most people (especially SOX auditors) very much
> like the idea of a gap free sequence because
> they (and people in general) interpret some inherent
> correctness in it.
Computer Discount Warehouse (CDW), one of the largest computer hardware/software suppliers in the
United States (and I believe one of the most profitable direct sales entities anywhere) was a
public company until 2007. CDW uses what are AFAICS randomly generated characters strings for
sales order acknowledgements, packing lists, and invoices. If there is a pattern to them I can't
see it, but they certainly aren't sequential as can be seen when you have an emergency order that
is shipped at 0300 on Sunday and it generates 27 invoices.
sPh
sequence
February 7, 2009 - 8am Central time zone
Reviewer: A reader
create table qstages (
bkno number(10),
media varchar2(3),
stage_no number(1),
recdt date,
compdt date,
status varchar2(1),
created_Date date
)
currently table has no primary key. it has a unique index on (bkno,media,stage,recdt,status).
Created_Date was added newly to table so thousands of old records do not have one.
i found it hard to deal with this table (sorts,query) as i need to sort records to find last record
entered for a book. A stage_no does not have to be incremental (ie they can do stage 4 before stage
2).
I am thinking of adding a "Seqno" to the table and poulate it via "after-insert" from DB.
1. would you add a sequential number for each record i.e. 1,2,3,...150,000
or would you add 1,2,3 and the reset it for each book.
In the first case (seqno) would be PL, and second case PK would be (bkno,seqno)
It sounds to me the second option is better but not easy to implement as the t rigger would have to
count the number of records for each book and then add one and create the next one. I probably need
some program to sort records and apply numbers correctly (cnat be done in SQL).
2. in one other column you mention that you can get autogenerated seq number using
but mod( dense_rank() ...., 4 ) would make it be 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, ...
how can this be done?
3. I enforced a default constraint for "created Date" and i still see some nulls sometimes. How do
you expalin that? should not oracle add that even if application missed that. do you think
something is updating that field to NULL.
4. All the old records do not have created dates. Is there any way to get that from oracle
internally. does it keep track on when a row was created?
thank you,
dbms_random
February 25, 2009 - 5am Central time zone
Reviewer: Anil
Tom,
If I have to generate unique values in an query, can I use dbms_random? I have a problem with
ROWNUM as it's doing a full table scan.
CREATE OR REPLACE VIEW MY_VIEW1 AS
SELECT ROWNUM COL0, COL1, COL2
FROM MY_TABLE1
SELECT * FROM MY_VIEW1
WHERE COL1 = 111;
The above query goes for a full table scan.
So, I thought I would use DBMS_RANDOM() but Iam doubtful whether it generates unique numbers.
CREATE OR REPLACE VIEW MY_VIEW1 AS
SELECT ROUND(DBMS_RANDOM.VALUE(1,100)) COL0, COL1, COL2
FROM MY_TABLE1
SELECT * FROM MY_VIEW1
WHERE COL1 = 111
Do you any alternative way to generate the unique values? I have query from a view only.
Followup February 25, 2009 - 6am Central time zone:
... If I have to generate unique values in an query, can I use dbms_random? ...
well, of course not. dbms_random returns a random number, the probability it returns "1" twice exists.
you will need to explain your entire problem - rownum would not in general "force" that, so - explain in painstaking DETAIL what you are trying to do - do not provide solutions that haven't worked - provide only "what you are trying to do - phrased as a specification", then we can generate a solution for you.
Here's the specification
February 25, 2009 - 7am Central time zone
Reviewer: Anil
Tom,
We want to generate unique number (not neccessarily it should start from 1) while SELECTing from a
VIEW. This view will be based on an another table which is not having any unique column (though
there's composite primary key)
Table
-----
Col1 Col2 Col3
A PPP 10-jan-2008
A XXX 11-jan-2008
A HGF 10-jan-2008
B A12 10-jan-2008
C LMN 10-jan-2008
C PQR 12-jan-2008
Z C15 1-feb-2008
If I want to fetch all the rows where Col1='A' and the result should look like:
View
----
Serial_No Col1 Col2 Col3
1 A PPP 10-jan-2008
2 A XXX 11-jan-2008
3 A HGF 10-jan-2008
Assuming Col1 and Col2 are composite primary key. The Serial_No must be a running sequence not
necessarily should start from 1
I hope Iam clear in my explaination.
Followup February 25, 2009 - 5pm Central time zone:
just add rownum to the view, but be prepared for it to be slow slow slow - as it would have to evaluate rownum before applying any of your other predicates against the view (no view merging)
this all sounds very fishy, this unique column - would not be deterministic, so while it might be unique - it would be USELESS, no idea why you would want it, no idea what you could use it for, it would be less than useful - it would be misleading at best and used inappropriately at worst.
why do you want this?
If it is really that the view would be a single table view like this:
CREATE OR REPLACE VIEW MY_VIEW1 AS
SELECT ROUND(DBMS_RANDOM.VALUE(1,100)) COL0, COL1, COL2
FROM MY_TABLE1
why not use rowid? If you do not enable row movement and never reorganize this table, at least rowid would be unique AND deterministic.
or better yet, fix the table, give it the required primary key.
To: Anil
February 26, 2009 - 5am Central time zone
Reviewer: Oleksandr Alesinskyy from Germany
Consider usage of analytical row_number() over(...) function if you definitely need your number.
But tom is 100% right - it very hard to imagine meaningful usage of a such number.
Followup March 3, 2009 - 7am Central time zone:
it would have the same defects as rownum - most acute would be "not deterministic". I cannot see the use of an artificial unique number that cannot be reproduced in the same fashion tomorrow.
March 3, 2009 - 10am Central time zone
Reviewer: Oleksandr Alesinskyy
Sure it would - that why a said that it's hard to imagine reasonable usage of this number.
the best sequential method I've found so far
March 9, 2009 - 6pm Central time zone
Reviewer: Mark M
I've got the same opinion as Tom about sequence numbers, but at one time I had a CFO that would
question me personally every time there was a gap in check numbers and I wanted him off my back.
THIS METHOD STILL DOES NOT GUARANTEE THERE WILL NEVER BE A GAP. But it minimizes the risk of a gap
tremendously.
(1) Create a sequence with NOCACHE, ORDER, and INCREMENT BY 1.
o Yes, I know NOCACHE hurts performance.
o ORDER only matters in a cluster (according to 10g documentation), and can seriously hurt
performance.
(2) Create an autonomous stored function to insert your new record.
o Make sure you do as little as possible between getting the sequence number and committing the
record. Ideally, the seq.nextval is in the insert statement.
o Return the new sequence number for future reference.
o Give your new record a status of VOID. If something happens before you get back to saving the
rest of your information you have a voided record.
I never ran into a gap again. That doesn't mean there aren't holes. There are:
(1) No matter how close the seq.nextval to the commit, there is an opportunity for Oracle to be
shut down or crash. If you are truly anal you can create a database trigger to run after the
database starts to find the max sequence and update the sequence to value + 1;
(2) If you have high transaction volume there is no reason to believe that the records in
different connections will be committed in the order the sequences were handed out. That doesn't
mean there will be a gap, only that they will not be committed in the same order they were handed
out.
(3) Combining (1) and (2) means you can have an out-of-sequence crash where the 2nd one is
committed and the 1st one isn't. This is a gap that can't be mended by just updating the sequence
with a database startup trigger. Told you there was a hole. You can use the same database trigger
to create a voided record for each gap if that makes you feel any better. Or use different
sequences for each user or station to avoid concurrency issues.
(4) There are any number of innovative ways to fail the insert, which will eat your sequence
number. Make the insert as simple as possible, ideally just the sequence. Or insert normally and
have the autonomous function do an update at a later time.
(5) If you REALLY REALLY need to make sure, use pre-printed invoice forms that already have the
invoice number on them. After printing enter the invoice number into your application. (You can
streamline this with pre-printed bar-codes!) Manage your coffee-stained, stolen, etc., invoices
outside the computer system.
I wonder if you could do something with advanced queues to really take care of the concurrency
issue. I'm sure that would be a ridiculous solution, but it's fun to think about.
mainataing sequences using autonomus transaction in RAC
September 30, 2009 - 8am Central time zone
Reviewer: A Reader from PA
Would like to know any impact of generating the sequences using autonomous transaction in RAC
environment, While loading the data in bulk, we are seeing high "log file sync" and "wait scn ack"
waits, though same test runs fine in NON-RAC environment with no waits. The procedure is selecting
(SELECT FOR UPDATE) and then updating the in-built sequence based on certain criteria.
Followup October 7, 2009 - 8am Central time zone:
just alter the sequence to be cache 10000000;
since
a) sequences are NEVER GAP FREE
b) sequences NEVER IMPLY ORDER
this is perfectly ok, if you made the mistake of falsely believing either A or B, stop it with A (it is not possible, it is not physically possible) and with B, start using some attribute YOU assign (like a date, a timestamp) to order data, nothing but nothing else works.
sequences already use autonomous transactions - that is why you are seeing high log file sync waits. By default every 10 times you hit a sequence we
a) start an autonomous tranaction
b) update seq$
c) commit <<<=== log file sync!!!! right there
the only correct way to fix that is to up the cache size to something reasonable.
|