Index Spawning Vs Index Splitting In B-Tree Index

SQL> CREATE TABLE idx_split
  2  (
  3   id INT,
  4   name VARCHAR2(90)
  5  );

Table created.

SQL> INSERT INTO idx_split
  2  SELECT rownum,'DAS' AS name
  3  FROM dual
  4  CONNECT BY LEVEL <= 100;

100 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE UNIQUE INDEX idx_idx_split ON idx_split(id);

Index created.

SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;

    HEIGHT NAME                              LF_BLKS    BR_BLKS
---------- ------------------------------ ---------- ----------
         1 IDX_IDX_SPLIT                           1          0

SQL> INSERT INTO idx_split
  2  SELECT 100 + rownum,'DAS' AS name
  3  FROM dual
  4  CONNECT BY LEVEL <= 1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;

    HEIGHT NAME                              LF_BLKS    BR_BLKS
---------- ------------------------------ ---------- ----------
         2 IDX_IDX_SPLIT                           2          1

SQL> CREATE TABLE idx_split
  2  (
  3   id INT,
  4   name VARCHAR2(90)
  5  );

Table created.

SQL> INSERT INTO idx_split
  2  SELECT 1100 + rownum,'DAS' AS name
  3  FROM dual
  4  CONNECT BY LEVEL <= 2000;

2000 rows created.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;

    HEIGHT NAME                              LF_BLKS    BR_BLKS
---------- ------------------------------ ---------- ----------
         2 IDX_IDX_SPLIT                           6          1
		 
SQL> INSERT INTO idx_split
  2  SELECT 3100 + rownum,'DAS' AS name
  3  FROM dual
  4  CONNECT BY LEVEL <= 200000;

200000 rows created.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE INDEX idx_idx_split VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height,name,lf_blks,br_blks FROM index_stats;

    HEIGHT NAME                              LF_BLKS    BR_BLKS
---------- ------------------------------ ---------- ----------
         2 IDX_IDX_SPLIT                         380          1