| Can I disable redo log for just one INSERT statement? [message #167725] |
Sun, 16 April 2006 00:21  |
moeen49
Messages: 12
Registered: September 2005
|
Junior Member |
|
|
Hi,
I need to warehouse 40,000,000 records into one table. When I run the following command:
INSERT INTO …… SELECT …
The command takes hours and the monitoring tools show significant increase of redo log.
Can I disable the redo log for just one INSERT statement?
Is it the best way to improve the performance of this ISERT statement?
Thank you,
Alan
|
|
|
|
|
|
| Re: Can I disable redo log for just one INSERT statement? [message #167902 is a reply to message #167730] |
Mon, 17 April 2006 21:34  |
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
|
Senior Member |
|
|
| aciolac wrote on Sun, 16 April 2006 02:51 |
If you want to disable logging, you can disable them for your table. Simply exec
SQL>ALTER TABLE your_table_name NOLOGGING;
this operatinon will stop logging of changes on this table. Also some log information will be generated, but not so much.
|
It is correct, but partly, not completely.
This modification itself will not stop logging.
You have to use a hint APPEND additionally.
Also it works only for INSERT ... SELECT ... statements.
example:
INSERT /*+ APPEND */ into Tgt SELECT * from Src;
it does not work with
INSERT /*+ APPEND */ into Tgt VALUES (...);
|
|
|
|
| Re: Can I disable redo log for just one INSERT statement? [message #168048 is a reply to message #167725] |
Tue, 18 April 2006 09:44 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just to clarify, if you only want to "disable" redo for a single statement, then don't alter the table to nologging. Leave the table alone, and use the insert append direct path insert method described. But you'll need a backup of that table now because you won't be able to recover your changes without it.
Also, you are not really "disabling" redo. Instead, you are just significantly, massively, reducing it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
