RE: Index height

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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: http://jonathanlewis.wordpress.com/2007/03/18/thinking-big/

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

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Orlando L [oralrnr_at_gmail.com] Sent: 26 April 2014 05:24
To: oracle-l_at_freelists.org
Subject: Index height

List

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
Orlando

--
http://www.freelists.org/webpage/oracle-l

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