RE: Bitmap index or no index?

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Tue, 07 Apr 2009 10:05:40 +0200
Message-Id: <581613166_at_web.de>



Michael,

besides what has been raised by Mark Farnham, you need to consider how the table is going to be modified.

Although Oracle 10 has improved the issue with deteriorating bitmap indexes due to DML applied, bitmap indexes still can cause severe locking issues due to the nature how rowid ranges are covered by a single index entry.

So if your table is subject to frequent OLTP transactions, as a general rule of thumb don't use a bitmap index.

If you don't/can't follow Mark's suggestion and you need to specifically index the NULL values, you can still use b*tree indexes to achieve this.

There are a coupe of options:

  1. Create a function-based composite index using a constant non-null literal.

CREATE INDEX <new_index> ON mfgunit(backflushed, 0);

This is effectively going to index the null values in backflushed, therefore this index can be used to identify the nulls. Note that this index can be used for other kinds of predicates on backflushed, too, like "backflushed = 1"

2. Create a very specific function-based index only indexing the null values of backflushed:

CREATE INDEX <new_index> ON mfgunit(case when backflushed is null then 'X' else null end);

and use a corresponding filter predicate:

select replace(p.item_no, '-'), count(*) from pumpdata pd, part p, mfgunit m
where pd.mfgunitid = m.mfgunitid and case when m.backflushed is null then 'X' else null end = 'X' and pd.partid = p.partid and pd.pumpdefectsid=0 group by p.item_no
order by 2;

The advantage of this approach is that you get a very tiny index that is not very costly to maintain.

The disadvantage is that you can use this index only for this specific purpose.

> Looking at the
> query I may add a bitmap on pumpdefectsid if it doesn't exist already
> since that only has 7 values I think.

This is one of these myths that seem to go away only very slowly: Think: If you only have 7 distinct values in "pumpdefectsid" with couple of million rows, each value will cover on average a couple of hundred thousand rows. Reading these many rows in a random fashion using any kind of index (doesn't matter if b*tree or bitmap) is very likely to be far less efficient than simply running a FTS through the whole segment. In worst case you need a couple of hundred thousand random I/Os to read the rows reading/accessing the same blocks again and again. The FTS is very likely to require far less I/O operations unless your table has more then db_file_multiblock_read_count * couple of hundred thousand blocks, which is quite unlikely the case.

Things look different though if the 7 values are skewed, and you only have a few rows that have pumpdefectsid=0, but then a b*tree index is suitable again, no need for an bitmap index.

Bitmap indexes on columns with a low number of distinct values are only powerful in special cases (index-only query without table access) or in particular if you can combine multiple of them to very selectively identify a few rows from your table, e.g. "attrib1 = 0 and attrib2 = 'RED' and attrib3 = 'USA'", each of them on its own not selective but combined identify only a couple of rows, that's the power of bitmap combined operations. By the way, since Oracle 9i Oracle by default can convert b*tree indexes on the fly to bitmaps to perform such combine operations even on b*tree indexes.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

> Date: Mon, 06 Apr 2009 16:24:07 -0400
> From: Michael Ray <topshot.rhit_at_gmail.com>
> Subject: Bitmap index or no index?

> For the following query should I bother with a bitmap index on
> backflushed or just go without an index?

> select replace(p.item_no, '-'), count(*)
> from pumpdata pd, part p, mfgunit m
> where pd.mfgunitid = m.mfgunitid and m.backflushed IS NULL
> and pd.partid = p.partid and pd.pumpdefectsid=0
> group by p.item_no
> order by 2;

> This is 10.2 with a couple million rows in the mfgunit table. The number
> of rows where backflushed is null will be very few FOR the records that
> match the join condition with the pumpdata table. Once the records that
> match this query are processed, backflushed is set for them. A process
> will regularly add new records that will match the query.



Verschicken Sie SMS direkt vom Postfach aus - in alle deutschen und viele auslšndische Netze zum gleichen Preis!
https://produkte.web.de/webde_sms/sms
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 07 2009 - 03:05:40 CDT

Original text of this message