Re: Explain plan help

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:c7do9e$frh$1 @hercules.btinternet.com:

>
> It would have been helpful to mention your
> version of Oracle.
>
> It is likely that the activity produced by that
> plan is effectively a nested loop full tablescan.
>
> There are some optimisation features on that
> particularly form of the FILTER which can
> restrict the number of inner table scans, but
> I don't think they apply in your example.
>
> The use_hash hint probably doesn't apply
> because the context is wrong - the query
> needs to be unnested before a hash join
> is relevant. This makes me think you are
> running 8.1 rather than 9.2.
>
> You could try the /*+ unnest */ hint in the
> subquery - this might be sufficient to make
> the query unnest and hash.
>
>

8.1.7.4

Thanks for the reply. I tried unnest but it didn't change the execution plan at all. I ended up rewriting the DML to do something like UPDATE.. WHERE ROWID IN (...) and used a subquery to return the ROWIDs I wanted. Cut the execution time down from > 1 hour to 90 seconds.

-- 
Chuck
Remove "_nospam" to reply by email