Custom Generated ID/Sequence in Oracle PL/SQL?

Question

I have a client requirement that requires day wise unique ID. I am working on Oracle 12c.

In that case the table (Client_Doc) structures as follows:

Doc_ID (PK), Date(PK), Doc_Description, Other_Columns

Sample Data Be like:

Value_Date   Doc_ID   Doc_Description
01-11-2020   1        Test Doc
01-11-2020   2        User Info
01-11-2020   3        Customer Doc
02-11-2020   1        Live
02-11-2020   2        Region
03-11-2020   1        Test

So if I want to generate ID I can do:

SELECT NVL(MAX(Doc_ID), 0) + 1 
INTO V_ID
FROM Client_Doc
WHERE Value_Date = :P_DATE;

But it is not a good practice due to dirty read and performance issue for a large quantity of table rows and also can't use the sequences because of the requirement. What will be the best approach to meet up the requirement?

If you are allowed gaps in `DOC_ID` for any given date, an almost identical question was asked and answered here: https://stackoverflow.com/q/38913246/5174436 — Matthew McPeak, Nov 11 '20 at 14:55

MT0 · Answer 1 · 2020-11-11T13:12:59.673

1

Generate a single primary key column (generated from a sequence or as identity) and then make a view which generates your composite key using the ROW_NUMBER analytic function:

CREATE TABLE client_doc (
  ID              INT
                  GENERATED ALWAYS AS IDENTITY
                  CONSTRAINT client_doc__id__pk PRIMARY KEY,
  Value_Date      DATE
                  NOT NULL,
  Doc_Description VARCHAR2(50)
);

CREATE VIEW client_doc_view ( id, value_date, doc_id, doc_description ) AS
SELECT id,
       value_date,
       ROW_NUMBER() OVER ( PARTITION BY value_date ORDER BY id ),
       doc_description
FROM   client_doc;

INSERT INTO client_doc ( value_date, doc_description )
SELECT DATE '2020-11-01', 'Test Doc'     FROM DUAL UNION ALL
SELECT DATE '2020-11-01', 'User Info'    FROM DUAL UNION ALL
SELECT DATE '2020-11-01', 'Customer Doc' FROM DUAL UNION ALL
SELECT DATE '2020-11-02', 'Live'         FROM DUAL UNION ALL
SELECT DATE '2020-11-02', 'Region'       FROM DUAL UNION ALL
SELECT DATE '2020-11-03', 'Test'         FROM DUAL;

Then, the view contains:

SELECT *
FROM   client_doc_view;

ID | VALUE_DATE | DOC_ID | DOC_DESCRIPTION
-: | :--------- | -----: | :--------------
 1 | 01-NOV-20  |      1 | Test Doc       
 2 | 01-NOV-20  |      2 | User Info      
 3 | 01-NOV-20  |      3 | Customer Doc   
 4 | 02-NOV-20  |      1 | Live           
 5 | 02-NOV-20  |      2 | Region         
 6 | 03-NOV-20  |      1 | Test

Then, when you want to display the data to the customer, you can show them the composite data of value_date and doc_id but in the background you have a single unique column that you can use for joins and foreign keys that is backed by a sequence.

db<>fiddle here

edited Nov 11 '20 at 13:12

answered Nov 11 '20 at 13:07

MT0

143,790
11
59
117

It seems there are a lot of assumptions that must be made for this to be safe. E.g., no deletes allowed on the table (or else they would change the identifiers on all later rows). – Matthew McPeak Nov 11 '20 at 14:54
1

@MatthewMcPeak See the last paragraph, this is for display only; all referential constraints should be done on the primary key. – MT0 Nov 11 '20 at 14:57
Thanks - that make sense. I'll leave my comment undeleted though, because I think it's an important point to stress. There are off-system usages of this "key" that could still cause errors. For example, a user looks at the records displayed and thinks "I need to investigate and edit row 01-NOV-20 / 2". Later, they come back to make the edit. If they query 01-NOV-20 / 2 to make the edit, they could be making a mistake. (That's part of the reason I always advocate for users' brains to be physically wired into the system, but I keep getting pushback). – Matthew McPeak Nov 11 '20 at 15:06
@MT0 mate unfortunately its not only for display purposes and also ROW_NUMBER() OVER ( PARTITION BY value_date ORDER BY id ) will take a performance issue on over of 10M records... – Nafi Pantha Nov 11 '20 at 16:52
@NafiPantha You are after something that doesn't really exist. You want a sequence partitioned over another column but such a structure doesn't exist and you don't want to use `MAX` as it doesn't handle parallel inserts and you want whatever solution you use to be performant .... you aren't going to find something as it doesn't exist. I tried to give an alternative and if that isn't good enough then you're probably going to be stuck and ought to discuss the requirement with the customer and ask them to change the requirement. Alternatively, you can use MatthewMcPeak's comment on you answer. – MT0 Nov 11 '20 at 18:38

score 0 · Answer 2 · answered Nov 11 '20 at 12:22

As you're on 12c, use identity column. Here's an example:

SQL> create table client_doc
  2    (doc_id  number generated always as identity,
  3     datum   date,
  4     doc_description varchar2(20),
  5     --
  6     constraint pk_clidoc primary key (doc_id, datum)
  7    );

Table created.

SQL> insert into client_doc (datum, doc_description) values (sysdate, 'Test');

1 row created.

SQL> select * From client_doc;

    DOC_ID DATUM    DOC_DESCRIPTION
---------- -------- --------------------
         1 11.11.20 Test

SQL>

Custom Generated ID/Sequence in Oracle PL/SQL?

2 Answers2