Image Image Image Image Image Image Image Image Image
johan

By

October 4, 2011

Oracle Index Tree Dump Visualisation

October 4, 2011 | By | No Comments

 Update: JiTree is an online tool for visualising your indexes.

I’ve been working on a Processing project to visualise Oracle Index Tree Dumps. This is a quick example of the progress. It’s still very much a work in progress, so there are problems to address.

For the test table I’m just copying the dba_objects view into a table.

SQL> select count(*) from dba_objects;

COUNT(*)
----------
68913

SQL> create table test_o as select * from dba_objects;

Table created.

SQL> create index test_o_idx on test_o(object_id);

Index created.

Next I get the treedump as follows:


SQL> select object_id
2 from dba_objects
3 where object_name = 'TEST_O_IDX';

OBJECT_ID
----------
71227

SQL> alter session set events 'immediate trace name treedump level 71227';

Session altered.

Plugging the treedump into my visualisation tool (as I said, it’s a work in progress) gives this view. A very simple tree consisting of only a root node (red) and leaf nodes (clustered around the yellow core).



Now I delete 20000 rows from the test_o table:

SQL> delete from test_o where object_id between 40000 and 60000;

19565 rows deleted.

SQL> alter session set events 'immediate trace name treedump level 71227';

Loading the new treedump shows us where leaf nodes have been deleted.


Submit a Comment


× four = 16