DELETE takes too long

[gaegoori]

I'm trying to DELETE FROM owner.table_name; about 60000 records from one table and it's taking forever. Is there any way that I can speed this up?
What can I check to see if there are anything wrong with this performance? This is on Oracle 817 and Solaris 8.

[pipo]

look at the explain plan of your delete query...
some questions : how many lines in your table ? are your stats up-to-date if you're using cbo ? are you deleting with a where clause involving an indexed column ?

[gaegoori]

how do I check the explain plan?
what do you mean by how many lines in table?
table is anaylized this morning
I think I'm using CBO, not sure.
I'm running this command:
SQL> delete from owner.table_name;

no where clause or indexes used for this delete statement.

sorry if I sound like stupid..but....it's better to learn and ask if I don't know rather than just being quiet.

[pipo]

if you just wish to delete the whole content of your table, you can truncate your table (truncate table ...), it will be much faster, but be cautious since you won't be able to rollback the truncate.

PS : to see the optimizer mode :

select value
from v$parameter
where name = 'optimizer_mode';

[ales]

Also check if an unindexed foreign key constraint is referencing the table. This might lower the speed significantly.

[prakashs43]

try setting nologging for a table and indexes which u are deleteing and try deleting records from the table . this will stop data entering into redo logs .

this may improve your operation .

[ales]

Originally posted by prakashs43
try setting nologging for a table and indexes which u are deleteing and try deleting records from the table . this will stop data entering into redo logs .

this may improve your operation .

No. NOLOGGING works only for some special statements. Never for DELETE.

[gaegoori]

TRUNCATE cannot be an option here. I have 6 FK and non of them are indexed.....I guess this is the big performance degradation?!? How do I index on my FK?

[ales]

I think (I guess) you could disable these FKs and try TRUNCATE.

[chrisrlong]

ales is correct.

Look here for one of jmodic's rants about NOLOGGING

http://www.dbasupport.com/forums/sho...9&pagenumber=2

As others have noted...

- Check for un-indexed FKs to this table or ones with ON DELETE clauses

- Check for DELETE triggers on this table

- Check how this table is stored. Is it all on one physical disk? Is the PCTFREE set way to high, so that it takes up far more room than necessary... those types of things

- If you don't mind the inability to rollback the DELETE, use TRUNCATE instead.

- Chris