Original subtitles

In this lesson we discuss bitmap indexes.

So bitmap indexes are different or distinct

from B-tree indexes, which we might

consider regular indexes or the standard index that is used.

Bitmap indexes were designed because Oracle realized

that certain situations called for an index,

say, as a large table.

But structuring those values into a B-tree structure

was not the most efficient way to do so and really

gave you very little benefit.

So a bitmap index is a special type

of index for very specific situations.

So in order to understand a bitmap index,

you have to understand the concept of cardinality.

So cardinality is the number of distinct values in a set.

So if we looked at cardinality in terms of a table

and the column values that it has,

let's look at an employee table that has employee ID.

So the employee ID, let's say, is different,

is distinct for every employee.

Every employee has a different employee ID.

So in a million row table of employees,

there would be a million different values

for employee ID.

So we refer to that as high cardinality, a large number

of distinct values.

So high cardinality would be lots of distinct values.

But what if we had a column in that table,

in the employee table, called gender?

So gender, typically going to have an M for male or F

for female.

So we would refer to that as a very low cardinality column.

What if we tried to create a B-tree index on gender,

on the gender column?

Well, if it was structured, it would simply

have an M and an F, and then all of the different row IDs

that were possible underneath it.

So you haven't really gotten any benefit

from the structure of the data.

You still have to scan through a large number of values.

So if you looked at it as 50/50, just

by kind of the numeric odds, then you've

reduced your one million row scan

to 500,000 distinct values.

But that's not the same kind of benefit

that you get from a B-tree index.

And so a bitmap index is used when the column

values are low in cardinality.

That is to say, they have a small number

of distinct values.

Now, we said this is only for certain situations.

So rule number 1 is low cardinality in the column.

And what constitutes low cardinality?

Well, that is somewhat debatable.

I think generally Oracle advises that,

if the cardinality of the column is about 10%--

so about 10% of the rows in the table

are distinct in those number of values--

then a bitmap index can be helpful.

But it is best really to just test it and try it, and run

explain plans and get the execution plan,

and see things like the cost, test how long the query runs,

those types of things.

The other thing that is important in a bitmap index

is that you have few or no updates on that column.

Because of the way a bitmap index is structured,

updates are going to be a very high resource

intensive operation.

So, if you create a table and put a lot of bitmap indexes

on it, you're going to find that your updates of that table,

if they're updating that value, are going to be slower.

So it's best if you are not doing updates to the table.

So, if we were to look at the structure of a bitmap index,

it's quite different than a B-tree index.

And so what we have are a value and then its row ID.

And let's say it was the gender column and we have M and F.

And so our first row ID would be this value,

and it would be an M. So our value is M for male

and associated with this row ID.

Then the next is female, associated with this row ID,

so on and so forth.

So, now, if we do a query against the employee table

where gender equals M, the scan simply

has to look for where this value is essentially marked.

Here, gathers the row ID, no, no.

Here, gathers the row ID.

So on and so forth.

And it'd be sort of the opposite for female.

No, yes, get the row ID.

Yes, get the row ID.

And then no.

So let's look at how to create a bitmap index that's

very similar to a B-tree.

We simply say create bitmap index.

And we're going to do it on the job table

on Scott emp in the job column.

So, if we look at that table and decide

whether it's a good candidate, we

see that the job column as clerk and salesman and manager,

but there's a fairly low number of distinct values.

Whereas this emp now, they're entirely unique and distinct.

Not a good candidate for a bitmap index.

But job is certain categories that people have a job in.

We could say that that's a low cardinality column.

We create a bitmap index in order

to be able to select data from that table in the most

efficient way possible when we're

querying on the job column.