Search notes:

Oracle: Full Table Scan vs. Using Index

Create a demonstration table:

create table tq84_tab (
   flg char(1)  not null check (flg in ('Y', 'N')),
   val number(4,1),
   xyz varchar2(250)
);

Seed the random generator

begin
   dbms_random.seed(28);
end;
/

Fill the table. Note that the value of the column flg is highly skewed: it is Y only about every thousandth record:

insert into tq84_tab
select
   case when dbms_random.value < 0.001 then 'Y' else 'N' end,
   dbms_random.value(0, 1000),
   rpad('*', 250, '*')
from
   dual connect by level <= 10000;

Create a (bitmap) index on flg:

create bitmap index tq84_inner_ix_id on tq84_tab(flg);

Make sure statistics are up-to-date for the table:

begin
   dbms_stats.gather_table_stats(
      user,
     'tq84_tab',
      method_opt => 'for all columns size skewonly');
end;
/

The execution plan with the predicate flg = 'N' is using a full table scan:

explain plan for
   select
      val
   from
      tq84_tab
   where
      flg = 'N';

select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------
-- | Id  | Operation         | Name     |
-- --------------------------------------
-- |   0 | SELECT STATEMENT  |          |
-- |   1 |  TABLE ACCESS FULL| TQ84_TAB |
-- --------------------------------------

The optimizer switches to using an index when using the highly selective predicate flg = 'Y':

explain plan for
   select
      val
   from
      tq84_tab
   where
      flg = 'Y';

select * from table(dbms_xplan.display(format=>'basic'));
--
-- ----------------------------------------------------------------
-- | Id  | Operation                           | Name             |
-- ----------------------------------------------------------------
-- |   0 | SELECT STATEMENT                    |                  |
-- |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_TAB         |
-- |   2 |   BITMAP CONVERSION TO ROWIDS       |                  |
-- |   3 |    BITMAP INDEX SINGLE VALUE        | TQ84_INNER_IX_ID |
-- ----------------------------------------------------------------

Cleaning up:

drop table tq84_tab;

Index