Home » RDBMS Server » Performance Tuning » Delete is taking time
Delete is taking time [message #64748] Wed, 21 January 2004 02:16 Go to next message
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 #64750 is a reply to message #64748] Wed, 21 January 2004 04:58 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
When you want to delete ALL the rows from the table,the fastest way to do that is

Truncate table NOTEBOOK_CHECKIN_INFO;


Pls have a look here Truncate vs Delete

Hope this helps
Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: Delete is taking time [message #64755 is a reply to message #64750] Wed, 21 January 2004 17:55 Go to previous messageGo to next message
Shesh
Messages: 16
Registered: July 2003
Junior Member
I tried truncate but it is giving ORA-02266 as it contains some references. I don't want to disable those constraints
Re: Delete is taking time [message #64758 is a reply to message #64755] Thu, 22 January 2004 02:24 Go to previous messageGo to next message
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
Re: Delete is taking time [message #65431 is a reply to message #64750] Mon, 20 September 2004 07:03 Go to previous message
amin
Messages: 7
Registered: April 2004
Junior Member
Una desventaja al ejecutar un truncate seria que no se puede hacer un rollback de las filas que se borran
Previous Topic: Regarding Procedure Performance
Next Topic: analying 10053 trace
Goto Forum:
  


Current Time: Thu Mar 28 18:05:16 CDT 2024