RE: Completly different execution plans select vs. update with same where clause
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "mseiwert@xxxxxx" <mseiwert@xxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 Mar 2008 11:03:25 -0400
Michael,
The difference may be ascribed to the fact that the select is being optimized
w/ a first rows strategy, while the optimizer knows that the update MUST use an
all rows strategy. (In the case of the update, there's no concept of first
rows, as all rows must be updated before the statement completes.)
What version of Oracle?
Also, I see that CPU costing is disabled. You may want to enable CPU costing
by collecting system statistics.
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Michael Seiwert
Sent: Thursday, March 27, 2008 10:49 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Completly different execution plans select vs. update with same where
clause
Hi,
could anybody explain to me why the execution plan of the select statement
differs completly from the execution plan of the update statement? While the
select statement uses a very good plan using nested loops and highly indexed
access the update statement full scans the tables accessed through the view
v_titel2 ? Please see attached textfiles for execution plans. If more
information is needed I could provide 10046 and 10053 traces.
SELECT titel
FROM v_titel2
WHERE tiar_id IN (2, 6)
AND send_id IN (
(SELECT send_id
FROM termine t1
WHERE NVL (format_id, 0) = 6
AND EXISTS (SELECT 'X'
FROM imp_termine
WHERE ID = t1.ID AND job = 2666)))
AND titel LIKE '%/%';
UPDATE v_titel2
SET titel = 'XXX'
WHERE tiar_id IN (2, 6)
AND send_id IN (
(SELECT send_id
FROM termine t1
WHERE NVL (format_id, 0) = 6
AND EXISTS (SELECT 'X'
FROM imp_termine
WHERE ID = t1.ID AND job = 2666)))
AND titel LIKE '%/%';
Thank you in advance for helping.
Very best regards,
Michael
--
//www.freelists.org/webpage/oracle-l
Other related posts:
