RE: Completly different execution plans select vs. update with same where clause

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






--
http://www.freelists.org/webpage/oracle-l

Other related posts: