data redundancy

很多情况下我们都会需要复制源表数据以达到冗余数据的目的，那么到底是使用CREATE TABLE AS SELECT的CTAS方式，还是先建好表的结构然后再插入数据好呢？

我们来看看这2种方式的不同表现：

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

[Read more…]

CTAS VS create table and then insert