Thursday, August 11, 2011

CTAS "Create Table As Select" With PARALLEL NOLOGGING

Normally CTAS "Create Table As Select" with give a good performance but if the original table has a large number of rows PARALLEL NOLOGGING method should be consider.

The table below has 1,346,163 records.



The time it takes to create another table with normal CTAS is 41 seconds.



If we use CTAS with PARALLEL NOLOGGING the time it takes is reduce to 22 seconds.



Remember the keyword PARALLEL denotes the number of parallel threads that needs to run to do the job quickly. Ideally you should keep the parallel count between 3 to 5.

The keyword NOLOGGING tells Oracle not to enter anything in the redo log files. Ideally, when any SQL command is executed, Oracle keeps on writing the events in the redo log file so that if anything goes wrong, Oracle can rollback the changes.

Hence adding the NOLOGGING parameter will ensure that you save on the time that oracle spends to log events.

Try it out with this syntax and you will see a marked improvement.

3 comments:

  1. What's Happening i'm new to this, I stumbled upon this I have discovered It
    positively helpful and it has aided me out loads. I hope to contribute & help different
    users like its helped me. Good job.

    healthy ()

    ReplyDelete
  2. I think this is one of the most important information for me.

    And i'm glad reading your article. But want to remark on some general things,
    The site style is ideal, the articles is really great : D.

    Good job, cheers

    Check out my site ... Hypothyroidism Revolution
    Scam ()

    ReplyDelete
  3. Its like you read my mind! You seem to know a lot about this, like you wrote the book in it
    or something. I think that you can do with a few pics to drive the message home a little bit, but
    instead of that, this is great blog. An excellent read.
    I'll definitely be back.

    Learn more

    ReplyDelete