Re: Optimizer question

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 12 Apr 2007 13:33:29 -0600
Message-Id: <20070412193237.99C946B7AFF@turing.freelists.org>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 12 2007 - 14:33:29 CDT