how to implement multiple sequences on a single table 2

[misterstick]

i'd like to be able to have an arbitrary number of
sequences against a single table.

i realise that general algorithm questions like this aren't
really what this forum is about, but i thought someone
might have come across this problem before, or could find
or have found a solution somewhere.

some background.

we use a batching algorithm to import transactions.

users create a batch of posting records, update the batch
and the process validates and updates the data from the
postings (external to the system) into transactions
(internal) adding it into all necessary summary tables at
the same time.

each batch of postings is described in a header record
called, surprisingly BATCH_HEAD. the BATCH_HEAD records
have an arbitrary number as their unique identifier
BATCH_NO. for auditing purposes these BATCH_NOs must be
sequential, there must be no gaps.

in days gone by when we had direct files (CISAM, etc) there
was no problem with this, just store the last number in a
single record key file, and lock and update it as necessary.

in oracle, we've has to use sequences because locking
doesn't work. this works well enough: the write of a
BATCH_HEAD record is done close enough to the number
getting pulled off the sequence that missing numbers are so
far not an issue.

time passes, specs change.

the system has been updated so that it is multi-company.
the number of companies is arbitrary and user-defined.

the BATCH_HEAD table is now keyed by company and batch_no.
the batch numbers must still be sequential and gapless, but
now within company: company 1 gets its own set of batches
within the table numbered 1 to m, and if company 2 exists
it does too, numbered 1 to n.

in CISAM the logic doesn't change much, you just add a
record to the key table for each company and retrieve
records by company. the lock, read, update, unlock
processing works just as well.

in oracle, however, things aren't so simple. i can think of
several ways to do this. each, however, requires a giant
mess of PL/SQL.

although excited by the prospect of writing densely
impenetrable logic i'd really rather try and keep things as
simple as possible.

anybody have any ideas?

many thanks,


mr s. <

 

[ChrisHunt]

in oracle, we've has to use sequences because locking doesn't work.

In what way does locking "not work" in Oracle? That's not my experience.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[misterstick]

it wasn't me that did the testing, but i believe that oracle is write and not read locking.

if i try to read a value from a locked table in CISAM it waits until the lock is released to give it to me.

if i try to read a value from a locked table in ORACLE it gives it to me immediately, and only complains if i try to write to it. adding in transactions only makes things worse.

"doesn't work" meaning "is not suitable for this purpose" not "is broken."



mr s. <

 

[Dagon]

To guarantee that transactions are gapless you can't use sequences anyway. The whole point of sequences is that there is no locking on them and any number of users can grab numbers at the same time. This is entirely deliberate and not any sort of bug.

It's perfectly possible to do something similar in Oracle to what you were doing in CISAM. You just have a table with the latest sequence number on it. At the start of the transaction, you do a select for update to get the latest sequence number. Any other transactions trying to get a sequence number will then be forced to wait. You create your records, update the table to the next sequence number and commit.

The principle is exactly the same for sequence numbers within a company. You just extend your table to have company number in it and select the latest sequence number for each company. I doubt any of this would involve "a giant mess of PL/SQL".

The downside, of course, is that it hits performance if there are a lot of concurrent transactions. If that's a problem, just explain to your users that it is the price they pay for their spurious requirement (and it nearly always is a spurious requirement) of having gapless sequences.

As for Oracle locking not working, that is a nonsense statement you have made without any evidence or explanation. Locking is actually one of Oracle's strongest features and works extremely well.

 

[Dagon]

if i try to read a value from a locked table in ORACLE it gives it to me immediately, and only complains if i try to write to it. adding in transactions only makes things worse.

Use "select for update".

 

[misterstick]

for update? that's all?

now, where were you six years ago when we first moved to oracle?


mr s. <

 

[misterstick]

oh, and the giant mess of PL/SQL was to create and delete company-specific sequences on the fly...



mr s. <

 

[Thargy]

stick,

I can't restrain myself any longer, that's just dreadful!

Why flaff about with sequeces anyway? They're not the problem, the aforementioned requirement is. You don't need a sequence to generate a unique company identifier anyway, that's usually by referential integrity to a company table, which might have an integer primary key (created from a sequence of course )

Why are end users allowed to specify 'gapless sequences' when they know nothing of Oracle? Or maybe, they know enough to cause trouble, but not enough to fix it.

There may legitimately be a business requirement to demonstrate that the audit is complete, and uninterrupted, but there is definitely no business requirement for gapless sequences. What if you move to sql server? Will the business cease trading, simply because sql server uses autonumbers?

Yours in dismay,

Tharg

Grinding away at things Oracular

 

[Dagon]

Totally agree. This obsession with not having gaps in sequences comes up time and time again in forums. It's always something that is driven by business users who don't understand the point of the unique key and are trying to use it for something that it was never intended to be.

I sometimes think that having a sequence which keeps increasing is a bad idea. Instead it should generate completely random numbers (providing it has some mechanism of guaranteeing not to generate the same number again). Then maybe the users would get the idea that this is just a way of uniquely identifying the records, not a method of recording when they arrived on the system or checking that no records were missed.

 

[misterstick]

hmm.

1. our solution is trying to be as database neutral as it can be. the problems we're currently having are because we weren't in a previous incarnation of the code. last number tables would have been my first choice, and now that i know that they can work in oracle i'll try my damnedest to make them. i'm using third-party software (powerhouse) which can be a bit cranky but i'm quietly optimistic as ever.

2. i understand that any pl/sql solution wouldn't have been optimal and that the one set out above is probably one of the worst.

3. user requirements are user requirements. they pay the bills, they get final say. i have found that "my database doesn't work that way" is one of the least persuasive arguments ever.

4. sequential numbering is a simple, elegant, easily understood solution to ensuring data completeness. users get it without it having to be explained to them. first graders could understand it and third graders implement it. what are the alternatives? daisy chaining of some kind?

many thanks for the input.




mr s. <

 

[Dagon]

In what way does it ensure completeness ? If you are using sequences, you could have a database crash just after you have selected a sequence number but before the data is committed. You try again and it works, but you get a higher sequence number. In that case you would have a gap but you wouldn't really have missed anything.

Likewise you could miss a row because it fails for some reason and rolls back. It also rolls back its update of the sequence number table, but all the other rows go through. In that case, your sequence is perfectly in order but you've still missed rows.

It seems to me a much better approach to checking completeness would be to ensure you detect any Oracle errors which occur and to audit the data against the source system. Having a sequence number that is perfectly in order proves nothing about system integrity. It might look prettier and appeal to the mentality of people who are really fixated about such things, but that's about it.

 

[SantaMufasa]

user requirements are user requirements. they pay the bills, they get final say.

That's correct...when my daughter went in for some rather complex brain surgery, I specified to the surgeon that I wanted him to approach the surgical area by entering the posterior cerebral cortex, traverse the anterior region of the corpus callosum, then skirt the thalamus, finally reaching the hypothalmus to focus on her problem. The surgeon had to follow my specifications, because I pay the bills, I get final say. <grin>


If your users are specifying how to uniquely identify rows and to implement referential integrity, then you need to send them back to User's School. In your case, they are not creating requirements...they are, instead, frustrated designers!!! If they want to design, then let them design and build the applications. If they want a well-running application, then let them identify business requirements and leave the design and build up to the professionals.

Otherwise, you turn the asylum over to the inmates.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[misterstick]

yup, sequences sure are suboptimal.

the last number table logic goes something like this.

begin transaction
lock last_number table
read last_number record
increment last_number and assign to key value on new_record
write last_number
unlock last_number table
write new_record
end transaction commit

if anything fails it's all rolled back.

this ensures that each record that is written to the database has the next sequential number to the previous one.

since very little processing is done, and volumes are relatively low, the waiting times for lock release have historically been pretty small.

as i said, requirements are requirements: pointing out the existence of the following code is pretty unhelpful.

alter trigger some_table_audit disable;
update some_table set ( key_value ) = ( rownum ) where company = 1;
alter trigger some_table_audit enable;

mr s. <

 

[misterstick]

ninja'd.

SantaMufasa:

unfortunately for your metaphor this isn't brain surgery.

think of it more as building a house. the owner does get final say over materials and how they are used. saying that they can't have a window of a particular shape because we don't know how to install it should get us fired.

they give us a spec which we implement. if we think one of their requirements is dumb (which it often is) we can explain the reasons why we think it's dumb but ultimately we don't have final say over anything.


mr s. <

 

[Thargy]

Stick,

we have such people here in the U.K. who want houses built "their way". Fortunately for us all, the government went bonkers and passed a law requiring that all houses (or any building for that matter) must comply with the building regulations. As a result, our houses don't fall down. If a user wants elegant timber, instead of a rough-sawn four by two, they can have it, provided it complies with the regulations.

To give a U.S. centric analogy, if you were the customer, and you paid the bills, would you demand that your gun supplier manufactured it with a "no safety catch" option, and to further reduce cost, you chose the "no trigger guard" option?

<Awaits transatlantic bullet>

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

Okay, then if you don't like the "brain surgery" analogy, then we'll use your construction analogy:

I agree that "materials and how they are used" and "window of a particular shape" are both within the scope of customer specifications. But, if that same customer tells you that you should use the butt-end of a screw driver to "hammer" in all of your nails, then that is about the same as specifying how your unique identifiers should behave. If you choose to accommodate that style of client "overstepping", and call it client specifications (under the guise that "the customer is always right"), then that is certainly your prerogative.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[misterstick]

ok. now i see what the problem with metaphor is.

right. the client gave me a requirement. not a particularly difficult one to understand or implement.

in the non-relational world this kind of requirement is easily satisfied. one of the benefits of simplicity, i guess.

your answers aren't paricularly helpful. telling my client "you're an idiot to want this" will probably be as pursuasive as "my database doesn't work that way."

i agree that user interference in coding decisions would be intensely irritating.

unfortunately in this case all they've done is tell me what they want. they don't care how i deliver, just that i do.

my case for "well, oracle doesn't do that, it does this" is really rather undermined by the fact that X-SAM does, and has done reliably in this application for over twenty years.

still, however this discussion ends, i have my answer, for which i am grateful.

mr s. <

 

[Thargy]

stick,

nobody's saying that your client's are idiots, just that they should state their requirements and let you deliver, as best you can, with the technology available.

One obvious point to make is that since they've asked you to move on from X-SAM, they're implicitly accepting that change is inevitable, since it would be unreasonable to expect any other technology to work in precisely the same way as X-SAM, right?

If X-SAM is so good, and they've only had it for 20 years, why change? Just keep right on with it, for ever...

If you tell them that Oracle doesn't work that way, and they suggest SQL Server instead, you might point out that you can't guarantee no breaks in numbering there either, because SQL Server's auto numbers don't (to my knowledge) guarantee break free numbering either (I don't know of any system that does.)

Your clients have to accept that any technology has its own ways of working, along with benefits and limitations.

Does this help any with enlightening your clients?

T

Grinding away at things Oracular

 

[ChrisHunt]

I'm going to come to misterstick's defence, and to that of his users.

Just suppose, for a moment, that you're an ordinary design who knows little or nothing about the internals of this system, or about database design in general. Say you get a list of 100 transactions, labelled 1-98 and 100-101. Aren't you going to wonder where transaction 99 went? Maybe ask somebody (who'll tell you that it doesn't matter because that's how the system works). If these transaction numbers go out to customers, a sharp-sighted client might spot the same gap and ring up about it. Later, it's the auditor's turn to raise it as an issue.

Of course it doesn't actually matter. All these people can be told (correctly) that it isn't a problem, but all that telling can become a problem in itself.

There's also the issue of people's perception of the system. They're going to think "If our system is too dumb to count from 1 to 100 without missing numbers, what other mistakes is it making?" Users can place inordinate importance on the most superficial things, sometimes you just have to humour them.

I've worked on systems where it's left gaps in very visible numbers. The first time you get a support call saying "there's a gap in the numbers", you just tell them not to worry about it. By the time you get the tenth call on the subject, you wonder whether it wouldn't be easier to code the system not to produce gaps in the first place.

After all, how hard can it be? Numbers are what computers are supposed to handle best, aren't they? Sure, it's a pain to write extra code when you could just pull an arbitrary number from a sequence, but it's not rocket science. Sometimes you have to pick your battles - do you want to spend 15 minutes doing something pointless, or would you rather answer 15 pointless support calls? I'd pick the one that keeps the customer happy.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LKBrwnDBA]

My 2c:

Also, in some cases there may be "legal" requirements to keep sequential track of transactions/contract numbers/invoice numbers/permit numbers/etc...(particularly when working/dealing with government).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb