All language subtitles for [SubtitleTools.com] Bitmap Indexes - Learning Oracle 12c [Video]

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.