CREATE TABLE vs INSERT

Hi everyone,

I have a question on the DROP TABLE -> CREATE TABLE AS SELECT NOLOGGING method comparing to the CREATE TABLE (Once) NOLOGGING and perform INSERT /*+ APPEND */ method.

Which is faster ? Provided the table is very huge.

Thanks

Code:

---

SQL> select count(*) from test;

  COUNT(*)
----------
      5783

Elapsed: 00:00:00.01
SQL> create table test_1 nologging as select * from test;

Table created.

Elapsed: 00:00:00.05
SQL>  insert /*+ APPEND */ into test_1 select * from test;

5783 rows created.

Elapsed: 00:00:00.01

---

Hi ckwan,

The best way to find out is to test and check which is the best solution using your environement and your data.

Hi Thomas,

I think that in your comaprison you forgot to check how long it takes to create the empty table. Also I dont think that a table with 5000 rows is something that could be called a huge table.

With a table having 17M rows a got a totaly different result:

Code:

---

SQL> create table test_cts parallel nologging as select * from source

Tabelle wurde angelegt.
Abgelaufen: 00:01:50.01


SQL> create table test_ins parallel nologging as select * from source where rownum < 1;

Tabelle wurde angelegt.
Abgelaufen: 00:00:02.03

SQL>  insert /*+ APPEND */ into test_ins select * from source;

16963112 Zeilen wurden erstellt.
Abgelaufen: 00:05:10.04

---

I'm not sure anymore but I think that an "create table as" doesnt generate any undo data so that could be the reason for the big difference.

Hope that helps
Mike