oracle create table parallel,[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属性改回来

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-2126898/，如需转载，请注明出处，否则将追究法律责任。