Optimization in Oracle

Question

I am trying to optimize the below query and I have index for item_desc_secondary column.But explain plan gives full table access for item_main_BKP table.How can we avoid these full table access.

select i.item_desc_secondary,min(i.dept) dept
            from ITEM_MAIN_BKP i
            where i.i_level = i.t_level
            and i.item_desc_secondary is not null
           group by i.item_desc_secondary

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  6879 |   114K|   386   (1)| 00:00:01 |
|   1 |  HASH GROUP BY     |                 |  6879 |   114K|   386   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ITEM_MAIN_BKP   | 15039 |   249K|   385   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Answer 1

There could be many reasons why the optimizer has chosen a full table scan over an index scan, e.g.:

1. The ITEM_MAIN_BKP table is small enough that a full scan is faster than using the index.
2. The i_level and item_desc_secondary columns are not selective enough, meaning that the majority of rows in the table would need to be accessed regardless of whether an index is used or not.
3. Because the optimizer has determined that a full scan is more efficient based on the size and distribution of the data (& this might be due to stale statistics).

nb: a full table scan does not automatically mean that the query is poor. A full scan can be faster than using an index if the table is small or if the majority of the data needs to be accessed.