对Oracle 并行的误解3--其实建主键没有用到并行

  如果为亿级别的表建主键会非常慢，原因是用建主键的语句是不能用到并行的，需要索引和约束分开建才能快。做一个实验：

drop table test purge;

create table test as select * from dba_objects where object_id is not null;

--按普通的方式建索引，可以看到并行度为1

alter table test add constraint pk_t_object_id primary key (object_id) Nologging parallel 16;
select degree from user_indexes s where s.index_name=upper('pk_t_object_id');

1

--按下列的方式两步走，先建唯一性索引，然后加约束

alter table TEST drop constraint PK_T_OBJECT_ID cascade;
create unique index ind_t_object_id on test(object_id)  Nologging parallel 16;
alter table test add constraint pk_t_object_id primary key (object_id);

select degree from user_indexes s where s.index_name=upper('ind_t_object_id');

16

--最后一定要记住，把索引并行度打回来，不然后果很严重

alter index ind_t_object_id noparallel;