Create Table ...as select Vs. Insert Statements
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Create Table ...as select Vs. Insert Statements

  1. #1
    Join Date
    Jun 2003
    Location
    Illinois
    Posts
    2

    Question 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.
    Last edited by LoriWong; 06-19-2003 at 12:16 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Jun 2003
    Location
    Illinois
    Posts
    2
    Thank you Chris and Gregg for your replies.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width