Optimizer question
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Apr 2007 17:42:54 +0100
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 for table B in 10053 trace
shows cost based on use of join column index (seems to make sense since the
only criterion for table B is the join predicate). This cost is then
propagated into join computation and nested loops join is properly chosen.
Execution time is a few seconds.
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 considers only full table scan
to satisfy this additional predicate clause. This is, of course, much higher
than indexed access path in first query. This cost is propagated to join
computation, which choose hash join. Hash join is not the most efficient;
execution time is several minutes. A use_nl hint in the query results in
execution time (a few seconds) similar to query A.
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
Filter table A based on date-column index.
Join resulting row source to table B using nested loops on join columns.
Filter resulting row source based on table B predicate.
I hope I'm making sense here, and I realize that this is a lot of information,
but the only people that I know who could possibly discuss this with me are on
this list. Thanks in advance for any help.
Regards,
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com <www.credit-suisse.com>
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
