I am trying to tune a query in a 7.3.4 Rule-based environment. This rather
complex query does an index range scan and then reads the table (very large)
by rowid. This was taking forever so I created an index containing all the
columns to be read in the table (6 columns). This drastically improved the
query performance as the optimizer only retrieved the information from the
index, the query performance was then acceptable. Unfortunately after I
bounced the instance once, the optimizer didn't use this index anymore.
Any ideas as to how I can force the optimizer to use a specific index
without going cost-based or specify hints? How does the optimizer choose
the index, does it use the one with the smallest index records thinking it
would be more efficient? Or does it simply use the most recently used one?
Or does it simply use the first one it finds in its catalog that matches a
certain criteria?
Received on Tue Feb 27 2001 - 19:10:08 CST
