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.
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.
It is faster to go with "CREATE TABLE table_name AS SELECT * FROM ..."
This does not generate logging to the database, therefore it is
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.