Create Table ...as select Vs. Insert Statements

Would someone happen to know what is the difference between migrating tables from one database to another using the command "create table table_name as select * from schema.table_name@link" as opposed to creating the table and then running insert statement for all the data.

I choose to migrate the table with its data using the "create table.......as select statement because it accomplishes the same thing as doing creating a table and running an insert script with a bunch of insert statements(approx 1000 records). I believe it is also faster and generates less redo files, therefore less activity on the database.

The other dba in this office requested that I do insert statement every time I migrate tables with data from one database to another, I'm not sure if there is a benefit to this since both methods accomplish the same thing.

Please advise, thank you.

Well, there are actually 3 ways to do it, not 2.

1 - CREATE TABLE AS SELECT...

2 - CREATE TABLE
INSERT..SELECT

3 - CREATE TABLE
INSERT..VALUES
INSERT..VALUES
...

It sounds as if you are talking about comparing 1 to 3. Actually, let's compare 2 to 3. The only difference there is a single bulk statement (INSERT..SELECT) versus 1000 small statements (INSERT..VALUES). Now, which do you think is faster, a single statement on a set or 1000 statements on rows? Think about it...

Actually, if you have to think about it, you may need a new line of work. Databases are set-oriented beasts, plain and simple. It is always (read:99.9%) better to do a single large statement than lots of little statements. Time it. Check the resources. Total them up. I guarantee you that the single statement will win on all fronts.

So, since 2 is obviously way better than three, the question is actually whether 1 or 2 is the better choice. At the timing and resource level, I would venture to guess that thay are likely equivalent with choice 1 probably slightly ahead. (I'm sure someone will jump right in and give some exact numbers:)) I also like choice 1 because the datatypes are determined automatically, so there is less work up front and less maintenance headaches down the line.

There may be other implications of which I am unaware.

- Chris

It is faster to go with "CREATE TABLE table_name AS SELECT * FROM ..."
This does not generate logging to the database, therefore it is
faster.

The only problem you would have is if the tables contained LONG or
LONG RAW datatypes. If this case, I still do the "create table as
select" specifying the columns (except for the LONG column). I then
go thru a little pl/sql cursor to read the original data and update
the new data.

Gregg

Quote:

---

Originally posted by gbrabham
It is faster to go with "CREATE TABLE table_name AS SELECT * FROM ..."
This does not generate logging to the database, therefore it is
faster.

---

Um, no. If you use option 2 and add an APPEND hint, you get the same direct-load benefits

- Chris

Thank you Chris and Gregg for your replies.

Quote:

---

Originally posted by gbrabham
It is faster to go with "CREATE TABLE table_name AS SELECT * FROM ..."
This does not generate logging to the database,....

---

The way you suggested it, this CREATE_AS_SELECT would generate exactly the same amount of redo logs as would CREATE TABLE + INSERT_FROM_SELECT.

If you want to perform CTAS with minimal logging, then you have to specify it in your command:
"CREATE TABLE table_name NOLOGGING AS SELECT * FROM ..."

This same effect can be achived with CREATE, followed by INSERT_FROM_SELECT with the +APPEND hint, as Chris has already pointed ot.