How effective is creating index on column having two types of values only

Question

I have created an index on a column, which has only two possible values (Y and N), data type is same for both values.

Suppose column name is indicator. I wrote a select statement like,

SELECT INDICATOR 
FROM TEMP_TABLE 
ORDER BY INDICATOR

When I used explain plan for this query in SQL developer, it is using full table scan not index scan.

Why it is not using index table scan.

Answer 1

In order to find a record in a table without an index, the DBMS needs to read all the records to find a match. With an index, the DBMS should only need to read a set of consecutive records to find the matches then go find the corresponding records in the table. So there's an extra step when the DBMS uses an index.

As a rough rule of thumb, if you are reading more than 5% of the records from a table, then a full table scan will be faster than an indexed lookup.

But the distribution of the data is also important. Consider the case where 99% of the data is 'Y'. Using the index to resolve records with 'Y' will be very innefficient (applying the inverse of my rule of thumb, it will take 20 times longer than a full table scan). OTOH using the index to find records with 'N' will be 5 times more efficient than a full table scan.

This is the reason that Oracle uses histograms for more complex index attributes.

Oracle also has a feature called bind peeking - which leverages the distribution issue to choose the most efficient query based on the predicates being searched for. Take some time to think about what happens when the pattern of queries matches the pattern of data - the cached plan will depend on which version of the query is first processed.

Short version: don't use indexes on (a set of) attributes with very low cardinality.