Custom Treedump

Today I developed a utility, which I find basic and very simple, but useful. It dumps Oracle index tree in a nice way. It's similar to TREEDUMP event, but it's a mix of SQL and PL/SQL. The utility would have to be extended to support various types of indexes (IOTs, compressed etc.), but even now it allows to see what's inside the index block and how the index keys and rowids are placed in it. Below is an example of its output:

SQL> COLUMN empno FORMAT 9999
SQL> COLUMN pad FORMAT A5 TRUNCATED
SQL> COLUMN ename FORMAT A7
SQL>
SQL> CREATE TABLE emp_copy
2  AS
3    SELECT empno, ename, RPAD('X', 1000, 'X') pad FROM scott.emp;

Table created.

SQL> CREATE INDEX i_emp_copy ON emp_copy(empno, ename);

Index created.

SQL> @treedump

Function created.

SQL> @tdump i_emp_copy

PL/SQL procedure successfully completed.

Treedump [index f.b (dba) -> table f.b.r (keys/block)]           EMPNO ENAME
---------------------------------------------------------------- ----- ------
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.0 (001/014)  7369 SMITH
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.1 (002/014)  7499 ALLEN
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.2 (003/014)  7521 WARD
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.3 (004/014)  7566 JONES
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.4 (005/014)  7654 MARTIN
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.5 (006/014)  7698 BLAKE
0004.00011196 (0x01002bbc/16788412) -> 0004.00011188.6 (007/014)  7782 CLARK
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.0 (008/014)  7788 SCOTT
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.1 (009/014)  7839 KING
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.2 (010/014)  7844 TURNER
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.3 (011/014)  7876 ADAMS
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.4 (012/014)  7900 JAMES
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.5 (013/014)  7902 FORD
0004.00011196 (0x01002bbc/16788412) -> 0004.00011189.6 (014/014)  7934 MILLER

14 rows selected.

SQL> DROP INDEX i_emp_copy;

Index dropped.

SQL> CREATE INDEX i_emp_copy ON emp_copy(pad, ename DESC);

Index created.

SQL> @tdump i_emp_copy

PL/SQL procedure successfully completed.

Treedump [index f.b (dba) -> table f.b.r (keys/block)]             PAD SYS_NC00004$
---------------------------------------------------------------- ----- --------------
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.3 (001/007) XXXXX BEBBBEB2ACFF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011188.1 (002/007) XXXXX BEB3B3BAB1FF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011188.5 (003/007) XXXXX BDB3BEB4BAFF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011188.6 (004/007) XXXXX BCB3BEADB4FF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.5 (005/007) XXXXX B9B0ADBBFF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.4 (006/007) XXXXX B5BEB2BAACFF
0004.00011198 (0x01002bbe/16788414) -> 0004.00011188.3 (007/007) XXXXX B5B0B1BAACFF

0004.00011197 (0x01002bbd/16788413) -> 0004.00011189.1 (001/007) XXXXX B4B6B1B8FF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.4 (002/007) XXXXX B2BEADABB6B1FF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011189.6 (003/007) XXXXX B2B6B3B3BAADFF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011189.0 (004/007) XXXXX ACBCB0ABABFF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.0 (005/007) XXXXX ACB2B6ABB7FF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011189.2 (006/007) XXXXX ABAAADB1BAADFF
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.2 (007/007) XXXXX A8BEADBBFF

14 rows selected.

SQL> DROP INDEX i_emp_copy;

Index dropped.

SQL> CREATE INDEX i_emp_copy ON emp_copy(pad);

Index created.

SQL> @tdump i_emp_copy

PL/SQL procedure successfully completed.

Treedump [index f.b (dba) -> table f.b.r (keys/block)]             PAD
---------------------------------------------------------------- -----
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.0 (001/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.1 (002/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.2 (003/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.3 (004/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.4 (005/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.5 (006/007) XXXXX
0004.00011197 (0x01002bbd/16788413) -> 0004.00011188.6 (007/007) XXXXX

0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.0 (001/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.1 (002/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.2 (003/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.3 (004/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.4 (005/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.5 (006/007) XXXXX
0004.00011198 (0x01002bbe/16788414) -> 0004.00011189.6 (007/007) XXXXX

14 rows selected.

Thing dumps leaf blocks only, but orders them correctly, similary to TREEDUMP event output. I'm going to polish the code a bit and make the utility available.

Update: here a an updated SQL script.