Conditions Based On Inequalities Can’t Use Indexes – How To Resolve?

Conditions based on inequalities (!=, <>) cannot make use of index(es). I will illustrate this limitation and show you how to optimize SQL statements hitting it.

For the demonstration, I have a table students table having a column named result that can contain the values – ‘Pass’, ‘Fail’, ‘To be evaluated’. The column is characterized by a very non-uniform distribution having most of the rows set to value Passed (P). Here’s the example:

Let’s execute the query to select all students who have not passed (result = ‘T’ or ‘F’). Even though the query has a very strong selectivity and the result column is indexed, the query optimizer chooses a full table scan for reading 7 rows as the predicate involves inequality.

In a case like this, where the inequality condition has a strong selectivity, we can advantage of an index using following four techniques :

First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited. For example, if the query is modified as shown, index range scan is employed.

Second, manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique can be applied if the values are unknown or the number of values to be specified is too high. Hence, if the query is rewritten as shown, it will be able to to take advantage of the or expansion query transformation:

– Third technique: We can create function based index using decode / case and restructure our query to use decode / case

In this technique, we will create a function based index using decode or case which will return two distinct values when the the condition is satisfied / not satisfied. Subsequently, we will restructure or query to use decode / case.

The fourth technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

In cases where the inequality condition having a strong selectivity is notable to make use of an index, we can advantage of an index using following three techniques :

First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited.

Second, manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique can be applied if the values are unknown or the number of values to be specified is too high.

Third , create function based index using decode / case and restructure the query to use decode / case

The fourth technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

hello ma’am i did the same exercise,queries are using indexes now by all solution queries but cpu cost increased ,physical read also increase and also bytes sent via sql*net to client also increase.By using inequlities operator although it was not using index but cpu cost,physical reads and bytes sent to client was lesser than when it was using indexes.? Any suggesitons

Since size of table is small, benefit of using indexes is not visible. When FTS is used all the blocks below HWM are visited. When is index is accessed, index blocks also need to be accessed in addition to the table block(s) having data. When index is accessed for the first, it has to be read from disk, hence physical reads. Subsequent accesses to index are from buffer cache which accounts increase in logical reads.

Try the same exercise with large table. You will definitely observer improvement in logical / physical I/O and CPU usage when index is used.