很多情况下我们都会需要复制源表数据以达到冗余数据的目的，那么到底是使用CREATE TABLE AS SELECT的CTAS方式，还是先建好表的结构然后再插入数据好呢？ 我们来看看这2种方式的不同表现：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
SQL>
select
*
from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> archive log list;
Database
log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log
sequence
1
Next
log
sequence
to
archive 2
Current
log
sequence
2
/* 数据库处于归档模式下 */
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
0
undo change vector
size
0
SQL>
create
table
YOUYUS
as
select
*
from
dba_objects;
Table
created.
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
5783384
undo change vector
size
15408
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
/* CTAS方式产生了少量的undo，可以猜想其使用直接路径方式插入，Oracle仅产生维护数据字典的undo */
SQL>
drop
table
YOUYUS;
Table
dropped.
SQL> conn /
as
sysdba
Connected.
/* 清理现场 */
SQL>
create
table
YOUYUS
as
select
*
from
dba_objects
where
0=1;
Table
created.
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
19492
undo change vector
size
5680
/* 建立空表YOUYUS，同样需要维护数据字典 */
SQL>
insert
into
YOUYUS
select
*
from
dba_objects;
50729
rows
created.
SQL>
commit
;
Commit
complete.
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
5743540
undo change vector
size
203904
/* 普通
insert
操作产生了远大于CTAS的undo */
SQL>
drop
table
YOUYUS;
Table
dropped.
SQL> conn /
as
sysdba
Connected.
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
0
undo change vector
size
0
SQL>
create
table
YOUYUS
as
select
*
from
dba_objects
where
0=1;
Table
created.
SQL>
insert
/*+ append */
into
YOUYUS
select
*
from
dba_objects;
50729
rows
created.
SQL>
commit
;
Commit
complete.
SQL>
select
ss.
name
,ms.value
from
v$mystat ms,v$sysstat ss
2
where
3 ms.statistic#=ss.statistic#
4
and
ss.
name
in
(
'undo change vector size'
,
'redo size'
);
NAME
VALUE
---------------------------------------------------------------- ----------
redo
size
5781712
undo change vector
size
14808
/* 建表后直接路径插入方式与CTAS产生的redo和undo数量大致相仿 */
|
从资源消耗的角度讲CTAS或直接路径插入方式有一定优势，如果启用nologging选项的话这种优势会更加显著。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277556
