RE: Index height

From: Jonathan Lewis <>
Date: Sat, 26 Apr 2014 07:34:02 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DEB2F8_at_exmbx05.thus.corp>

Here's a note I wrote a few years ago about B-tree indexes and the arithmetic of work/height:

If your indexes are designed correctly, and ignoring a couple of pathological cases and bugs, a B-tree index (with NO regular maintenance) should probably be able to cover 10M to 30M rows at height = 3; and at height 4 that could increase to somewhere between 1 and 5 billion rows.

Bitmap indexes are harder to predict, but generally small - unless you're doing regular DML in small batches in which case you can be very unlucky

Jonathan Lewis

From: [] on behalf of Orlando L [] Sent: 26 April 2014 05:24
Subject: Index height


I decided to check the height of indexes used in our databases as part of my learning experience. In the biggest OLTP database (550 GB) that I maintain I saw about a 100 indexes with height of 4 (3 blvl+1), a 1000 with height 3.

I was expecting some big bad 'heights' for indexes. Do the listers have big indexes lurking in their neck of the woods?

Thank you all

Received on Sat Apr 26 2014 - 09:34:02 CEST

Original text of this message