Re: Explain plan help

Chuck wrote:
>
> 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?

Unfortunately, it's the latter. TABLE ACCESS FULL is generally a bad thing in an Explain Plan unless the table is really small. If I'm reading this right and you're joining a 300 Million row table with a 13 thousand row table, and the plan is for full table scans, you've got a performance problem.

I'm hardly an expert on tuning, but I've found that the cost-based optimizer with properly indexed and analyzed tables usually provides adequate performance. I only resort to hints if cost-based is not giving me what I want. Of course, I don't tend to deal with 300 million record tables, so your mileage may vary.

So, do you have indexes on the following fields? olm_report_perform_to_category.scorecard_id olm_report_perform_to_category.category_id score.scorecard_id
score.category_id
NOTE: a composite index on more than one field may not be enough.

Have you analyzed the tables? (necessary so that the optimizer has a chance to act inteligently)

Is your instance set to use cost-based optimization?

What Oracle version? What OS?

-- 
//-Walt
// 
//