Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 03, 2016 - 9:05 pm UTC

Last updated: July 05, 2016 - 12:55 am UTC

Version: 11g2

Viewed 1000+ times

You Asked

when to use bitmap or b*tree index?
And which one is best for containing 1 Milion rows in a single table contain child-parent relationship?

and Connor said...

Two important things to consider

- read performance
- side effects during writes

Read performance is easy to test

a) create each index
b) run a selection of typical queries
c) see what works best overall

Side effects is where things get interesting

a) dml with a bitmap index will be most likely be slower than with a b-tree index

b) dml with a bitmap index will potentially lock more rows than just the row(s) affected by the dml.

c) if you are using an index on a child table to avoid locking concerns when updating the parent, then only a b-tree index does that.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But what are the best practices ?

A reader, July 04, 2016 - 3:03 pm UTC

Can you please elaborate point c. It's confusing,ambiguous .

So b tree does child locking ?

Bitmap does not ?

Thanks
Chris Saxon
July 05, 2016 - 12:55 am UTC

"best practice" is to

- understand each
- utilise each where it is best for *your* situation

If a book, blog, etc says "use bitmap indexes when the distinct values are less than 5% of the rows", then trusting that as a bulletproof recommendation is a recipe for disaster.

So, in terms of foreign locking:

In the docs

https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT88972

under the section "Locks and Foreign Keys", you'll that

a) when you have a child table to refers back to a parent table, and
b) the column in the *child* table is not indexed, and
c) you do certain kinds of operations on the *parent* table (eg delete)

then the whole child table is locked for the duration of the statement's execution.

Sometimes this is not an issue, sometimes it is. But if you decide that you *do* need that index, then a bitmap index is not sufficient to solve the locking issue - it has to be a b-tree index.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.