Oracle not using my bitmap index if I query for all columns

Question

I created a bitmap index for my table on column_x. Keep in mind that column x has low cardinality, only 20 unique values out of 100,000 rows of data.

Then I query

SELECT * FROM table_1
WHERE column_x = 'xyz'

But instead of doing an index scan, it used full table scan.

| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 53295 |  8483K|  3413   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEE     | 53295 |  8483K|  3413   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Now if I added an index for another column y, then I query with

SELECT column_x,column_y FROM table_1
WHERE column_x = 'xyz'

the result would be

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  | 53295 |   676K|     8  (75)| 00:00:01 |
|*  1 |  VIEW                         | index$_join$_001 | 53295 |   676K|     8  (75)| 00:00:01 |
|*  2 |   HASH JOIN                   |                  |       |       |            |          |
|   3 |    BITMAP CONVERSION TO ROWIDS|                  | 53295 |   676K|     1   (0)| 00:00:01 |
|*  4 |     BITMAP INDEX SINGLE VALUE | DIVISION_IDX     |       |       |            |          |
|   5 |    BITMAP CONVERSION TO ROWIDS|                  | 53295 |   676K|     1   (0)| 00:00:01 |
|   6 |     BITMAP INDEX FULL SCAN    | STATUS_IDX       |       |       |            |          |
--------------------------------------------------------------------------------------------------

Why can't I select all columns? There's no way I should create an index for every column right? Because some of the columns would have high cardinality or would be less put on a where condition. Also my query only return about 5% of the all data.

Did I do something wrong here?

Answer 1

You did nothing wrong. Oracle is doing the right thing. Indexes don't always make things better. Oracle is trying to be intelligent and use indexes only when it will help, and not when it won't.

When you select all the columns, the only way to get those columns is to get it from the table itself. If it were to use an index to access based on your predicate (WHERE clause), it would have do a single block read for every ROWID it found in the index, which would be about 5% of the rows (1/20), which is pretty significant. That many inefficient single block reads (without factoring in caching, this means an entire I/O operation for every row!) will really add up and make that an expensive operation at high volumes. A full table scan can read 5% of a table faster than index access can because it uses much more efficient I/O methods that are optimized for large amounts of data.

When you select only one column which also happens to be indexed, Oracle can satisfy your entire query without using the table at all. It uses one index for selection, the other to get the column you want, and intersects the two on their common key (ROWID). No table visit required at all, so it doesn't need to do all those single block reads. So it finds it preferable to use your bitmap indexes for this scenario.

You shouldn't be trying to force Oracle to use indexes for everything. Let it use a full table scan when it believes it's the better path. Very often a full table scan is the right choice. Sometimes it gets it wrong and needs nudging (often via hints), but most of the time you can let it do its thing. Only invest time into performance tuning when a SQL actually performs unacceptably when run.