| Delete command running taking long time [message #494052] |
Fri, 11 February 2011 03:13  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
I am trying to delete rows from a table and its is running from long time (more than 4 hours till the time of post). When I inserted these same number of rows from this table to another table it just taken 20-25 min.
After scratching the net I am checking the undo usage and its continuously increase the used undo blocks. can some one tell me why this query is taking so much time? or what I can do other than waiting to complete this query.
=============================================================
SQL> insert into backup.Tbl_chat_log select * from smschat.Tbl_chat_log where tr
unc(DATE_TIME) <= trunc(sysdate-7);
13008144 rows created.
SQL> commit;
Commit complete.
SQL> delete from smschat.Tbl_chat_log where trunc(DATE_TIME) <= trunc(sysdate-7);
=====================================================
There is only one index on this table.
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
IDXAPRT_DTIME APARTY
IDXAPRT_DTIME SYS_NC00007$
Regards
Pradeep
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Delete command running taking long time [message #494063 is a reply to message #494061] |
Fri, 11 February 2011 03:36  |
pradies
Messages: 250
Registered: May 2008
|
Senior Member |
|
|
Hi,
We know that this can be done but I can't do the same because there is transaction on the data after the date spacified must be going on and I can't truncate table table right now.
Is there any other solution or any other suggestation except this.
Thanx & Regards
pradeep
|
|
|
|
|
|
|
|
|
|
| Re: Delete command running taking long time [message #494088 is a reply to message #494052] |
Fri, 11 February 2011 05:33 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello pradies
By any chance do you have constraints on this table with 'delete cascade' effect?
In 9i I observed such scenario where child table did not had index on the column was was being fully scanned for each row deleted from parent table.
Regards,
OraKaran
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
