Oracle – Why is BLEVEL Null for Some Indexes?

indexoracle

I was trying to see the b-tree levels for indexes and to my surprise I see some of them are set to null.

SELECT index_name, blevel FROM user_indexes ORDER BY 2 desc;

Their name starts with "SYS_"(picture attached below). SO I am assuming they are system generated indexes? Maybe of primary keys? Either ways I am curious as to why they are null. I can understand if the value is 0 which mean everything is in leaf nodes and it will just take 1 hop for unique scan to get the data.

enter image description here

Best Answer

Indexes whose the name start with SYS_IL.. are LOB indexes.

BLEVEL is a property of B-tree indexes, and does not make sense for LOB indexes.

Even if you collect statistics, BLEVEL for such an index will remain null.

Related Question