Sequences
Venkatesa Prasad, June 22, 2002 - 3:23 pm UTC
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
June 22, 2002 - 4:05 pm UTC
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
mo, November 12, 2002 - 7:50 am UTC
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.
November 12, 2002 - 10:29 am UTC
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
mo, November 12, 2002 - 10:38 am UTC
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,
November 12, 2002 - 1:26 pm UTC
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?
Sam, December 18, 2002 - 4:36 pm UTC
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?
December 18, 2002 - 6:11 pm UTC
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
Tony, December 19, 2002 - 1:56 am UTC
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.
December 19, 2002 - 7:39 am UTC
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.
Jeff Larimore, December 19, 2002 - 8:57 am UTC
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
December 19, 2002 - 9:40 am UTC
I will submit this to the people that can make it happen...
That nasty word "embedded"
Connor McDonald, December 19, 2002 - 11:24 am UTC
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 ?
December 19, 2002 - 11:38 am UTC
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.
Tianhua Wu, December 19, 2002 - 12:17 pm UTC
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).
December 19, 2002 - 1:04 pm UTC
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
pk, December 19, 2002 - 4:09 pm UTC
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...
Connor McDonald, December 19, 2002 - 5:20 pm UTC
session 1:
select max(...)+1
session 2:
select max(...)+1
Ooops....data corruption.
Dave, December 20, 2002 - 12:23 am UTC
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.
December 20, 2002 - 8:08 am UTC
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???
Tianhua Wu, December 20, 2002 - 12:24 am UTC
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
George Johnson, December 20, 2002 - 8:30 am UTC
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
December 20, 2002 - 8:32 am UTC
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>
</code>
http://www.m-w.com/cgi-bin/dictionary?va=wazoo <code>
Dave, December 20, 2002 - 8:31 am UTC
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.
December 20, 2002 - 8:36 am UTC
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
Tony, December 20, 2002 - 9:07 am UTC
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.
December 20, 2002 - 9:23 am UTC
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
Pedro Diaz, December 20, 2002 - 10:45 am UTC
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.
December 20, 2002 - 11:02 am UTC
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
Jeff Larimore, December 20, 2002 - 4:02 pm UTC
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
December 20, 2002 - 8:14 pm UTC
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$.
Saibabu Devabhaktuni, February 26, 2004 - 3:56 am UTC
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?
February 26, 2004 - 10:06 am UTC
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
bn, July 22, 2004 - 4:00 am UTC
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
July 22, 2004 - 7:19 am UTC
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
bn, July 22, 2004 - 9:35 am UTC
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
July 23, 2004 - 7:30 am UTC
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...
Adam Musch, July 22, 2004 - 10:18 am UTC
... 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
bn, July 26, 2004 - 10:10 am UTC
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
July 26, 2004 - 11:56 am UTC
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
bn, July 27, 2004 - 10:38 am UTC
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
July 27, 2004 - 12:49 pm UTC
if you can use streams -- it is the preferred method going forward.
Assigning new field id inside trigger
Teymur Hajiyev, October 21, 2004 - 10:07 am UTC
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.
October 21, 2004 - 2:41 pm UTC
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
J1, December 23, 2004 - 12:05 pm UTC
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.
December 23, 2004 - 12:47 pm UTC
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.
J1, December 23, 2004 - 2:06 pm UTC
Seg.nextval
Anil, February 09, 2005 - 1:13 am UTC
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
February 09, 2005 - 2:12 pm UTC
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
Anil, February 10, 2005 - 1:53 am UTC
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
February 10, 2005 - 2:26 am UTC
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
Raghav, February 10, 2005 - 5:02 am UTC
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.
February 11, 2005 - 2:50 am UTC
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
Anil, February 10, 2005 - 5:08 am UTC
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
February 11, 2005 - 2:51 am UTC
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
Anil, February 11, 2005 - 6:01 am UTC
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
February 11, 2005 - 8:13 pm UTC
sequences
Anil, February 11, 2005 - 6:10 am UTC
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...
Dmytro, March 30, 2005 - 7:39 am UTC
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?.." ;)
March 30, 2005 - 8:44 am UTC
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??
Greg, March 30, 2005 - 9:22 am UTC
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??
;)
March 30, 2005 - 10:23 am UTC
(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...
Dmytro, March 30, 2005 - 9:37 am UTC
...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...
Dmytro, March 30, 2005 - 9:50 am UTC
...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
Richard, August 24, 2005 - 7:35 am UTC
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?
August 24, 2005 - 11:00 am UTC
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!
Richard, August 25, 2005 - 3:54 am UTC
Sequence no
AD, November 26, 2005 - 7:27 am UTC
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
November 26, 2005 - 12:32 pm UTC
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
Kumar, January 10, 2006 - 5:39 am UTC
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
A reader, January 19, 2006 - 6:16 pm UTC
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
Steve, March 07, 2006 - 12:44 pm UTC
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.
March 08, 2006 - 4:38 pm UTC
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
Martin, March 08, 2006 - 6:29 pm UTC
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.
March 09, 2006 - 1:00 pm UTC
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
Umesh Kasturi, March 09, 2006 - 12:17 am UTC
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
March 09, 2006 - 1:06 pm UTC
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!!
Greg, March 09, 2006 - 9:04 am UTC
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
Pradikan, March 29, 2006 - 5:09 am UTC
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
March 29, 2006 - 7:17 am UTC
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...?
Asher Cerka, April 13, 2006 - 8:44 pm UTC
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.
April 14, 2006 - 12:19 pm UTC
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
A reader, May 04, 2006 - 6:18 pm UTC
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
May 05, 2006 - 1:31 am UTC
you can come back and assign unique gap free numbers to rows, yes, if you wanted to.
Using rownum as a unique sequence
A reader, May 05, 2006 - 3:28 pm UTC
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
May 05, 2006 - 3:49 pm UTC
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
jhon edison b.c, May 05, 2006 - 4:00 pm UTC
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
May 05, 2006 - 4:55 pm UTC
Ok, I'll bite - where do the dates come from exactly - what "logic" produced them?
It is great
Subhrajit Bandyopadhyay, August 08, 2006 - 7:08 am UTC
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
Sanji, November 08, 2006 - 3:07 pm UTC
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
November 08, 2006 - 8:06 pm UTC
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
A reader, November 09, 2006 - 1:34 am UTC
November 09, 2006 - 8:30 am UTC
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
Hemal, November 16, 2006 - 5:02 am UTC
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
November 16, 2006 - 3:13 pm UTC
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
Maverick, November 16, 2006 - 4:23 pm UTC
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,
November 16, 2006 - 4:47 pm UTC
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
Hemal, November 17, 2006 - 4:33 am UTC
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
November 17, 2006 - 8:03 am UTC
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
Maverick, November 17, 2006 - 1:12 pm UTC
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,
November 20, 2006 - 2:11 am UTC
umm, sequences can run backwards.
so, you can set a minimum value they can run backwards to.
Min Value in sequences
AD, November 17, 2006 - 4:51 pm UTC
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
November 20, 2006 - 2:29 am UTC
that is it.
Regarding Oracle Sequences
Hemal, November 18, 2006 - 12:28 am UTC
Thanks a lot Tom.
I highly appreciate your help.
Best Regards
-Hemal
MinValue Still not sure :-)
Maverick, November 18, 2006 - 9:35 pm UTC
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.
November 20, 2006 - 2:34 am UTC
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
jimk, November 19, 2006 - 9:00 am UTC
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
Frank, November 30, 2006 - 9:02 am UTC
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.
November 30, 2006 - 10:03 am UTC
why?
Sequences
Frank, November 30, 2006 - 10:42 pm UTC
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.
December 01, 2006 - 5:17 am UTC
why
Regarding Sequences
Hemal, December 11, 2006 - 12:09 pm UTC
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
December 11, 2006 - 1:09 pm UTC
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
RP, December 14, 2006 - 12:20 pm UTC
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
December 15, 2006 - 8:26 am UTC
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
Hemal, December 14, 2006 - 12:43 pm UTC
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
December 15, 2006 - 8:28 am UTC
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
Hemal, December 17, 2006 - 9:46 pm UTC
Thanks Tom
I highly appreciate it.
Sequences again.... please help !
Fabio Franzotti, January 09, 2007 - 7:41 am UTC
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
January 11, 2007 - 9:23 am UTC
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
SamG, January 11, 2007 - 12:43 pm UTC
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.
YL, January 21, 2007 - 9:45 pm UTC
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
Sean, May 25, 2007 - 9:55 am UTC
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
May 26, 2007 - 12:00 pm UTC
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
Priya, June 26, 2007 - 5:43 pm UTC
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.
July 02, 2007 - 9:57 am UTC
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
A reader, July 30, 2007 - 9:41 am UTC
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
July 30, 2007 - 5:33 pm UTC
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
Wiktor Moskwa, July 31, 2007 - 3:06 pm UTC
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...
August 02, 2007 - 10:30 am UTC
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?
Jim, August 02, 2007 - 11:40 am UTC
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
martina, August 03, 2007 - 4:04 am UTC
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
August 05, 2007 - 1:31 pm UTC
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
Carsten, August 03, 2007 - 1:34 pm UTC
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.
August 05, 2007 - 1:44 pm UTC
well, until user uses one of them to level a table
or spills coffee
or .... whatever
I give up, go for it - be serial.
Carsten, August 06, 2007 - 7:59 am UTC
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.
August 06, 2007 - 12:03 pm UTC
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
Phil, August 06, 2007 - 11:34 am UTC
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
August 06, 2007 - 12:25 pm UTC
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
A reader, August 06, 2007 - 1:03 pm UTC
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
August 07, 2007 - 9:28 am UTC
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
Dario, August 06, 2007 - 7:19 pm UTC
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
Jeff, August 08, 2007 - 1:06 pm UTC
--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
Phil, August 09, 2007 - 4:25 pm UTC
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!
August 14, 2007 - 10:50 am UTC
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
Jeff, August 09, 2007 - 5:50 pm UTC
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
Wiktor Moskwa, August 14, 2007 - 3:34 pm UTC
============================
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
lowest ranking person in my workgroup, October 14, 2007 - 2:57 pm UTC
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?
Thomas Bender, March 17, 2008 - 12:28 pm UTC
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.
March 24, 2008 - 8:52 am UTC
It is not purist, it is just correct.
SEQUENCE CACHE SIZE
Stan, July 11, 2008 - 12:51 pm UTC
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.
July 11, 2008 - 1:37 pm UTC
i see no downsides
a gapless sequence
rc, August 02, 2008 - 6:47 am UTC
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.
August 03, 2008 - 2:03 pm UTC
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
rc, August 04, 2008 - 6:31 am UTC
>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!"
August 04, 2008 - 1:16 pm UTC
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
rc, August 04, 2008 - 3:09 pm UTC
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.
August 04, 2008 - 3:16 pm UTC
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?
rc, August 04, 2008 - 3:59 pm UTC
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.
August 04, 2008 - 8:34 pm UTC
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
Dieter Oberkofler, September 11, 2008 - 6:20 am UTC
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
September 16, 2008 - 12:18 pm UTC
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
Mani, December 09, 2008 - 8:41 am UTC
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
December 09, 2008 - 2:15 pm UTC
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
Umesh Kasturi, December 10, 2008 - 1:51 am UTC
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
December 10, 2008 - 9:46 am UTC
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
Mani, December 10, 2008 - 8:12 am UTC
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
December 10, 2008 - 10:00 am UTC
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
sPh, December 10, 2008 - 10:38 am UTC
> ) 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
A reader, February 07, 2009 - 8:53 am UTC
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
Anil, February 25, 2009 - 5:23 am UTC
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.
February 25, 2009 - 6:58 am UTC
... 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
Anil, February 25, 2009 - 7:38 am UTC
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.
February 25, 2009 - 5:49 pm UTC
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
Oleksandr Alesinskyy, February 26, 2009 - 5:47 am UTC
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.
March 03, 2009 - 7:39 am UTC
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.
Oleksandr Alesinskyy, March 03, 2009 - 10:49 am UTC
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
Mark M, March 09, 2009 - 6:11 pm UTC
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
A Reader, September 30, 2009 - 8:37 am UTC
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.
October 07, 2009 - 8:34 am UTC
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.
sequence increment
mfz, September 08, 2010 - 8:33 pm UTC
Why would the second merge increment the sequence value , as the "insert " phase in that statement was not triggerd
( ie , the latter part was not executed as all the records were already in the destination table) ?
SQL> drop table dept purge;
Table dropped.
SQL> create table dept as select * from scott.dept where 1=2;
Table created.
SQL> create sequence dept_id_seq increment by 1 start with 1 maxvalue 999 ;
Sequence created.
SQL> merge into dept d using
2 ( select deptno , dname , loc from scott.dept ) a
3 on (d.dname = a.dname)
4 when matched then
5 update set d.loc= a.loc
6 when not matched then
7 insert ( deptno , dname , loc )
8 values
9 ( dept_id_seq.nextval , a.dname , a.loc) ;
4 rows merged.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select dept_id_seq.currval from dual;
CURRVAL
----------
5
SQL> merge into dept d using
2 ( select deptno , dname , loc from scott.dept ) a
3 on (d.dname = a.dname)
4 when matched then
5 update set d.loc= a.loc
6 when not matched then
7 insert ( deptno , dname , loc )
8 values
9 ( dept_id_seq.nextval , a.dname , a.loc) ;
4 rows merged.
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select dept_id_seq.currval from dual;
CURRVAL
----------
9
SQL> select max(deptno) from dept;
MAX(DEPTNO)
-----------
5
SQL>
merge into dept d using
( select deptno , dname , loc from scott.dept ) a
on (d.dname = a.dname)
when matched then
update set d.loc= a.loc
when not matched then
insert ( deptno , dname , loc )
values
( dept_id_seq.nextval , a.dname , a.loc) ;
September 09, 2010 - 8:50 pm UTC
nextval is assigned for every row coming out of the rowsource - whether or not it is used.
it is OK
it is normal
it is what is supposed to happen.
sequences are NOT gap free, they never have been, never will, never are.
they will have gaps - it is unavoidable in all cases - no matter HOW you use them, they will have gaps. Not "may", but "WILL" have gaps.
and even if you use a sequence at the rate of a 1,000 per second - how many years do you think it would take to exhaust the sequence (you don't have to worry about that)... do the math - it is a long long long time.
Triggers instead of sequence.nextval,
A reader, September 01, 2011 - 3:44 pm UTC
Hello,
If we have a sequence, sequence.nextval would generate a value and can be inseted into a table along with other columns. Instead of that (for the convenience of programming), the development wants a before insert trigger on a table that generates the nextval and inserts into the primary key attribute.
for example: insert into emp (emp_id,ename,job) values (emp_id.nextval, 'abc','xyz'); -- this is I prefer
Instead the development wants to write as insert into emp(ename,job) values ('abc','xyz'); and the before insert trigger populates the emp_id with the help of sequence.
Is this trigger approach feasible when there are millions of rows inserted into the table every day?
Thanks
September 01, 2011 - 4:03 pm UTC
it is feasible, as in "yes, you can do it"
However, I hate that approach.
First - a trigger adds overhead, period. It consumes resources that are unnecessary.
Second - it introduces 'magic' into the process. The value is automagically assigned. Imagine the look on the next (or just another) developers face when they attempt to insert a specific value - and it just "disappears".
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html Third - triggers either prevent certain operations or when used with certain operations are "by passed"
ops$tkyte%ORA11GR2> create sequence s;
Sequence created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t ( x int primary key, y int );
Table created.
ops$tkyte%ORA11GR2> create or replace trigger t
2 before insert on t for each row
3 begin
4 :new.x := s.nextval;
5 end;
6 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert /*+ append */ into t (y) select 100 from dual;
1 row created.
ops$tkyte%ORA11GR2> select * from t;
X Y
---------- ----------
1 100
ops$tkyte%ORA11GR2> drop trigger t;
Trigger dropped.
ops$tkyte%ORA11GR2> insert /*+ append */ into t (x,y) select s.nextval, 100 from dual;
1 row created.
ops$tkyte%ORA11GR2> select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
see, direct path load via SQL is prevented with a trigger - you cannot do it (that we could query the table after the insert append demonstrates that we did not direct path load it)
Furthermore
ops$tkyte%ORA11GR2> create sequence s;
Sequence created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t ( x int primary key, y int );
Table created.
ops$tkyte%ORA11GR2> create or replace trigger t
2 before insert on t for each row
3 begin
4 :new.x := s.nextval;
5 end;
6 /
Trigger created.
ops$tkyte%ORA11GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE t
REPLACE
FIELDS TERMINATED BY '|'
(
y
)
BEGINDATA
100
200
ops$tkyte%ORA11GR2> !sqlldr / t direct=y
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Sep 1 17:00:58 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-26010: Column X in table OPS$TKYTE.T is NOT NULL and is not being loaded
direct path loading bypasses triggers altogether.
I would suggest to the programmer that they get paid to write code. Explicit code - code that shows exactly what it is doing is better than "magic" (in my opinion - after seeing triggers in action and growing to dislike them greatly since 1992). The convenience of this trigger is highly questionable as it does not take very much effort to type in sequence.nextval.
Triggers follow up,
A reader, September 01, 2011 - 4:33 pm UTC
Thanks. It was a very good demo. I am very much concerned from performance point of view.
From logical IOs and CPU consumption point of view, I did a small demo myself by inserting 10000 rows into a table with trigger and a similar another table without trigger.
With trigger here are the results:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10012 0.42 0.48 0 0 0 0
Execute 30010 7.14 7.32 2 10259 31158 20001
Fetch 10022 4.28 4.63 0 24 10000 10015
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50044 11.86 12.44 2 10283 41158 30016
Without trigger here are the results:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10022 0.42 0.51 0 0 0 0
Execute 20021 10.43 10.89 0 10270 41136 20005
Fetch 29 0.00 0.00 0 34 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30072 10.86 11.41 0 10304 41136 20023
The logical IOs and CPUs are slightly different but will it matter a lot when a 10 million rows are inserted instead of 10000?
Thanks
September 02, 2011 - 8:32 am UTC
well, let's look at it from a bulk and slow by slow perspective and see what we see:
ops$tkyte%ORA11GR2> create sequence s cache 10000;
Sequence created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t1 ( x int primary key, y varchar2(30) );
Table created.
ops$tkyte%ORA11GR2> create or replace trigger t1
2 before insert on t1 for each row
3 begin
4 :new.x := s.nextval;
5 end;
6 /
Trigger created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t2 ( x int primary key, y varchar2(30) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t as select level id, rpad( 'x', 25, 'x') data from dual connect by level <= 1000000;
Table created.
ops$tkyte%ORA11GR2> insert /*+ append */ into t select id+1000000, data from t;
1000000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert /*+ append */ into t select id+2000000, data from t;
2000000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert /*+ append */ into t select id+4000000, data from t;
4000000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert /*+ append */ into t select id+8000000, data from t where rownum <= 2000000;
2000000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select count(*) from t;
COUNT(*)
----------
10000000
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> insert into t1 (y) select data from t;
10000000 rows created.
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> insert into t2 (x,y) select s.nextval, data from t;
10000000 rows created.
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 53517 cpu hsecs
Run2 ran in 7802 cpu hsecs
run 1 ran in 685.94% of the time
<b>as you can see - that trigger would definitely impact the cpu utilization - 6 to 7 times as much CPU for a bulk insert with triggers..</b>
Name Run1 Run2 Diff
LATCH.AWR Alerted Metric Eleme 19,624 0 -19,624
LATCH.cache buffers chains 4,426,449 4,406,085 -20,364
LATCH.object queue header oper 715,628 738,149 22,521
STAT...dirty buffers inspected 5,352 32,127 26,775
STAT...table scan rows gotten 10,425,064 10,396,984 -28,080
STAT...recursive cpu usage 30,896 210 -30,686
STAT...Elapsed Time 46,278 8,564 -37,714
STAT...DB time 46,281 8,562 -37,719
STAT...session cursor cache hi 40,089 1,423 -38,666
STAT...CPU used by this sessio 53,519 7,802 -45,717
STAT...CPU used when call star 53,520 7,802 -45,718
STAT...session uga memory 0 -65,512 -65,512
STAT...free buffer inspected 174,446 285,933 111,487
LATCH.simulator hash latch 298,079 176,139 -121,940
STAT...session pga memory 65,536 -65,536 -131,072
STAT...redo synch time (usec) 3,312 233,532 230,220
LATCH.row cache objects 907,901 650,707 -257,194
STAT...undo change vector size 282,414,280 284,083,944 1,669,664
STAT...file io wait time 3,794,793 8,462,268 4,667,475
STAT...redo size ######################## 4,840,460
STAT...calls to get snapshot s 10,022,819 22,721 -10,000,098
STAT...execute count 10,001,582 1,436 -10,000,146
STAT...opened cursors cumulati 10,001,581 1,435 -10,000,146
STAT...recursive calls 10,005,978 4,685 -10,001,293
LATCH.shared pool 10,021,707 3,327 -10,018,380
<b>you can see the effect of the trigger here clearly...</b>
STAT...physical write total by 423,690,240 455,467,008 31,776,768
STAT...physical read bytes 400,883,712 448,143,360 47,259,648
STAT...physical read total byt 414,646,272 462,938,112 48,291,840
STAT...cell physical IO interc 838,336,512 918,405,120 80,068,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
47,173,232 36,715,932 -10,457,300 128.48%
PL/SQL procedure successfully completed.
<b>and it won't scale as well - as you add users - it'll take even more CPU for the trigger approach as it will hit more latch contention</b>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> truncate table t1;
Table truncated.
ops$tkyte%ORA11GR2> truncate table t2;
Table truncated.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> begin
2 for x in ( select * from t )
3 loop
4 insert into t1 (y) values ( x.data );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> begin
2 for x in ( select * from t)
3 loop
4 insert into t2 (x,y) values( s.nextval, x.data );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop(10000);
Run1 ran in 188177 cpu hsecs
Run2 ran in 123232 cpu hsecs
run 1 ran in 152.7% of the time
<b>even in slow by slow - where we do a lot of work because of the slow by slowness of it all - you need one and a half times as much CPU for the trigger...</b>
Name Run1 Run2 Diff
LATCH.undo global data 398,299 387,987 -10,312
LATCH.object queue header oper 1,498,104 1,509,006 10,902
LATCH.SQL memory manager worka 36,829 24,845 -11,984
LATCH.enqueues 75,901 60,522 -15,379
LATCH.enqueue hash chains 128,506 111,338 -17,168
LATCH.checkpoint queue latch 366,101 348,276 -17,825
LATCH.AWR Alerted Metric Eleme 0 19,742 19,742
STAT...session cursor cache hi 12,564 -26,422 -38,986
STAT...file io wait time 435,337 475,276 39,939
LATCH.cache buffers chains 163,588,815 163,541,555 -47,260
STAT...Elapsed Time 163,763 109,246 -54,517
STAT...DB time 163,764 109,246 -54,518
STAT...recursive cpu usage 179,178 115,144 -64,034
STAT...CPU used by this sessio 188,177 123,234 -64,943
STAT...CPU used when call star 188,179 123,234 -64,945
STAT...session pga memory -65,536 0 65,536
STAT...table scan rows gotten 29,535,405 29,370,435 -164,970
STAT...undo change vector size######################## -606,276
LATCH.row cache objects 1,449,043 706,171 -742,872
LATCH.simulator hash latch 2,698,873 1,950,070 -748,803
STAT...db block gets from cach 9,991,518 9,144,963 -846,555
STAT...redo size ######################## -1,312,228
STAT...physical read bytes 449,101,824 450,625,536 1,523,712
STAT...physical read total byt 467,681,280 459,227,136 -8,454,144
STAT...execute count 20,002,057 10,001,597 -10,000,460
STAT...opened cursors cumulati 20,002,061 10,001,601 -10,000,460
STAT...recursive calls 20,113,361 10,109,266 -10,004,095
STAT...physical write total by############ 705,208,320-307,175,424
STAT...cell physical IO interc########################-315,629,568
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
211,063,218 209,445,652 -1,617,566 100.77%
<b>the latching isn't as much different because of the fact we use so many latches in general with the slow by slow approach - but the statistics show themselves - a lot more work is done with the trigger</b>
PL/SQL procedure successfully completed.
follow up,
A reader, September 02, 2011 - 9:22 am UTC
Oh my god!! this is fantastic demo. I am going to repeat this demo and give them the similar results. Hope they can digest it.
Thanks a lot.
Sequence on Partitioned Table
Rajeshwaran, Jeyabal, September 12, 2011 - 12:18 pm UTC
Tom:
Can you please help me why the sequence is not getting populated here?
Is that sequence are not supported on Parallel DDL?
rajesh@ORA10GR2> drop sequence s;
Sequence dropped.
Elapsed: 00:00:00.03
rajesh@ORA10GR2> create sequence s cache 1000000;
Sequence created.
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t
2 (
3 x,
4 y
5 )
6 partition by hash(x)
7 partitions 8
8 nologging
9 parallel 4
10 as
11 select /*+ parallel(e,4) */ s.nextval as id,
12 rpad('*',100,'*') as y
13 from big_table e
14 /
Table created.
Elapsed: 00:00:25.25
rajesh@ORA10GR2> select count(*),count(x) from t;
COUNT(*) COUNT(X)
---------- ----------
6030630 0
Elapsed: 00:00:02.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> select last_number
2 from user_sequences
3 where sequence_name ='S'
4 /
LAST_NUMBER
-----------
7000001
Elapsed: 00:00:00.04
rajesh@ORA10GR2>
rajesh@ORA10GR2>
Sequence on Partitioned Table
Rajeshwaran, Jeyabal, September 14, 2011 - 7:30 pm UTC
Tom:
Can you answer the above question? Let me know if you need additional information.
September 15, 2011 - 7:31 am UTC
I'll bug that - it looks like a bug...
Why do my sequences start with 2?
A reader, November 10, 2011 - 4:03 pm UTC
I installed the personal edition database on my Windows XP Professional SP3 laptop at work to be my sandbox and I noticed while building tables that all my sequences seem to be starting with the number 2 instead of 1. I had my box reimaged for other reasons and I reinstalled the database a second time, and I still have all my sequences starting with the number 2.
It is not a big deal to me that this is happening, I am just concerned that it might indicate something I did wrong in the installation. Should I be worried about this? Is there something I can look at to fix this issue?
SQL> create table test (seq number);
Table created.
SQL> create sequence s_seq;
Sequence created.
SQL> insert into test (seq) values (s_seq.nextval);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
SEQ
----------
2
November 10, 2011 - 5:59 pm UTC
I'll guess
you are 11.2.0.1
you are using deferred segment creation
you are hitting a side effect whereby the first insert would be totally restarted after the initial insert created the segment - so the insert is really run twice.
It would not happen in 11.2.0.2 where this product issue was corrected.
This issue could really be a bummer when you did something like:
insert into table select <really complex select here>;
it would run the query - figure out "hey, segment doesn't exist, create it" and then... yes - restart the insert all over.
I'm guessing - did I guess right? If you create a table:
create table t ( x int ) segment creation immediate;
you should not see this side effect.
You guessed right.
A reader, November 11, 2011 - 10:05 am UTC
Creating the table with that segment clause fixed the issue. I'll upgrade to 11.2.0.2, that will be a good bit of experience for me. I am a PL/SQL Developer, so I have never done an upgrade before. Thank you for the quick response.
Sequence
Ritu, January 18, 2012 - 6:51 am UTC
Hi Tom,
Is it possible in oracle to generate 100 or n number of Sequence number in advance.
Thanks,
Ritu
January 18, 2012 - 7:43 am UTC
ops$tkyte%ORA11GR2> create sequence s;
Sequence created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 type array is table of number index by binary_integer;
3 l_data array;
4 begin
5 select s.nextval bulk collect into l_data
6 from dual
7 connect by level <= 100;
8 end;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select s.nextval from dual;
NEXTVAL
----------
101
Use of Sequence
Hamidur Rahman Siddique, January 23, 2012 - 5:30 am UTC
I read about sequence. I have no obligation to use sequence if i solve this issue.
I need to re numbering when year change or month change.
How can i do this with sequence ?
January 23, 2012 - 6:22 am UTC
you'll have to explain what you want to do a bit better.
"i need to renumber when year or month change"
renumber what?
renumber how?
what is the logic, what rows are you renumbering, what are your rules for renumbering.
do it like a specification, one that you could write code from.
otherwise, just update t set c = sequence.nextval - it'll renumber everything, but probably won't do what you really want (which is something we do not yet know)
re-numbering by sequence
Hamidur Rahman Siddique, January 23, 2012 - 12:41 pm UTC
Sorry for little explain. I have to make a invoice code like
INV.007412.11
Next year
INV.000001.12
Ok ? Now INV is come from another table. last 11/12 come from date. middle part should be a column value with number data type and all three things are concatenate with format mask.
invoice_code:='INV'||ltrim(to_char(:inv_id,'000000'))||to_char(:date,'RR');
inv_id are inserted from sequence.
My question
How inv_id will start from 1 at the new year start ?Hope you understand..
January 23, 2012 - 3:40 pm UTC
why do you care if they start from 1?
since sequences ARE NOT GAP FREE
NEVER will be GAP FREE
CANNOT made to be GAP FREE
you don't know if there will be a 1, or a 2, or a 3 - or in fact "N" in general. Even if you reset the sequence - you dropped and recreated it - there is no assurance that sequence 1 would ever be generated or used.
Now what?
In order to do this, you will need a manual process to reset the sequence (drop and recreate it) at midnight on december 31st in the appropriate time zone.
Or, you will need to wrap the above in a function - so you call a function F, and that function would "do the right thing" and use the "right sequence" starting at midnight on december 31st - something like:
create function get_silly_id return varchar2
as
begin
if (to_char(sysdate,'yyyy')='2011')
then
return 'INV.' || to_char(2011_seq.nextval, 'fm000000') || '.11';
else
return 'INV.' || to_char(2012_seq.nextval, 'fm000000') || '.12';
end if;
end;
and sometime during 2012, create or replace that function to reference 2012 and 2013 instead of 2011 and 2012.
You must be creating software for very very very very tiny companies. Just a million maximum possible invoices - and since sequences ALWAYS HAVE GAPS - much less than a million invoices per year. You might want to seriously rethink that. why not just use a sequence? If you did that - and you generated 100,000 sequences per second forever - it would take trillions of years to use them all up!
Currval of sequence for entire transaction
A reader, August 28, 2014 - 11:05 am UTC
I have a table
create table
test( l number , i number );
create table
test2(l number , i number );
create sequence s ;
I need to automatically insert into these tables the same value of sequence s into column l for the same transaction thru a trigger in both the tables for all insert values.
How to do this ?
So when I insert into test and test2
begin
insert into test(i) values (2) ;
insert into test(i) values (6) ;
insert into test(i) values (8) ;
insert into test2(i) values (1) ;
insert into test2(i) values (3) ;
insert into test2(i) values (4) ;
commit ;
end ;
I need in tables the following values
test ( l , i )
1,2
1,6
1,8
and in test2 (l, i)
1,1
1,3
1,4
Now another transaction
should give next value of sequence s for the entire transaction
These inserts are being called by third party application
Update without select for update for get_nextval function?
Pasko, May 21, 2016 - 9:33 am UTC
Hi Tom,
I was just reviewing this post after i recently got a requirement to implement gapless sequence numbers for an application ported from db2 ;)
I have a question regarding your suggested implementation of the function get_nextval below.
Should we also serialize with a 'select for update' before doing the update?
/Quote/
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 );
....
....
Regards,
Pasko
May 22, 2016 - 3:54 am UTC
No need to, because the update will lock the row (or wait if someone else already has it).
You'll hold that lock until you commit (hence only person gets to obtain a sequence number at a time).