RE: query and delete records really slow in a table
- From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
- To: "T.Koppelaars@xxxxxxxxxxxxxxxxxxxx" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>, "legedoos@xxxxxxxxx" <legedoos@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Oct 2007 09:17:35 -0500
If that explain plan is for deleting one row, then it is not a good explain
plan. It is doing a full table scan in the child table for every row in the
parent. Did you analyze the tables with histograms? (i.e. for all indexed
columns)
Pat
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Koppelaars, Toon
Sent: Friday, October 19, 2007 8:06 AM
To: legedoos@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: query and delete records really slow in a table
Rob,
- Can you share the delete statement text?
- Is the index structure on all involved foreign keys the same (on this and the
other database)?
- Can perform a sql-trace of both cases (fast and slow)?
Toon
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LegeDoos
Sent: vrijdag 19 oktober 2007 14:29
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
___________________________________________________________________________________________________
CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is private, privileged, confidential or exempt
from disclosure under applicable law. If you are not the intended recipient or
it appears that this mail has been forwarded to you without proper authority,
you are notified that any use or dissemination of this information in any
manner is strictly prohibited. In such cases, please delete this mail from your
records.
To view this notice in other languages you can either select the following link
or manually copy and paste the link into the address bar of a web browser:
http://emaildisclaimer.medtronic.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
