Oracle分区序列 [英] Oracle Partitioned Sequence

问题描述

我试图看看是否存在一些东西创建一个带分区逻辑的序列。
我需要一个依赖于其他主键的序列号：

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

因此序列必须依赖于 id_person 分区。在oracle上有这样的东西，或者我必须在应用程序层面上自己实现它。

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?

谢谢。

您已创建此PLSQL包一个函数和过程：

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 - 输入要在序列处关联的名称和列名称列表固定的主键部分，用于输入序列Ex：'ID_PERSON'

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'

，此过程的工作是创建将根据pkColumnNameList列管理序列增量的表。

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-获取要递增的序列名称和pkColumnNameList主键的值，并进行下一步：
1）动态创建sql以工作
2）dbms_lock.allocate_unique（）;锁定表
3）检查表中是否存在一个记录，用于输入
中的pk值4）如果存在记录，则在序列列中用max + 1更新记录
5）如果不存在记录，则在序列栏中插入新的记录
6）return new id;

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...

推荐答案

实际要求二级序列是无缺口的？如果是，你有一个巨大的序列化/可扩展性问题。

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.

数据集（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;

结果：

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

这篇关于Oracle分区序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！