The index may have key columns (in the leading portion) that have monotonically increasing values (say like sequence.nextval or sysdate/systimestamp).

Since those monotonically increasing values will always keep on increasing and naturally will have very perfect clustering of data in the leaf blocks.

Have a table with one index on the ID column.

demo@ORA12C> create table t as
  2  select a.*, rownum as id
  3  from all_objects a
  4  where 1 = 0 ;

Table created.

demo@ORA12C> create index t_idx on t(id) ;

Index created.

demo@ORA12C> insert into t
  2  select a.*, rownum
  3  from all_objects a ,
  4      all_users b
  5  where rownum <=1e6;

1000000 rows created.


demo@ORA12C> analyze index t_idx validate structure;

Index analyzed.

demo@ORA12C> select blocks,lf_blks,pct_used from index_stats;

    BLOCKS    LF_BLKS   PCT_USED
---------- ---------- ----------
      2176       1999        100

The index has around 2000 leaf blocks and each leaf block is used to its maximum utilization.

Now let’s rebuild this index and see what happens.

demo@ORA12C> alter index t_idx rebuild nologging;

Index altered.

demo@ORA12C> analyze index t_idx validate structure;

Index analyzed.

demo@ORA12C> select blocks,lf_blks,pct_used from index_stats;

    BLOCKS    LF_BLKS   PCT_USED
---------- ---------- ----------
      2304       2226         90

The index got increased in size by adding 225 more leaf blocks to its structure.

However rebuilding those index with PCTFREE 0, we are back to square one.

demo@ORA12C> alter index t_idx rebuild nologging PCTFREE 0;

Index altered.

demo@ORA12C> analyze index t_idx validate structure;

Index analyzed.

demo@ORA12C> select blocks,lf_blks,pct_used from index_stats;

    BLOCKS    LF_BLKS   PCT_USED
---------- ---------- ----------
      2048       1999        100