Optimizer question

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 12 Apr 2007 17:42:54 +0100
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663C165@ENYC11P32005.corpny.csfb.com>

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_at_credit-suisse.com
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

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

Received on Thu Apr 12 2007 - 11:42:54 CDT