Oracle Indexes

Having already covered general block header details relevant to several different types of Oracle blocks (Block Dumps Part I and Part II), the next part of the block dump is relevant only to index blocks. Below is a dump of the index only section of an index leaf block dump: Leaf block dump =============== header [...]

OK, let’s look at the next portion of the index block dump.   Following the hex dump of the block (as we ended Part I of the series) is the second part of the block header (see below):    Block header dump:  0x0201490a  Object id on Block? Y  seg/obj: 0x1c205  csc: 0x00.2d11214  itc: 2  flg: -  [...]

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail. As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the [...]

As discussed in my earlier post on Bitmap Index Degradation After DML Prior To 10g, Oracle wasn’t particularly efficient in the manner it maintained Bitmap Indexes after DML operations. During insert operations, if an existing Bitmap index entry didn’t cover the rowid range of a new row to be inserted, Oracle would create a new Bitmap index entry with a [...]

Just a short note for anyone near the Canberra region that the next ACT Oracle User Group Seminar Session will be held next Wednesday, 2 June 2010 at the Oracle Offices in Turner. I’ll be presenting “Indexing New Features and Improvements Introduced in Oracle 11g Release 1 & 2″. Follow the above link for the full agenda. [...]

Bitmap Indexes have a bad reputation with regard to being problematic and suffering from severe degradation following heavy DML operations, especially larger amounts of insert activity. Bitmap indexes have been known to grow substantially and require periodic rebuilds following such insert activity. While this was certainly the case in previous versions of Oracle, things have dramatically improved since version 10g. [...]

A basic little post to conclude this discussion. The issues regarding whether to go for single column indexes vs. concatenated indexes are similar for Bitmap indexes as they are for B-Tree indexes.   It’s generally more efficient to access a concatenated index as it’s only the one index with less processing and less throwaway rowids/rows [...]

Although Bitmap Indexes are commonly created on one column, you can create multi-column, concatenated Bitmap indexes as well.   Many of the same issues and factors in deciding to create a single, multi-column index vs. several, single column indexes apply to Bitmap indexes as they do with B-Tree indexes, although there are a number of [...]

As I’ve discussed previously, using a Bitmap index on a unique column makes little sense as the underling index must be larger than a corresponding B-tree index due to the implicit additional overheads associated with Bitmap indexes. As such, Oracle doesn’t permit the use of a Bitmap Index on a declared unique column or to [...]

As discussed in the previous post, a Bitmap index on a unique column will be larger than a corresponding Btree index due to the additional overheads associated with each index entry (such as the additional rowid, the additional column length bytes and the bitmap column itself). Oracle therefore attempts to protect you from explicitly creating such [...]