Index Spawning Vs Index Splitting In B-Tree Index

reference_partition

The phenomenon of vertical tree growth or height increase is known as “Index Spawning”. Happens in Auto-incremental scenario.

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

The phenomenon of horizontal tree growth is known as “Index Splitting”. Happens in Auto-incremental scenario.

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

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

8 thoughts on “Index Organized Tables In Oracle”

  1. Good day! Would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would really appreciate your content. Please let me know. Cheers

    1. Thank you for you response.

      Please share your twitter account so that i can start sharing my content, also requesting you to please ask your friend & colleagues to subscribe my blog site for all latest updates.

      Thank You…

  2. You could definitely see your enthusiasm in the work you write. The world hopes for more passionate writers like you who are not afraid to say how they believe. At all times follow your heart. brist på järn lleme.prizsewoman.com/map11.php

  3. I have read somewhere similar point of view and I totally agree with what you said. However, there are also some other things could be mentioned on this topic, but overall I like what you described.
    In this website there is also a lot of interesting and useful information:

Leave a Reply

Your email address will not be published. Required fields are marked *