indexes and NOT, OR, AND operators

Question

I could not get a straight answer on this. The columns in my database have good selectivity on colA, colB, colC. They are in single table. These columns also contain NULL values. I tried building NORMAL indexes. But the query executions are not fast. I guess because of the operator NOT, indexes are not being used.

Question: Please confirm whether the index will be used in the following scenarios. I know that colB <> 'b1' will not use index. Also, I am NOT asking what would happen if I were to force index usage. I can't because my product builds the query in backend to which I don't have any control.

1. NOT (colA = 'a1')
2. NOT (colA = 'a1' OR colA = 'a2')
3. NOT (colA = 'a1' OR colA = 'a2' AND colC = 'c1')

4. NOT (colA = 'a1' OR colA = 'a2' OR colC = 'c1')

5. colB = 'b1' OR colB is not null

6. colB = 'b1' OR colB is null
7. colB <> 'b1' OR colB is not null
8. colB <> 'b1' OR colB is null

Answer 1

In these cases, a normal index would not normally be useful, but a Bitmap index might be used:

1. NOT (colA = 'a1')
2. NOT (colA = 'a1' OR colA = 'a2')
3. NOT (colA = 'a1' OR colA = 'a2' AND colC = 'c1')
4. NOT (colA = 'a1' OR colA = 'a2' OR colC = 'c1')

In this case, I doubt Oracle would choose to use either index:

1. colB = 'b1' OR colB is not null

In this case, the normal index on colB wouldn't be usable at all because of the NULL predicate:

1. colB = 'b1' OR colB is null

In these cases, I doubt Oracle would choose to use either index:

1. colB <> 'b1' OR colB is not null
2. colB <> 'b1' OR colB is null

In all cases it's uncertain because there are edge cases (e.g. with certain patterns of data) where the index may very well be chosen by the CBO - e.g. when the data is highly skewed and histograms have been taken, or if dynamic sampling is used.

Note also that the results will probably vary depending on whether you use literal values (as in your examples) or bind variables.

Answer 2

for NOT (A='a') or A<>'a' cases a TABLE FULL SCAN will be used

for IS NOT NULL cases FULL INDEX SCAN will be used where the column index is present

for IS NULL cases no column index will be used (NULL values are not indexed), in this case a function based index can be created as IS_NULL(column), where IS_NULL is a deterministic function returning e.g. 1 for NULL values and NULL for NOT NULL values. in the queries you have to use IS_NULL(column) IS NOT NULL instead of column IS NULL to use this function based index (FULL INDEX SCAN)