Home » SQL & PL/SQL » SQL & PL/SQL » "sub"sequence generator problem
"sub"sequence generator problem [message #250130] Sun, 08 July 2007 09:55 Go to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
Hi all,

I have a transaction table where I store business transactions. This is a multi client system and the XID should be unique in combination with the client ID.

Is there any way to achieve this with basic sequence generator methods or do I have to implement this using triggers (select max ...)? I wanted to avoid doing this select max thing as I thing this will end up in double keys some day.

The problem is that client A should not be able to guess how many transactions the other clients are processing (and this would be the case if I would use a standard sequence for the XID).

Thanks, Best Regards,
Martin
Re: "sub"sequence generator problem [message #250132 is a reply to message #250130] Sun, 08 July 2007 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about a XID = clientId||seqNumber?

Regards
Michel
Re: "sub"sequence generator problem [message #250136 is a reply to message #250132] Sun, 08 July 2007 11:08 Go to previous messageGo to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
Yes, that's what I wanted to do, but I want this sequence number to be with as little gaps as possible. Client A should have 1,2,3 and client B as well. So that's my problem and what I meant in the titl e "sub"sequence. I need a sequence "below" client id.

Thanks, BR
Martin
Re: "sub"sequence generator problem [message #250143 is a reply to message #250136] Sun, 08 July 2007 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gap is irrelevant.
Give me a good reason for "as little gaps as possible".

Regards
Michel

[Updated on: Sun, 08 July 2007 11:46]

Report message to a moderator

Re: "sub"sequence generator problem [message #250179 is a reply to message #250143] Mon, 09 July 2007 00:50 Go to previous messageGo to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
The problem with ONE sequence for all clients' transactions is that client A knows how many transactions the other clients are doing. And that is a no go.
Also for account reasons your invoice numbers for instance have to be gapless.

Martin
Re: "sub"sequence generator problem [message #250196 is a reply to message #250179] Mon, 09 July 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The problem with ONE sequence for all clients' transactions is that client A knows how many transactions the other clients are doing

So use a sequence per client.

Quote:
for account reasons your invoice numbers for instance have to be gapless

This is not possible without locking the whole table during each transaction. That is serialize all transactions.
Good luck !

Regards
Michel
Re: "sub"sequence generator problem [message #250205 is a reply to message #250196] Mon, 09 July 2007 02:25 Go to previous messageGo to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
"use one sequence per client" is exactly what I wanted to do. But is there a better way to do this instead of creating one sequence object per client? I was hoping there is a way of creating a sequence in dependence of the client id, so I could pass a parameter and get the next value for that client.

Thank you very much, Rgrds
Martin
Re: "sub"sequence generator problem [message #250209 is a reply to message #250196] Mon, 09 July 2007 02:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 09 July 2007 08:37
Quote:
The problem with ONE sequence for all clients' transactions is that client A knows how many transactions the other clients are doing

So use a sequence per client.



Sorry to say, but that is, to put it mildly, a strange advice. You're telling him to create a database object for every new entry in the clients-table, thus forcing him to use dynamic sql (to use the correct sequence) for the orders-trigger.
Re: "sub"sequence generator problem [message #250211 is a reply to message #250205] Mon, 09 July 2007 02:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The requirement is that a sample of sequence numbers observed over a period of time should not give any indication as to the total number of sequences allocated over that period.

Client-sub-sequences are not the only solution to this problem.

Three ideas that immediately present:
  • Allocate a sequence number to a user at the beginning of their session, append (say) 6 zeros and then add 1 to get an identifier for each transaction. In this way, one user may allocate numbers 12300001, 12300002, ..., 12300010 and another user may allocate 12400001 - 124000788. If there is sufficient variance in the number of transactions created per session, then any assumptions from a random sampling are pure guesswork.
  • Have a background process that - at random times during the day - changes the INCREMENT of the sequence to a random integer between 1 and 1000000.
  • Have a background process that allocates and discards a random number of transaction numbers.


Ross Leishman
Re: "sub"sequence generator problem [message #250217 is a reply to message #250211] Mon, 09 July 2007 03:06 Go to previous messageGo to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
Thanks - I like the idea of changing the increment several times a day - I think this is pretty easy.

I also thought about using GUIDs - any thoughts about GUIDs as PK?

Thanks a lot,Rgrds,
Martin
Re: "sub"sequence generator problem [message #250223 is a reply to message #250209] Mon, 09 July 2007 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You're telling him to create a database object for every new entry in the clients-table, thus forcing him to use dynamic sql (to use the correct sequence) for the orders-trigger.

If each client connects with its userid, having each one a sequence in his schema don't force to use dynamic SQL. It depends on the implementation.

Regards
Michel
Re: "sub"sequence generator problem [message #250224 is a reply to message #250217] Mon, 09 July 2007 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I like the idea of changing the increment several times a day

But what about the gaps you talked about?

Quote:
any thoughts about GUIDs as PK?

Why not, but what about the gaps you talked about? There is no relation between 2 GUIDs.

Regards
Michel
Re: "sub"sequence generator problem [message #250229 is a reply to message #250224] Mon, 09 July 2007 03:33 Go to previous messageGo to next message
martin1215
Messages: 7
Registered: July 2007
Junior Member
I will have a seperate table for invoice numbers and then I can lock the whole table. So I will NOT use invoice number as PK.

This is not very elegant, but it should work.
Re: "sub"sequence generator problem [message #250231 is a reply to message #250229] Mon, 09 July 2007 03:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd say: use a view. Here's my script:
cl scr
set pages 20

CREATE SEQUENCE mhe_acc_seq START WITH 1 INCREMENT BY 1 NOCYCLE
/
CREATE TABLE mhe_accounts( acc_id     NUMBER
                         , client_id  VARCHAR2(10)
                         , CONSTRAINT mhe_accounts_pk PRIMARY KEY (acc_id))
/

CREATE OR REPLACE TRIGGER mhe_accounts_bri
  BEFORE INSERT ON mhe_accounts
  FOR EACH ROW
BEGIN
  SELECT mhe_acc_seq.nextval
  INTO   :NEW.acc_id 
  FROM   dual;
END;
/

INSERT INTO mhe_accounts(client_id) VALUES ('AA');
INSERT INTO mhe_accounts(client_id) VALUES ('AA');

INSERT INTO mhe_accounts(client_id) VALUES ('BB');
COMMIT;

INSERT INTO mhe_accounts(client_id) VALUES ('AA');
ROLLBACK;

INSERT INTO mhe_accounts(client_id) VALUES ('AA');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
COMMIT;

INSERT INTO mhe_accounts(client_id) VALUES ('AA');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
ROLLBACK;

INSERT INTO mhe_accounts(client_id) VALUES ('CC');
INSERT INTO mhe_accounts(client_id) VALUES ('DD');
INSERT INTO mhe_accounts(client_id) VALUES ('EE');
INSERT INTO mhe_accounts(client_id) VALUES ('CC');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
INSERT INTO mhe_accounts(client_id) VALUES ('BB');
COMMIT;

SELECT acc_id
     , client_id
FROM   mhe_accounts
ORDER BY client_id, acc_id
/

CREATE OR REPLACE VIEW mhe_acc_vw
AS
SELECT client_id||TO_CHAR(row_number() OVER ( PARTITION BY client_id ORDER BY acc_id ),'FM00000') ext_acc_id
     , acc_id                                                                                    acc_id
     , client_id
FROM   mhe_accounts
/

SELECT *
FROM   mhe_acc_vw
ORDER BY client_id, acc_id
/
     
DROP VIEW mhe_acc_vw
/

DROP TABLE mhe_accounts
/
DROP SEQUENCE mhe_acc_seq
/
When I run it, I get this:

Sequence created.


Table created.


Trigger created.


1 row created.

...
...snip...
...

1 row created.


Commit complete.


    ACC_ID CLIENT_ID
---------- ----------
         1 AA
         2 AA
         5 AA
         3 BB
         6 BB
         7 BB
        15 BB
        16 BB
        11 CC
        14 CC
        12 DD
        13 EE

12 rows selected.


View created.


EXT_ACC_ID           ACC_ID CLIENT_ID
---------------- ---------- ----------
AA00001                   1 AA
AA00002                   2 AA
AA00003                   5 AA
BB00001                   3 BB
BB00002                   6 BB
BB00003                   7 BB
BB00004                  15 BB
BB00005                  16 BB
CC00001                  11 CC
CC00002                  14 CC
DD00001                  12 DD
EE00001                  13 EE

12 rows selected.


View dropped.


Table dropped.


Sequence dropped.

SQL>
Is this what you were looking for?

MHE
Re: "sub"sequence generator problem [message #250238 is a reply to message #250231] Mon, 09 July 2007 04:29 Go to previous message
martin1215
Messages: 7
Registered: July 2007
Junior Member
Thanks. Looks good.

My only concern: performance.

I have to return the XID right after I processed the transaction. So I am not in the situation where I would do a SELECT normally. So this would be one query more per transaction. And what if I only want to select ONE transaction by XID - does the ROW_ID trick work then?
Previous Topic: What is signature?
Next Topic: after delete, how we can "release space" like truncate
Goto Forum:
  


Current Time: Mon Dec 05 11:14:55 CST 2016

Total time taken to generate the page: 0.12745 seconds