CREATE TABLE vs INSERT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: CREATE TABLE vs INSERT

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    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

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    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

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