Re: Optimization question: Unrolling subquery into IN clause
Date: Fri, 23 Oct 2009 05:59:23 +0000 (UTC)
Message-ID: <Xns9CAD5145FF6F5wrgrpde_at_news.albasani.net>
Randolf Geist <mahrah_at_web.de> wrote in news:badd2686-0166-47fa-a260-bfd7c7c43d01_at_e34g2000vbm.googlegroups.com:
> As already mentioned by other contributors, the cardinality estimates
> are way off. This is very likely the reason for the plan chosen. If
> they were correct, that execution plan probably would be the most
> efficient one.
...
Thanks very much for your explanation, this looks very useful. I'll have a closer look at the table histograms which seem to be the key to this problem.
> The TKPROF output (application bind variable usage) you've shown joins
> the TAB2 table twice, so I wonder where that second join comes from.
> Very likely this is not the same query, as you seem to say with
> "slightly more complex version of the query".
Yes, the tkprof'ed query (which is from an application and thus cannot be easily changed) had another outer join which I edited out of the examples and out of my test query (run in sqlplus) because it had no influence on performance. I also changed table and column names to make the query more easily readable (and in order not to disclose details about the application in question).
Thanks for your help
W. Rösler
Received on Fri Oct 23 2009 - 00:59:23 CDT