INSERT /*+ APPEND*/ INTO <table1> select <col1> from <table2>

C:\>sqlplus oravn/oravn

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Sep 24 10:43:09 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oravn@ORAVN> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
CLUSTERED                      TABLE
NON_CLUSTERED                  TABLE
BIGTAB                         TABLE

oravn@ORAVN> drop table a purge;

Table dropped.

oravn@ORAVN>
oravn@ORAVN>
oravn@ORAVN> set timing on
oravn@ORAVN> set autotrace on
oravn@ORAVN> create table a as select * from bigtab;

Table created.

Elapsed: 00:00:39.46
oravn@ORAVN> select count(*) from a;

  COUNT(*)
----------
   1000000

Elapsed: 00:00:05.62

Execution Plan
----------------------------------------------------------
Plan hash value: 3918351354

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  3966   (1)| 00:00:48 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| A    |  1064K|  3966   (1)| 00:00:48 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
      14494  consistent gets
      14404  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

oravn@ORAVN> set autotrace off
oravn@ORAVN> drop table a purge;

Table dropped.

Elapsed: 00:00:01.15
oravn@ORAVN> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.43

oravn@ORAVN> create table a as select * from bigtab
  2  where 1=2;

Table created.

Elapsed: 00:00:00.26
oravn@ORAVN> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
oravn@ORAVN> set autotrace on
oravn@ORAVN> insert into a select * from bigtab;

1000000 rows created.

Elapsed: 00:00:21.28

Execution Plan
----------------------------------------------------------
Plan hash value: 441133017

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | INSERT STATEMENT  |        |  1002K|   134M|  3985   (1)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| BIGTAB |  1002K|   134M|  3985   (1)| 00:00:48 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       4693  recursive calls
     128912  db block gets
      36589  consistent gets
          1  physical reads
  117469048  redo size
       1778  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
         52  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

oravn@ORAVN> set autotrae off
SP2-0158: unknown SET option "autotrae"
oravn@ORAVN> set autotrace off
oravn@ORAVN> set timing off
oravn@ORAVN>