| Dusmanta |
Posted: May 23, 2008 03:23:37 AM |
Total Post: 4
Joined: May, 2008
|
Hi,
I need to delete around 500000 of records. But i don't want rollback for the operation. Need to avoid witting into redo log.
i have tried with nologging. But I am not getting the proper result.
DELETE FROM CMF NOLOGGING WHERE ACCOUNT_NO IN (SELECT ACCOUNT_NUMBER FROM CUSTOMER_ACCOUNTS WHERE ACCOUNT_NUMBER = ACCOUNT_NO);
If i am using NOLOGGING then Redolog size is more than a simple DELETE statement.
SELECT NAME, VALUE FROM v$sysstat
WHERE NAME = 'redo size'
Please guide me if I am going in a wrong way.
Cheers:)
Dusmanta |
|
|
 Murtuja |
| Posted: May 23, 2008 04:34:33 AM |  |
|
Total Post: 857
Joined: Jan, 2006
|
We can't use NOLOGGING option with delete.
NOLOGGING option can be used with
create/alter table
create/alter index
direct load
In your situation CTAS method will be useful in terms of performance.
create table CMF_temp NOLOGGING PARALLEL 4
as select * from cmf WHERE ACCOUNT_NO NOT IN (SELECT ACCOUNT_NUMBER FROM CUSTOMER_ACCOUNTS WHERE ACCOUNT_NUMBER = ACCOUNT_NO);
Drop table cmf;
create indexes on cmf_temp
alter table cmf_temp rename to cmf;
|
|
|
|
|
| Dusmanta |
| Posted: May 23, 2008 06:54:41 AM | |
|
Total Post: 4
Joined: May, 2008
|
Is there any other way to avoid writting into redo log.
As in my table there is more than 20,00,000 of rec. Again recreating a temp table and dropping the table it is takeing more time.
and now it is creating more redolog for all three operations.
|
|
|
|
|
| Murtuja |
| Posted: May 23, 2008 07:09:15 AM | |
|
Total Post: 857
Joined: Jan, 2006
|
Hi,
Read this link. It will convince you to follow CTAS method
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
