Re: sql query not use index

You're question was, as I understand it is what sort of query WILL not use an index
1. A query against a table where the index values do not match a join or predicate condition (the index fields are not used or the fields are used within functions such as Upper(columnA) and the CBO does not recognize how to use them or does but decides it is too costly to apply the function against the index values to match) 2. A query that does match some or all of the columns in an index, but the CBO(cost based optimizer) decides that it would be more efficient to go directly to the table, rather than access the index first.

 This is typical where:

• the CBO believes you're going to retrieve a large part of the data in the table.
• the CBO believes the cost of accessing the index + the cost of accessing the table is going to be greater than just going straight to the table.

Indexes can be very useful and help performance but that is not always true.
The CBO decides to use, or not use the index on what it BELIEVES to be true about the data, not always exactly true. There are a lot of variables that influence the CBO's decision that are not always clear.

Hope this Helps, Received on Fri Mar 13 2009 - 18:00:14 CDT