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

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_at_proquest.com> 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_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)))
> 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
>
>
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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