| Delete is taking time [message #64748] |
Wed, 21 January 2004 02:16  |
Shesh
Messages: 16
Registered: July 2003
|
Junior Member |
|
|
Hi,
I am trying to delete all rows from a table which contains 65000 rows.
When I try to delete other tables having 65000 records it is deleting within 2 min, but If I try to delete this particular table the system is taking time.
I tried to delete 100 rows at a time by giving
'Delete from table A where rownum <100'
this statement takes 1 Min to delete 100 records.
Here is the execution plan for the above query.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=8 Card=99 Bytes=2942
40)
1 0 DELETE OF 'NOTEBOOK_CHECKIN_INFO'
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS* (FULL) OF 'NOTEBOOK_CHECKIN_INFO' (Cost= :Q68000
8 Card=58848 Bytes=294240)
3 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1.ROWID,A
1."CHECKIN_RECEIPT_NUM" FROM "NOTEBO
Can you please suggest what might be the problem?
I tried to rebuild the index, analyzed table and Indexes.
Thanks in advance
sheshadri
|
|
|
|
|
|
|
|
| Re: Delete is taking time [message #64758 is a reply to message #64755] |
Thu, 22 January 2004 02:24  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Sure, truncate wont work if the concerned table is referenced by enabled foreign keys. You'll have to disable them,truncate the parent and reenable the foreign keys.
If you absolutely cannot disable constraints or drop/recreate the table(with cascade constraints),then try to do the delete operation in a single sql by allocating a big rollback segment.
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
