使用并行创建主键约束的“奇葩”过程

落日映苍穹つ 2022-02-28 11:36 325阅读 0赞

这是杂货铺的第451篇文章

前两天和朋友一起讨论有关并行创建主键的问题，碰见的现象，以前确实没注意，没深入研究过。这个问题的背景是数据迁移过程中，要为一张表创建主键，为了提升速度，考虑增加并行，下面用实验，说明整个过程。

创建测试表，

> SQL> create table tbl\_pk(id number);  
> Table created.  
>

使用alter table增加约束的方式，同时指定索引表空间，以及并行度，来创建表的主键，但是执行后，提示ORA-03001，“未实现的特性”，这个解释很特别，难道未来会支持这种用法？

> SQL> alter table tbl\_pk add constraint pk\_tbl primary key(id) using index tablespace users parallel 12;
> 
> alter table tbl\_pk add constraint pk\_tbl primary key(id) using index tablespace users parallel 12  
>                                                                                       \*  
> ERROR at line 1:  
> ORA-03001: unimplemented feature

然而，经过测试，至少目前，从11g到19c，都是这个报错，

![640?wx\_fmt=png][640_wx_fmt_png]

一种说法是使用using index，表示如果主键字段上存在索引，那么创建主键的时候直接使用索引，不需要额外创建索引，既然索引都存在了，就是没必要开并行了，听着有些牵强。

但是如果使用alter table增加约束，只指定了并行度，不指定索引表空间，则可以创建主键，

> SQL> alter table tbl\_pk add constraint pk\_tbl primary key(id) parallel 12;  
> Table altered.

需要注意的是，虽然指定了并行度为12，可用这种方法，创建的主键索引degree=1，说明并行未生效，

> SQL> select index\_name, table\_name, degree from user\_indexes where table\_name='TBL\_PK';
> 
> INDEX\_NAME TABLE\_NAME DEGREE  
> \---------- ---------- ----------  
> PK\_TBL       TBL\_PK     1

如果在原始语句增加enable，就可以创建主键，但是同样并行度未生效，

> SQL> alter table tbl\_pk add constraint pk\_tbl primary key(id) using index tablespace users enable parallel 12;
> 
> Table altered.  
>   
> SQL> select index\_name, table\_name, degree from user\_indexes where table\_name='TBL\_PK';
> 
> INDEX\_NAME TABLE\_NAME DEGREE  
> \---------- ---------- ----------  
> PK\_TBL       TBL\_PK     1

开始怀疑并行的设置，是否有问题，但是对普通索引，指定并行度，能看出可以生效，

> SQL> create index idx\_tbl\_pk on tbl\_pk (a) parallel 12;  
> Index created.  
>   
> SQL> select index\_name, table\_name, degree  
> from user\_indexes where table\_name='TBL\_PK';  
> INDEX\_NAME   TABLE\_NAME   DEGREE  
> \----------------- ------------------ ------------  
> PK\_TBL             TBL\_PK             1  
> IDX\_TBL\_PK     TBL\_PK             12

不知道这是语法问题，即可以使用enable指定索引表空间和并行度，还是改的不彻底，并行度未生效。

那么问题来了，如何能用并行创建主键？另一种思路，主键是可以看作不可为空的唯一索引约束，那么拆为两步，第一步，指定索引表空间，以及并行度，创建唯一索引，上面测试过，可以让并行生效，第二步，指定使用创建的唯一索引增加表的主键约束，“曲线救国”，达到并行创建主键的目的，同时，为了提升速度，可以选择novalidate，不校验历史数据，

> SQL> create unique index pk\_tbl on tbl\_pk(id) tablespace users parallel 12;  
> Index created.
> 
>   
> 
> 
> SQL> alter table tbl\_pk add primary key(id) using index pk\_tbl;  
> Table altered.
> 
> 或者
> 
> SQL> alter table tbl\_pk add primary key(id) using index pk\_tbl novalidate;  
> Table altered.

主键索引的并行，已经生效，

> SQL> select index\_name, table\_name, degree from user\_indexes where table\_name='TBL\_PK';  
> INDEX\_NAME   TABLE\_NAME    DEGREE  
> \----------------- ------------------ ------------  
> PK\_TBL             TBL\_PK             12  
> IDX\_TBL\_PK      TBL\_PK             12

因为不是alter table ... add constraint指定约束名称的方式，所以主键约束的名称是系统默认的，SYS\_C开头，但是能满足并行创建主键索引，并且指定索引表空间的需求，

> SQL> select constraint\_name, constraint\_type, table\_name  
> 
> 
> from user\_constraints where table\_name='TBL\_PK';  
> CONSTRAINT\_NAME  CON   TABLE\_NAME  
> \------------------------- ------- ------------------  
> SYS\_C004044              P        TBL\_PK

主键和唯一索引很像，但两者是存在区别的，可以参考，

> 1. 主键为一种约束，唯一索引为一种索引，本质上就不同；
> 
> 2. 主键创建后一定包含唯一性索引，而唯一索引不一定就是主键；  
> 3. 主键不允许空值，唯一索引可以为空；  
> 4. 主键可以被其他表引用，而唯一索引不可以；  
> 5. 一个表最多只能创建一个主键，而可以创建多个唯一索引；  
> 6. 主键和索引都是键，主键是逻辑键，索引为物理键，即主键不实际存在。

P.S.

1. 关于novalidate选项，可以参考，

《[已存记录的非空约束限制][Link 1]》

《[Oracle违反约束数据的workaround][Oracle_workaround]》

《[表中已存重复数据的情况，如何增加唯一性约束？][Link 2]》

2. 感谢尚大师提供的案例素材。

[640_wx_fmt_png]: /images/20220228/07e2cd1176ab47cfb624f295f8c83a8a.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247487928&idx=1&sn=687267e84eea6d32a5add50da8316115&chksm=ea81544bddf6dd5da8b147c70a244f776a59438c80a231728b98b1876e0b809546726e0b7d22&scene=21#wechat_redirect
[Oracle_workaround]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247485325&idx=1&sn=4d1e2f805bf7b5bfd8a0cccbc1d38782&chksm=ea81427eddf6cb6825e0164464606aa0069bcf207ee99ef7e937984955fa617e31a3ff454855&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247484673&idx=1&sn=3d946049a0b343e1d6c2944ec6834bad&chksm=ea8140f2ddf6c9e46eb0c4b0db0404868ba3cdf0f6058fa6f2f42e8abc11b838677795e52515&scene=21#wechat_redirect