I have a large query generated by a tool that joins tables
A-B-C-D-E-F-G-H-I
I modified the query to produce the same results by joining tables:
A-F-G-H-I
A composite index is used by Oracle on table G in both queries, but in
the first case, Oracle uses an index range scan on that index while in the
second case, Oracle uses a fast full scan on the same index. So, instead of
the second query running faster (joins fewer tables), it runs slower.
The query selects only a small subset of entries in the index and the index
has the 2 columns required to satisfy the query (no table access is necessary).
What can I do to avoid the fast full scan? I could not find a hint that
accomplished it. Will building a histogram on the index help?
Received on Fri Aug 08 2003 - 11:02:25 CDT
