Re: Using bitmap indexes

dbaplusplus_at_hotmail.com wrote:
> I am using Oracle 9.2.0.5 on HP UNIX 11i. There are some large tables
> (million rows) in which access is done on columns with low cardinality
> (e.g., region). A bitmap index will be a good fit, but whenever I read
> Oracle litertaue on bitmap indexes, it says if table is modified (i.e.,
> new rows being added/deleted), do not use bitmap indexes. My
> application is combination of OLTP and data warehouse, i.e, table is
> being modified but not that much, i.e, there may be 1000 rows added
> every day.
>
> Will I see performnace imporvements in using bitmap indexes in such
> cases?
>

First, you need to construct test cases yourself that contain relevant SQL queries and where clauses that are of interest to you. Questions like you posed cannot be answered generically, you need to understand exactly the problem ( if there is one or multiple ones ) that you are attempting to solve and construct and measure test cases.

If there are multiple columns in a table that are being used together in a where clause, you can create a regular multi column index.

Jonathan Lewis has some good articles and information available on bitmap indexes as does Tom Kyte.

My advice is to do some more research and if it seems worthwhile, on a test system, create some specific test cases and check it out yourself.

Oracle 10g has added some improvements where oracle has attempted to limit the collateral damage experienced with updating and inserting against tables with bitmap indexes. You may want to setup test environments that include not only 9.2 but also 10.2 to see and assess impact.

Don't forget to include only the SQL select queries in your test cases, also provide SQL that does the inserting and updating. It's not terribly hard to create a set of scripts in multiple sessions where one set of sessions is doing retrieval access and another set of sesssions is doing the changing of data. Received on Sun Nov 12 2006 - 10:02:31 CST