In addition you may want to execute the statements, then use v$sql_plan or a 10046 trace to get the execution plan that was used to execute the statement. 'explain plan' does not always provide that actual plan used. On Thu, Mar 27, 2008 at 8:03 AM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx> wrote: > 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 > > > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist