Re: Optimizer question
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxxxxxxxxxx
- Date: Thu, 12 Apr 2007 13:33:29 -0600
At 10:42 AM 4/12/2007, Baumgartel, Paul wrote:
9.2.0.8 on Solaris 8.
Query is against a view that joins two tables--selects all columns
from table A, several columns from table B. The two tables have
indexes on the two join columns.
The first query is a select count(*) from view where tableAcolumn =
<date value>. Best single table access path for table A is
index-only based on the index on this date column. Single table access path
[snip]
Second query adds predicate clause tableBcolumn = <string
value>. This column is not indexed, as it contains only two
distinct values. In 10053 trace for this one, single table access
path for table B
[snip]
My question is why the optimizer does not consider join predicate
access to table B in second query. The desired plan, to my mind, is
It should, not in the "single table access path" part (because there
it considers only direct predicates on the single table), but further
"down" in the "GENERAL PLANS - Join order" part. There it should
consider both, among others, the NL A->B as well as the HA A->B (and
vice-versa). Check why the CBO calculates a lower cost for the HA
join than the NL join.
If you want - and can (confidentiality) - send me the trace and I have a look.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
Other related posts:
