sql - Oracle Partitioned Sequence

Question

I'm trying to see if exists something to create a sequence with partition logic. I need a sequence number that depend on other primary key ex:

id_person   sequence id
        1 | 1
        1 | 2
        2 | 1
        3 | 1
        1 | 3

so the sequence must depend on the id_person partition. Is there something like this on oracle or i must implement it by myself on the application level?

thank you.

---

Hi have create this PLSQL package one function and procedure:

PROCEDURE INIT_SEQUENCE(NAME varchar2, pkColumnNameList PARTITIONED_SEQUENCE_PK_COLUMN);
FUNCTION GET_NEXT_SEQUENCE_VALUE(NAME varchar2, pkPartitionColValue PARTITIONED_SEQUENCE_COL_VALUE) RETURN NUMBER;

INIT_SEQUENCE - get in input the name to associate at the sequence and a list of column name that are the fixed primary key part that vincolate the sequence Ex:'ID_PERSON'

the work of this procedure is to create the table that will manage the increment of sequence according to pkColumnNameList column.

GET_NEXT_SEQUENCE_VALUE- get the name of sequence to increment and the value of pkColumnNameList primary key and make the next step: 1) Create dynamically the sql to work 2) dbms_lock.allocate_unique(); to lock the table 3) check if is present a record in the table for pk value in input 4) if a record is present update the record with max + 1 in the sequence column 5) if a record is not present insert the new record with the 1 in the sequence column 6) return new id;

i would like to receive comment about this thanks in advance...

Answer 1

Is the actual requirement that the secondary sequence be gap free? If so, you've got a giant serialization/scalability issue.

If you need to present a gap-free sequence for human consumption, you could use an actual sequence (or a timestamp, for that matter) as Nick Pierpont suggests and preserve scalability, you could use analytic functions.

Dataset (t1):

 ID_PERSON SEQUENCE_ID
---------- -----------
         1           1
         2           2
         3           3
         1           4
         1           5
         1           6
         2           7
         3           8
         1           9

SQL:

select * 
  from 
  (select id_person, 
          sequence_id as orig_sequence_id,         
          rank () 
            over (partition by id_person 
                  order by sequence_id) 
            as new_sequence_id
     from t1
  )
 order by id_person, new_sequence_id;

Result:

ID_PERSON  ORIG_SEQUENCE_ID NEW_SEQUENCE_ID
---------- ---------------- ---------------
         1                1               1
         1                4               2
         1                5               3
         1                6               4
         1                9               5
         2                2               1
         2                7               2
         3                3               1
         3                8               2