Search

Top 60 Oracle Blogs

Recent comments

Oracle Indexes

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

Unique Bitmap Indexes Part I (Unnatural Selection)

As I’ve discussed previously, a Bitmap index can be considered over a B-tree index (where concurrent DML is not an issue) even if there are potentially tens of millions of distinct values, in a table that has say hundreds of millions of rows.   However, if a column is unique or “approaches” uniqueness, then one [...]

Myth: Bitmap Indexes With High Distinct Columns (Supermassive Black Hole)

As discussed in my previous post, it’s a silly myth to suggest a bitmap index should only be used for so-called “low cardinality” columns else the resultant index would be “huge”. I thought it might be worth a quick refresh to see a simple little demonstration why such claims are a nonsense.  There is in fact no limit to [...]

Myth: Bitmap Indexes With High Distinct Columns (Blow Out)

I just couldn’t resist.   One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values. A classic example of this myth being propagated is in this latest Burleson news item, “Oracle bitmap index maximum distinct values“, from Feb 16 [...]

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

I’ve slipped this post in as there have been a number of discussions recently on how execution plans have changed while nothing else appears to have changed in the database. How can an execution plan suddenly change when no one has made any changes to the database ?   By no changes, it means that there [...]

Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood)

I thought before I jump into a topic that requires looking at a number of index block dumps, it might be worth briefly recapping how one goes about dumping index blocks in Oracle.   A block dump is simply a formatted representation of the contents of a particular Oracle database block.  Although I’ll be focusing [...]

The CPU Costing Model – A Few Thoughts Part II

As previously discussed, the formula used by the CBO using the CPU costing model is basically:
 
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
/
average [...]

2 Year Anniversary !!

A little anniversary slipped by unnoticed this past weekend. Which isn’t really too surprising considering how busy things have been lately, with Christmas just around the corner.
I’ve now been at this blogging lark for 2 whole years !!
Hopefully, there are a few people out there just that little bit wiser now regarding how indexes work [...]