sql - Oracle - 为 3 亿条记录创建代理键

Question

有一个包含数百万条记录的表也有重复记录。创建一个新实体作为代理键的过程是什么（表示序列号）

例如表结构

col1 col2

101 A 101 A 101 B 102 A 102 B

我想创建一个新列 (col3) - 它表示一个序列号。

---

col1 col2 col3

101 A 1 101 A 2 101 B 3 102 A 1 102 B 2

请建议我为现有记录（3亿）创建代理键的步骤，即使在加载新记录时（我假设插入时需要触发器）。

标签： sqloracleduplicatesprimary-keysurrogate-key

Answer 1

只需使用row_number函数来填充col3：

对于已经存在的记录适用：

SQL> create table tab(col1 int , col2 varchar2(1));

Table created
SQL> insert all
  2         into tab values(101,'A')
  3         into tab values(101,'A')
  4         into tab values(101,'B')
  5         into tab values(102,'A')
  6         into tab values(102,'B')
  7  select * from dual;

5 rows inserted
SQL> create table tab_ as
  2  select col1, col2,
  3         row_number() over (partition by col1 order by col2) as col3
  4    from tab;

Table created
SQL> drop table tab;

Table dropped
SQL> alter table tab_ rename to tab;

Table altered

或者（不重新创建表格）：

SQL> create table tab(col1 int , col2 varchar2(1));

Table created
SQL> insert all
  2            into tab values(101,'A')
  3            into tab values(101,'A')
  4            into tab values(101,'B')
  5            into tab values(102,'A')
  6            into tab values(102,'B')
  7     select * from dual;

5 rows inserted
SQL> alter table tab add col3 integer;

Table altered
SQL> declare
  2   i pls_integer := 0;    
  3 begin
  4   for c in
  5     (
  6      select rowid, col1, col2,
  7         row_number() over (partition by col1 order by col2) as col3
  8        from tab
  9     )
 10   loop
 11    update tab t
 12       set t.col3 = c.col3
 13     where t.rowid = c.rowid;
 14     i:= i+1;
 15     if ( ( i mod 10000 ) = 0 ) then commit; end if;
 16   end loop;
 17  end;     
 18  commit;
 19  /

PL/SQL procedure successfully completed

SQL> select * from tab;

COL1 COL2  COL3
---- ---- -----
  101 A       1
  101 A       2
  101 B       3
  102 A       1
  102 B       2

  5 rows selected

对于即将到来的（新插入的）记录，您可以使用您提到的触发器：

SQL> create or replace trigger trg_ins_tab
  2  before insert on tab
  3  referencing new as new old as old for each row
  4  declare
  5  begin
  6      select nvl(max(col3),0) + 1
  7        into :new.col3
  8        from tab
  9       where col1 = :new.col1;
 10  end;
 11  /

Trigger created
SQL> insert into tab(col1,col2) values(101,'C');

1 row inserted
SQL> select *
  2    from tab t
  3   order by t.col1, col3;

COL1 COL2  COL3
---- ---- -----
  101 A       1
  101 A       2
  101 B       3
  101 C       4
  102 A       1
  102 B       2

  6 rows selected