RE: make create table run faster?
Date: Tue, 28 Apr 2009 13:05:15 -0400
A bit of sloppy language on my part, here. I should have said "CTAS is always nologging in noarchivelog mode." That is, the 'create table' statement will log redo on the data dictionary, as it must. CTAS, or 'create table as....' will do nologging (meaning only the minimal redo required to protect data dictionary and to enter invalidation records into redo for allocated blocks.)
Thanks to Jared for catching it and correcting me!
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Tuesday, April 28, 2009 12:05 PM
To: gmei_at_prospectiv.com; oracle-l_at_freelists.org Subject: RE: make create table run faster?
Nologging is redundant on a noarchivelog mode database. 'create table' is always nologging on noarchivelog mode.
Are you sure the create table is what's slowing you down? What about tuning the select? Also, don't forget, execution plan of select could change significantly if you test select by itself, and then add the create table later. This is because the CTAS will optimize as ALL_ROWS, whereas the standalone select may be optimized w/ FIRST_ROWS.
Hope that helps,
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei
Sent: Tuesday, April 28, 2009 11:56 AM
Subject: make create table run faster?
Oracle 10g in a NONARCHIVE mode.
I need to create some temp tables during some process. These temp tables will not be updated. I am doing something like this now:
CREATE TABLE TMP_TABLE123 PCTFREE 0 parallel 3 nologging as select ... from <some_base_tables>
Is there any other oracle parameters that I could use to make this step faster?
http://www.freelists.org/webpage/oracle-l Received on Tue Apr 28 2009 - 12:05:15 CDT