Why is Oracle using the index here?

Name     Null     Type      
-------- -------- --------- 
ID       NOT NULL NUMBER(4) 
GROUP_ID          NUMBER(4) 
TEXT              CLOB

There is a btree index on group_id. Here's how many rows each group_id has and the corresponding percentage:

GROUP_ID               COUNT                  PCT                    
---------------------- ---------------------- ---------------------- 
1                      1                      1                      
2                      2                      1                      
3                      4                      3                      
4                      8                      6                      
5                      16                     12                     
6                      32                     24                     
7                      64                     47                     
8                      9                      7

I ran this

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST', cascade=>true);

Which, if I understand correctly, will gather stats for the optimizer.

Now, I remember seeing that Oracle will not use the index and perform a full table scan instead if it's retrieving more than 5% or so of all rows. However, when I ran this query, it only started performing a FTS when group_id was 7, which has 47% of all rows.

Is this the way it's supposed to be?

Oracle also tracks the number of blocks that will be retrieved using the index values. It may be your data is clustered by group_id. In that case, it may make sense to use the index when retrieving more than 5% of the rows. If the data is sufficiently clustered for values 5 and 6, then using the index may be roughly equivalent to doing full table scan of a few blocks.

The relevant calculation will involve the number of index blocks needing to be retrieved, the number of data blocks needing to be retrieved, and the CPU required to access the data. There are tuning parameters which adjust the relative costs of retrieving index versus data blocks. This can change the plan that is being used.

StackExchange Database Administrators Q#5632, answer score: 4