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