disable_pk_ok.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 125 行

--主键可以drop，主键add批量插入数据，性能比较评测
--结论：drop以后，插入数据节省大量时间

--使用的语句
create table person (id number(10) ,name varchar2(20),
constraint  con_pk primary key(id));

alter table person disable constraint con_pk;

alter table person enable constraint con_pk;

alter table person drop constraint con_pk;


--主键drop，主键add批量插入数据，性能比较评测---------------------------


SQL> 
create table person (id number(10) ,name varchar2(20),
constraint  con_pk primary key(id));

表已创建。

--使用drop constranit 去掉pk-----------------------
alter table person drop constraint con_pk;



  1* truncate table person
SQL> /

表已截掉。

SQL> declare
  2  n_loop number;
  3  s_time date;
  4  --s_time varchar2;
  5  e_time date;
  6  begin
  7    select sysdate into s_time from dual ;
  8    for n_loop in 1..100000 loop
  9   insert into person values(n_loop,'name'||n_loop);
 10   commit;
 11    end loop;
 12    select sysdate into e_time from dual ;
 13    dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
 14    dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
 15  end;
 16  /
12:11:59
12:08:02

PL/SQL 过程已成功完成。


--使用add constraint加pk--------------------------------------------

SQL> alter table person add constraint con_pk primary key(id); 

表已更改。

SQL> truncate table person;

表已截掉。

SQL> declare
  2  n_loop number;
  3  s_time date;
  4  --s_time varchar2;
  5  e_time date;
  6  begin
  7    select sysdate into s_time from dual ;
  8    for n_loop in 1..100000 loop
  9   insert into person values(n_loop,'name'||n_loop);
 10   commit;
 11    end loop;
 12    select sysdate into e_time from dual ;
 13    dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
 14    dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
 15  end;
 16  /
12:21:27
12:15:17

PL/SQL 过程已成功完成。





--disable constraint
---------------------------------------------------result---------------

Table truncated.
SQL> truncate table person;

Table truncated.

SQL> alter table person disable constraint con_pk;

Table altered.

SQL> declare
  2  n_loop number;
  3  s_time date;
  4  --s_time varchar2;
  5  e_time date;
  6  begin
  7    select sysdate into s_time from dual ;
  8    for n_loop in 1..100000 loop
  9   insert into person values(n_loop,'name'||n_loop);
 10   commit;
 11    end loop;
 12    select sysdate into e_time from dual ;
 13    dbms_output.put_line(to_char((e_time),'hh:mi:ss'));
 14    dbms_output.put_line(to_char((s_time),'hh:mi:ss'));
 15  end;
 16  /
05:25:17
05:22:55

PL/SQL procedure successfully completed.