can't understand why Range Scan used instead of Fast Full scan
Hello,
my version is 12.1.0.2 (but also tested on an older server with 11.2.0.3)
been trying to understand why the preferred Optimizer path is to use Range Scan instead of Fast Full Scan in my table.
here is the demonstration of my table:
create table providers (
id number,
name varchar2(60),
region varchar2(60),
state varchar2(10)
);
create index providers_id_name on providers
(id, name);
then I insert about 1 Million rows. and gather statistics.
afterwards when I query :
select name from providers where id=30
I get this:
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 4 |
|* 1 | INDEX RANGE SCAN