(with apologies to Robert Ludlum and Eric Van Lustbader)

The Bitmap Betrayal (Introduction)

Oracle performance tuning is an excellent source of myths. The very best ones have a group of adherents who continue to support the myth even when presented with counter-examples. Who’s heard of these?

• Joins are faster than sub-queries
• Sub-queries are faster than joins
• Full Table Scans are bad

Those ones have been around as long as I can remember. Probably the single greatest concentration of Oracle performance tuning myths centres on Bitmap Indexes. Are these familiar?

• Bitmap indexes are good for low-cardinality columns, whereas B-Tree indexes are good for high-cardinality columns.
• Bitmap indexes are slow to update.
• Bitmap indexes don't support concurrent updates.

What is a Bitmap Index?

This is first post of the four-part epic - The Bitmap Conspiracy - detailing the structure and behaviour of Bitmap Indexes. Later in the series we will cover the internal structure of Bitmap Indexes, how Oracle uses them, and finally we will expose some of the myths surrounding them. But before we get there let’s just get a clear understanding of what a Bitmap Index actually is.

The Structure of a Bitmap Index

I’ve been tuning Oracle database applications for a long time now. I started out recognising some simple patterns and applying template fixes (Got a full table scan? Use an index!) but such a collection of “Do this; don’t do that” anecdotes will only take you so far. If you are curious (I was), you can uncover the reasons why one method is faster than another; i.e. what is the computer doing to make slow code so slow. I found that a good understanding of the internals meant that you didn’t always need to know how to tune a specific example because you could work it out for yourself.

In a database application, these investigations frequently lead to data structures; how does the database store its information and how does it retrieve it? Good information on the internals of Bitmap Indexes is hard to piece together, so in Part 2 of this Bitmap Indexing epic we will look more closely at the internals of Bitmap indexes.

Bitmap Execution Plans

In Part 1 we touched briefly on how Oracle can use Bitmap Indexes to resolve queries by translating equality and range predicates into bitmap retrievals. Now that we know more about how they are stored (see Part 2), let’s look closer at some of the operations that Oracle uses to access Bitmap Indexes and manipulate bitmaps.

1. Myth #1: Bitmap indexes are good for low-cardinality columns

Myth #1: Bitmap indexes are good for low-cardinality columns

This is one of those myths that is probably best demonstrated by example. First, let’s create some test data: 10 million rows with the following columns:

• COL1 – Alternating values of 1 and 0 – not indexed
• COL2 – Identical to COL1 – bitmap indexed
• PAYLOAD – 100 bytes of X’s to make the rows a realistic width – not indexed

COL1 and COL2 are identical low cardinality columns: there are only two valid values.

Apps clone is like cloning a human being. Please read the detail below.

1) Run adpreclone on DB/CM/AP tiers

This is pre-requisite step of cloning 11i prod and will not change anything in any system. This script will only create on $COMMON_TOP, a directory called "clone", with a basic structure of executable, scripts, templates and java files.

An age old problem with SQL is pagination through a row set. You want to display rows one page at a time, in some sort of order. The issue is architectural: SQL is a set oriented language. It deals with rows as a group, not individually, making it impossible to paginate through a set because that would require navigation from row to row. There are work arounds, there are potential programming bugs, and there is a Database 12c New Feature: the row limit clause. All demonstrations below are done with 12c final beta, release 12.1.0.1.0. Below is the old and the new way, with a bit of reverse engineering.

12 MArch 2013 update: Content removed - it exceeded what is permitted for the pre-release status of the product.
Sorry about that, will repost when I can.
JW.

I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.

Today’s topics:
1. Both mechanisms are based on hash functions.(About hash tables and hash collisions for scalar subquery caching excelent wrote Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3.