Finding the height of the B-Tree of a table in SQL-Developer

Question

Let's suppose I need to estimate te costs of the entire explain plan of an sql query, the one SQL-Developer shows. Is there somewhere this information, the height I mean, of the b-trees builted on indexes of a table? Or how can I estimate it?

Answer 1

In ALL_INDEXES view I found what I was looking for. Here the Oracle Documentation with all attributes you can select for an index. In my case:

SELECT INDEX_NAME, BLEVEL FROM ALL_INDEXES WHERE TABLE_NAME = 'table_name';

From Oracle doc on blevel attribute:

B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.

Answer 2

Might be wee late to the party but here goes.

ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;

SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

this will yield a table with a column "height".