RE: query and delete records really slow in a table
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <legedoos@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Oct 2007 09:02:53 -0400
Rob,
Do indexes exist on the child tables that will support the foreign keys?
Look at your explain plan. Try and eliminate the full table scans on
the OST_STUDENT_STUDIEVOORTGANG & OST_STUDENT_EXAMEN tables by adding
indexes.
Tom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LegeDoos
Sent: Friday, October 19, 2007 8:29 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: query and delete records really slow in a table
Hi there!
We have a couple (18) of tables "linked" with foreign keys and cascading
delete. The largest table contains < 1.000.000 records.
When deleting a record from the top level table, this is extremely slow
(like waiting a couple of minutes). On another database (on the same
server and on another server) deleting is fast (<1sec). Tables contain
defferent data.
-There are no blocking locks
-rebuilding the indexes didn't help
-the explain plan looks ok and is the same for the different databases
0 DELETE STATEMENT optimizer=ALL_ROWS (cost=460 card=1
bytes=73)
1 0 DELETE of 'OST_STUDENT_STUDIEVOORTGANG'
2 1 HASH JOIN (SEMI) (cost=460 card=1 bytes=73)
3 2 TABLE ACCESS (FULL) of
'OST_STUDENT_STUDIEVOORTGANG' TABLE
(cost=57 card=31859 bytes=1146924)
4 2 TABLE ACCESS (FULL) of 'OST_STUDENT_EXAMEN' TABLE
(cost=199
card=55431 bytes=2050947)
-I moved all tables en indexes to another tablespace, didn't help -There
are no errors in the alert.log.
Can anybody help?
Thanks!
Rob
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
