Re: Why is using Select in IN() clause much slower than using values?

cliffliang_at_gmail.com wrote:
> The version is 9.2.0.4.0.
>
> The "distinct" in the IN() select clause was a part of my testing and I
> have run it with and without. Whoever put together SF_QUAL_PAYCODES had
> put in duplicate values.

IN() automatically filters out duplicates, so DISTINCT is unnecessary.

> <plan skipped...>
> Anything else I should try? I don't have experience with manipulating
> the CBO and analyzing plans. Any Oracle Education classes or online
> resources you recommend?

Well, that seems to be quite a view. And quite a few hash joins of pretty large sets if plan cardinalities are correct (if stats are current and the CBO doesn't miscalculate them, then probably they are close to reality...) You should get the plan for the query with literal list of values in IN() and compare it to the one you get from the other query, maybe you will see the difference and will be able to hint the other query to use the same access paths. However, it could well be that the list of literals was merged into the view (that is, the predicate was propagated into the view to filter things earlier so that less data is processed on each step of the execution plan.) This can't be done with dynamic list selected from a table, because it's, well, dynamic - data in the table may change between executions, which may affect the plan adversely. If the list is going to be static, you may be better off with literals. However, if it's going to change often, then you should join the table and be ready to see worse performance because filtering will be applied at later stage and more data will need to be processed (unnecessarily, but unavoidably...)

As of education - you probably should start with Oracle documentation, in particular, Performance Tuning Guide for 9.2. Then there's an excellent book on the CBO by Jonathan Lewis - Cost-based Oracle Fundamentals (first of the series of 3 books,) which will give you excellent insight into CBO inner workings and why it does things the way it does them and how to help it do its job (though it's not for the beginner, I must admit.) Others may suggest other reading and maybe some courses. :)

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Aug 09 2006 - 13:17:25 CDT