Re: BITMAP INDEX doesn't work on our Oracle server 7.3.2.3.0

From: David Richardson <drichardson_at_all-4-one.com>
Date: 1997/05/25
Message-ID: <5m9cmj$r4h$1@news9.gte.net>#1/1

Hi Jian,

Searched the USenet for this message, might help, but also look in the 7.3 tuning guide for more technical answers. I was also under the impression that the parallel query option had to be installed to use bitmap indexing in 7.3.

DER

Xref: alice comp.databases.oracle.misc:1857 comp.databases.oracle.server:2223 comp.databases.oracle.tools:1533 Path:
alice!ragnarok.oar.net!malgudi.oar.net!echoes.wittenberg.edu!callisto.cns.muskingum.edu!oucsboss!cbgw2.lucent.com!cbgw1.lucent.com!csn!nntp-xfer-1.csn.net!su-news-hub1.bbnplanet.com!news.bbnplanet.com!cam-news-hub1.bbnplanet.com!howland.erols.net!worldnet.att.net!hunter.premier.net!uunet!in3.uu.net!199.170.183.55!brighton.openmarket.com!decwrl!purdue!mozo.cc.purdue.edu!news From: "Brad W. Skiles" <bwskiles_at_adpc.purdue.edu> Newsgroups:
comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools Subject: Re: Experience in bitmap index in Oracle 7.3 Date: 6 Feb 1997 21:19:59 GMT
Organization: Purdue University
Lines: 40
Message-ID: <01bc1473$5568fe30$f065d280_at_freh-nt-007> References: <32FA6519.3CD6_at_sas.se>
X-Newsreader: Microsoft Internet News 4.70.1155

We've begun implementing bitmaps on our 7.3.2.3 HP-UX system. The experience has been good and bad.

Good

Storage improvements have been dramatic. An index on a fiscal_year field
drop from 370 megs down to 2.7 megs. Not all reductions have been that extreme, but that shows you what the potential is. Performance has also shown dramatic improvements in some areas. I've seen
the cost factor for some queries, using explain plan, drop to a fraction of
its original size. Ditto for the execution time.

Bad

I've had a rough time predicting the size of these indexes in advance. I
thought I had it worked out once, and the next index was several times larger than I had predicted. I'm still hoping someone can shed some concrete light on a formula for sizing bitmap indexes. It's more complicated than the number of unique keys*number of rows*1 bit.

We've now received 2 patches for 2 seperate bugs that appeared when we tried to create bitmap indexes. (I'm guessing we're on to a 3rd bug now.)
One bug occured when a bitmap index appeared in the "and join" section of
an explain plan. The other bug was revealed when bitmap indexes generated
an ora-600 error when we attempted to build the indexes on one particular
table.

The 3rd bug I now suspect has to do with views having to be rebuilt if we
add a bitmap index to one of the tables on which the view is based.

I'm also suspicious that the bitmap indexes are causing Oracle to consume
more memory. At any rate, my memory useage is up, and I haven't identified
any change other than the bitmaps.

Directions

Instructions for bitmap indexes are in the Tuning book. Be sure to read about setting the COMPATIBLE parameter in your init.ora file. Good luck!

--brad
--

David Richardson	1-800-425-6719
All 4 One, Inc.		drichardson_at_all-4-one.com
2026 Aquia Drive	http://www.all-4-one.com

Stafford, VA 22554 Received on Sun May 25 1997 - 00:00:00 CDT