Database Tuning (MOSC)

MOSC Banner

can't understand why Range Scan used instead of Fast Full scan

edited Dec 5, 2018 12:51PM in Database Tuning (MOSC) 6 commentsAnswered ✓

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    

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center