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_at_proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Seiwert Sent: Thursday, March 27, 2008 10:49 AM
To: oracle-l_at_freelists.org
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)))

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)))

Thank you in advance for helping.

Very best regards,

Michael

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 27 2008 - 10:03:25 CDT