Oracle Indexes

Richard Foote's picture

Index Block Dump: Index Only Section Part I (TVC 15)

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 [...]

Richard Foote's picture

Index Block Dump: Block Header Part II and Read Consistency (I Can’t Read)

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: -  [...]

Richard Foote's picture

Index Block Dump: Block Header Part I (Editions Of You)

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 [...]

Richard Foote's picture

Bitmap Index Degradation Since 10g (Fix You)

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 [...]

Richard Foote's picture

ACT Oracle User Group Seminar Session: 2 June 2010

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. [...]

Richard Foote's picture

Bitmap Index Degradation After DML Prior To 10g (Beauty and the Beast)

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. [...]

Richard Foote's picture

Concatenated Bitmap Indexes Part II (Everybody’s Got To Learn Sometime)

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 [...]

Richard Foote's picture

Concatenated Bitmap Indexes Part I (Two Of Us)

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 [...]

Richard Foote's picture

So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)

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 [...]

Richard Foote's picture

Unique Bitmap Indexes Part II (You Can’t Do That)

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 [...]

To prevent automated spam submissions leave this field empty.
Syndicate content