Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Explain plan help
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.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st "Chuck" <chuckh_nospam_at_softhome.net> wrote in message news:Xns94E173940F91chuckhsofthomenet_at_130.133.1.4...Received on Thu May 06 2004 - 11:14:38 CDT
> I have the following showing up in an explain plan. There's not much
> good documentation on the FILTER operation. Can someone tell me exactly
> how this filter is getting it's results? Is it joining the two tables?
> If so is it a hash join, sort/merge, or is it as I suspect doing a
> nested loops, scanning table B for every row in table A?
>
> FILTER
> TABLE ACCESS FULL B rows=300m
> TABLE ACCESS FULL A rows=13k
>
> The actual DML looks like this. I am trying to force a hash join with FTS
> on both tables but I don't think it's working.
>
> UPDATE /*+ use_hash(olm_report_perform_to_category) full
> (OLM_REPORT_PERFORM_TO_CATEGORY) */
> olm_report_perform_to_category
> SET call_type_ind = 'Y'
> WHERE EXISTS
> (SELECT * FROM olm_report_scorecard score
> WHERE
> olm_report_perform_to_category.scorecard_id =
> score.scorecard_id
> AND olm_report_perform_to_category.category_id =
> score.category_id )
> AND record_complete_ind = 'N';
>
> --
> Chuck
> Remove "_nospam" to reply by email