[Oracle] Create table as 速度測試nologging+parallel

SQL> show user
USER 為 "BRUCE"

SQL> select count(*) from bruce;

  COUNT(*)
----------
  83324928


SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875


SQL> create table bruce1 as select * from bruce;

表已建立。

已用時間:  00: 07: 46.42


SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875
BRUCE1                             8.9375


SQL> create table bruce2 nologging as select * from bruce;

表已建立。

已用時間:  00: 02: 31.71


SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875
BRUCE1                             8.9375
BRUCE2                         8.88769531


SQL> create table bruce3 tablespace bruce nologging parallel 2
  2  as
  3  select * from bruce;

表已建立。

已用時間:  00: 00: 59.93


SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875
BRUCE1                             8.9375
BRUCE2                         8.88769531
BRUCE3                         8.88317871


SQL> create table bruce4 tablespace bruce nologging parallel 2
  2  as
  3  select /*+parallel(a 2)*/ * from bruce a;

表已建立。

已用時間:  00: 00: 59.32
SQL> select segment_name,bytes/1024/1024/1024 from user_segments;

SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
BRUCE                               8.875
BRUCE1                             8.9375
BRUCE2                         8.88769531
BRUCE3                         8.88317871
BRUCE4                         8.88317871


--------------------------------
普通insert           |   07: 46.42
nologging            |   02: 31.71
nologging + parallel |   00: 59.93
nologging + parallel(包括select)  |   00: 59.32

(1)對於將近9G的table,create table使用 nologging + parallel速度會有很大的提升，並且沒有log產生(很少).
(2)在select中也加上parallel好像作用不大，可能是電腦的cpu核數不夠，有機會找臺server測試一下.
(3)執行完成之後，切記將 table的logging和degree屬性改回來