-
Questions
-
Create Table Nologging As Select * vs Insert /*+ append */ Into Table Select *
Breadcrumb
Thanks for the question, alan.
Asked: June 03, 2002 - 4:00 pm UTC
Last updated: June 03, 2002 - 4:00 pm UTC
Version: 9.0.1
Viewed 1000+ times
You Asked
Hi Tom:
One of our fellow developer told as that it is faster to do a CREATE TABLE NOLOGGING AS SELECT * FROM table_name than INSERT INTO table_name SELECT * FROM table. Is this true? If Yes could you explain further why?
Is DIRECT LOAD, INSERT /*+ APPEND */ INTO table_name SELECT * FROM table is equal/similar in terms of processing to a CREATE TABLE NOLOGGING AS SELECT * FROM table_name. Please explain.
Thanks, and more power.
and Tom said...
depends -- are you archivelog or noarchive log?
In noarchive log -- i would say *no*, no difference... Consider this script:
drop table t;
drop table t2;
alter session set sql_trace=true;
create table T nologging as select * from all_objects;
create table t2 as select * from all_objects where 1=0;
insert /*+ append */ into t2 select * from all_objects;
in noarchivelog mode:
create table T nologging as select * from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 5.90 6.46 493 140103 78 22433
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.94 6.50 493 140103 78 22433
insert /*+ append */ into t2 select * from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 5.69 5.91 397 140097 56 22434
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.72 5.94 397 140097 56 22434
for all intents and purposes -- the SAME.
Now, in archivelog mode -- there might be somethere BUT -- they would be comparing apples to toaster ovens. The CTAS is done without logging -- but the insert does logging. You would alter the table to NOLOGGING before the insert/append and then it would work just like the CTAS! But even so -- on a nicely tuned system - the insert append will be just as fast even though it logs generally (as lgwr trickles the redo out during the insert over time)
I would ask them to prove it in a repeatable test case.
We're not taking comments currently, so please try again later if you want to add a comment.
