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

Hi,

I forgot to mention:

- Databaseversion is 9.2.0.8 - 64 bit
- optimizer_index_caching  70
- optimizer_index_cost_adj 30
- optimizer_max_permutations 2000
- optimizer_mode  CHOOSE

Very best regards

Michael


Am Donnerstag, 27. März 2008 16:03:25 schrieb Bobak, Mark:
> 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: