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 1The 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
Great reading.
Thank you Sir.
Very good
nice one
Nice work!
Great to see another voice sharing experiences and wisdom about Oracle Database. Thanks for taking the time to do this, Shoumadip!
Access paths are ways in which data is retrieved from the database. In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table. Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths. Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions.
It’s really a nice and helpful piece of info. I’m glad that you simply shared this helpful info with us.
Please keep us informed like this. Thank you for sharing.