RE: make create table run faster?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, "gmei@xxxxxxxxxxxxxx" <gmei@xxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- 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!
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Bobak, Mark
Sent: Tuesday, April 28, 2009 12:05 PM
To: gmei@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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,
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Guang Mei
Sent: Tuesday, April 28, 2009 11:56 AM
To: oracle-l@xxxxxxxxxxxxx
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?
Guang
Other related posts:
