Home » SQL & PL/SQL » SQL & PL/SQL » Delete statement on Huge table is very slow (9i)
Delete statement on Huge table is very slow [message #332518] Tue, 08 July 2008 15:52 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
I have a table of 100,000 records and I am trying to delete few rows based on where condition. I have also created Index on columns used on where condition.

Delete statement which is very slow :

DELETE FROM dp_archived_object
WHERE nbinstancesondisk <= 0
AND nbinstancesontape <= 0;

Index:

CREATE INDEX DP_IDX_INS_DISK_TAPE ON DP_ARCHIVED_OBJECT(NBINSTANCESONDISK,NBINSTANCESONTAPE)

Thanks In advance
Re: Delete statement on Huge table is very slow [message #332520 is a reply to message #332518] Tue, 08 July 2008 16:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE INDEX DP_IDX_INS_DISK_TAPE ON DP_ARCHIVED_OBJECT(NBINSTANCESONDISK,NBINSTANCESONTAPE) COMPUTE STATISTICS

How many of the 100,000 rows should be DELETEd?
Re: Delete statement on Huge table is very slow [message #332521 is a reply to message #332520] Tue, 08 July 2008 16:04 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Some where around 60,000 record should be deleted.
Re: Delete statement on Huge table is very slow [message #332522 is a reply to message #332521] Tue, 08 July 2008 16:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
prax_14 wrote on Tue, 08 July 2008 14:04
Some where around 60,000 record should be deleted.


then index(es) should be ignored & use a Full Table Scan.
Re: Delete statement on Huge table is very slow [message #332523 is a reply to message #332522] Tue, 08 July 2008 16:11 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
What in the case if the table has 400,000 records and i need to delete 80,000 records
Re: Delete statement on Huge table is very slow [message #332524 is a reply to message #332518] Tue, 08 July 2008 16:14 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Sorry correction if the table has 4,000,000 record and i need to delete 80,000 records
Re: Delete statement on Huge table is very slow [message #332525 is a reply to message #332518] Tue, 08 July 2008 16:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS

[Updated on: Tue, 08 July 2008 16:16] by Moderator

Report message to a moderator

Re: Delete statement on Huge table is very slow [message #332540 is a reply to message #332518] Tue, 08 July 2008 22:10 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I Could not get the Execution plan and statistics as the query is not getting completed even after 5 Hours.
Re: Delete statement on Huge table is very slow [message #332541 is a reply to message #332518] Tue, 08 July 2008 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am not sure what may or may not be happening on your system;
but something is sorely amiss with your reported reality.

SQL> set term on echo on time on
20:27:00 SQL> select count(*) from TBLREPORTDELTA;

  COUNT(*)
----------
  38831902

20:29:15 SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
20:29:21 SQL> delete from TBLREPORTDELTA;
commit;

38831902 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3127137464

-----------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |                |     1 | 53871   (1)| 00:10:47 |
|   1 |  DELETE            | TBLREPORTDELTA |       |            |          |
|   2 |   TABLE ACCESS FULL| TBLREPORTDELTA |     1 | 53871   (1)| 00:10:47 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       2310  recursive calls
   41388971  db block gets
     245981  consistent gets
     244808  physical reads
        839  bytes sent via SQL*Net to client
        722  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
   38831902  rows processed

20:48:43 SQL> 
Commit complete.

20:48:43 SQL> 

[Updated on: Tue, 08 July 2008 22:52] by Moderator

Report message to a moderator

Re: Delete statement on Huge table is very slow [message #332623 is a reply to message #332541] Wed, 09 July 2008 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd guess that either
1) There is an on delete trigger on the table that takes a long time to run.
2) The table you are deleting from is the parent to a child table with an un-indexed foreign key
3) Another session has a lock on the table, and your session is waiting for the lock to be released.

What do you get from v$session_wait for the session running the delete?
Re: Delete statement on Huge table is very slow [message #332787 is a reply to message #332518] Wed, 09 July 2008 13:11 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I managed to get the Execution plan and statistics while deleting 113 rows from 4,000,000 records. It takes nearly 3 minutes. But when I try to delete 100,000 records from the same table the delete statements does not finish even after 5 hours.

The table from which records are deleted "dp_archived_object" is the parent table to 2 child tables (foreign key) having 4,000,000 records each. I have also indexed the foreign keys in child table.

SQL> DELETE FROM dp_archived_object
2 WHERE nbinstancesondisk = 0
3 AND nbinstancesontape = 0;

113 rows deleted.

Elapsed: 00:02:52.02

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'DP_ARCHIVED_OBJECT'
2 1 INDEX (RANGE SCAN) OF 'DP_IDX_INS_DISK_TAPE' (NON-UNIQUE
)

Statistics
----------------------------------------------------------
10803 recursive calls
5152 db block gets
545080 consistent gets
540404 physical reads
591780 redo size
794 bytes sent via SQL*Net to client
878 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
67 sorts (memory)
0 sorts (disk)
113 rows processed


Please suggest a way to make this delete statement work faster.

Thanks in advance.
Re: Delete statement on Huge table is very slow [message #332788 is a reply to message #332518] Wed, 09 July 2008 13:22 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Forgot to mention this. I have deleted all the corresponding records in the child table before deleting in the parent table.
Re: Delete statement on Huge table is very slow [message #332818 is a reply to message #332518] Wed, 09 July 2008 16:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
This is better info. It is good you have provided it.

However, you have been given the two most common reasons for slow deletes:

1) trigger on the table
2) unindexed foreign key from child table(s)

It does not matter if there are no rows in the child table that map to a key you are deleting. What matters is if there is an index to support the referential check needed to figure out there are no rows, when the delete occurs.

Did you check to see if there were triggers on the table?

Did you check to see what foreign key constraints point back to the parent table and if these were indexed appropriately?

Please do the following:

1) check these
2) show us how you checked these
3) tell us what the results of this check mean to you

Good luck, Kevin
Re: Delete statement on Huge table is very slow [message #332819 is a reply to message #332518] Wed, 09 July 2008 16:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> unindexed foreign key from child table(s)

I am fighting this very battle this afternoon.

I make no guarentee, but the following SQL may prove helpful.
select distinct table_name, constraint_name
from user_constraints
where constraint_name in (select constraint_name
                          from user_cons_columns
                          where exists (select column_name
                                        from user_cons_columns
                                        minus
                                        select column_name
                                        from user_ind_columns
                                      )
                        )
and constraint_type = 'R'

alternatively...

select table_name,  constraint_name, column_name
from user_cons_columns
where (table_name, column_name) not in (select table_name, column_name from user_ind_columns)
 and  constraint_name in (select constraint_name from user_constraints where constraint_type = 'R')
order by 1,2,3



Needless to say, you need to be logged into the schema which owns the application.

The query above returned "only" 79 rows for 1 simple DELETE that exposed the missing indexes.

[Updated on: Wed, 09 July 2008 18:55] by Moderator

Report message to a moderator

Re: Delete statement on Huge table is very slow [message #333160 is a reply to message #332818] Thu, 10 July 2008 11:42 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I found the following things

1)The table has a delete trigger which deletes from another table.
2) I have Index all the foreign key constraints point back to the parent table.

How did I check these :

1) I used toad to find out that the table has trigger.

2) I Used toad to find the foreign key constraints point back to the parent table and check if they are index properly
Re: Delete statement on Huge table is very slow [message #333161 is a reply to message #332518] Thu, 10 July 2008 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1)The table has a delete trigger which deletes from another table.
Does this "another table" have any ON DELETE CASCADE enabled?
Re: Delete statement on Huge table is very slow [message #333162 is a reply to message #332518] Thu, 10 July 2008 11:51 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
The Trigger deletes from a table which is a parent and on delete cascade is enabled on its child.
Re: Delete statement on Huge table is very slow [message #333165 is a reply to message #332518] Thu, 10 July 2008 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
while logged in as application schema owner, post results from
select ucc.table_name, ucc.constraint_name, ucc.column_name 
from user_cons_columns ucc,  user_constraints uc
where (ucc.table_name, ucc.column_name) not in (select table_name, column_name from user_ind_columns)
 and   ucc.constraint_name = uc.constraint_name
 and   uc.constraint_type = 'R'
 and   uc.delete_rule = 'CASCADE'
order by 1,2,3
Re: Delete statement on Huge table is very slow [message #333167 is a reply to message #332518] Thu, 10 July 2008 12:07 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

DN_ACTIONS DN_ACTIONS_SLOT_FK
DA_SLOT_ID

DN_ACTIONS DN_ACTIONS_TASK_FK
DA_TASK_ID

DN_COMMON_FILTER DN_COMMON_FILTER_PLAN_FK
PLAN


TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

DN_FILTER DN_FILTER_PLAN_FK
PLAN

DN_FILTER DN_FILTER_SLOT_FK
SLOT_ID

DN_SLOT DN_SLOT_STPLAN_FK
STORAGEPLAN_NAME


TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

DN_TOCREATE SYS_C00403173
PARENT_ID

DN_TRANSFORM_RULE DN_TRANSFORM_RULE_FK
FILTER_ID

DP_TRANSCODERS SYS_C00403166
TR_ACTOR


9 rows selected.


In the Nine rows seleted the table "DN_TOCREATE" is the child to the table on which the trigger deletes.

But this table "DN_TOCREATE" has zero records in it.

The remaing 8 rows selected are not used.
Re: Delete statement on Huge table is very slow [message #333169 is a reply to message #332518] Thu, 10 July 2008 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suggest that indexes be created on each of the columns returned by query above.

You should consider disabling the ON DELETE CASCADE while the DELETE is running; since it is not needed while the child table is empty.
Re: Delete statement on Huge table is very slow [message #333877 is a reply to message #332518] Mon, 14 July 2008 13:32 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
If the foreign key itself is primary key in the child table is it still needed to create a index on the foreign key.
Re: Delete statement on Huge table is very slow [message #333878 is a reply to message #332518] Mon, 14 July 2008 13:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
As far as I know, an index is automagically created for every Primary Key constraint.
Re: Delete statement on Huge table is very slow [message #333879 is a reply to message #332518] Mon, 14 July 2008 13:39 Go to previous message
prax_14
Messages: 64
Registered: July 2008
Member
I realized that but just wanted to confirm. Thanks
Previous Topic: Index Not getting used
Next Topic: How to select Top N th sal by using Join query ? (merged)
Goto Forum:
  


Current Time: Mon Jul 07 13:23:06 CDT 2025