Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Explain plan help

Re: Explain plan help

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 May 2004 16:14:38 +0000 (UTC)
Message-ID: <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.

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

> 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
Received on Thu May 06 2004 - 11:14:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US