Oracle-为3亿条记录创建代理密钥

Question

有一个包含数百万条记录的表，其中也有重复的记录。创建一个新实体作为代理密钥(表示序列号(的过程是什么

E.g表格结构

col1 col2

101 A101 A101 B102 A102 B

我想创建一个新列(col3(，它表示序列号

---

第1列第2列第3列

101 A 1101 A 2101 B 3102 A 1102 B 2

请建议我为现有记录(3亿(创建代理密钥的步骤，甚至在加载新记录时(我认为插入时需要触发器(。

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