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;