Re: Q: bitmaps and grouping

Thanks everyone who helped with this. I'm still experimenting, but I have made some progress thanks to everyone's suggestions. So far here's what I've learned:

   Steps to get a query to rewrite to a bitmap index:

1. create the bitmap index
2. analyze the table
3. analyze the bitmapped column
4. do one of the following:
   • put a hinted view on top of the table and access the view instead of the table
   • or set a table hint?
   • use INDEX or INDEX_COMBINE
   • alter session set optimizer_index_cost_adj=1; -- or some low value, Oracle apparantly makes a decision based on the table's statistics
   • alter session set optimizer_mode =first_rows;
5. you may need to set these parameters: -optimizer_features_enable -compatible
6. if none of the above works, your table may be too small for the Oracle optimizer to choose anything but a full table scan. To test, create a bogustable with more rows and run EXPLAIN PLAN against it.

   ..as you can see by looking at the possibilities in 4) above, so far I haven't been able to find a way to get Oracle to use bitmap indexes that doesn't potentially harm performance for other types of queries. If I set optimizer_index_cost_adj too low, Oracle will do index scans and table lookups even in situations when full table scans are more appropriate. Same applies to using a hinted view. And a global optimizer setting of first_rows may harm large queries where throughput is more important than respose time.

So far the problem I have with bitmap indexes is their productive use seems to depend on a more in-depth knowledge of your system than is required for b-tree indexes; also Oracle's relative reluctance to use them forces tradeoffs that maybe aren't worth it.

Anyone know how to get Oracle to use bitmaps without harming the rest of the system?

Thanks --Peter

In article <pJFU5.639$TA6.82078_at_nntp1.onemain.com>,   "Barbara Kennedy" <barbken_at_teleport.com> wrote:
> I found this was true on regular indexes also.
> "select blah from aTable group by blah;"
> would do a full table scan even if blah was indexed.
>
> Jim
> <pwenker_at_my-deja.com> wrote in message
 news:8vur32$hms$1_at_nnrp1.deja.com...
> > Mike,
> >
> > That didn't do it; I got the same result. But thanks anyway --Peter
> >
> > In article <8vupu1$gpf$1_at_nnrp1.deja.com>,
> > Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> > > In article <8vup9u$g72$1_at_nnrp1.deja.com>,
> > > peter.wenker_at_usa.net wrote:
> > > > Greetings,
> > > >
> > > > I'm having trouble getting Oracle to use a bitmapped index for a
> > > > grouping operation. I've created a two-column test table and a
 bitmap
> > > > index on one of the columns. Oracle uses the bitmap index when
 I
 issue
> > > > a query on the indexed column using an IN function, but when I
 issue
 the
> > > > same query with the addition of a GROUP BY clause Oracle does a
 full
> > > > table scan.
> > > >
> > > > Any ideas? Thanks in advance for any help. Below please find
 some
> > > > details. --Peter
> > > >
> > > > ------------------
> > > > DETAILS:
> > > >
> > > > create table test
> > > > tablespace users
> > > > as select datekey, domainkey
> > > > from fact
> > > > where datekey = 860
> > > > and rownum < 11;
> > > >
> > > > insert into test select datekey, domainkey
> > > > from fact
> > > > where datekey = 842
> > > > and rownum < 11;
> > > >
> > > > create bitmap index test_dt_bm on test(datekey);
> > > > analyze table test compute statistics for all indexed columns;
> > > > analyze index test_dt_bm validate structure;
> > > >
> > > > explain plan set statement_id = 'TEST' for
> > > > SELECT
> > > > datekey
> > > > FROM
> > > > test
> > > > where datekey in (842, 860);
> > > >
> > > > Q_PLAN
> > > > ---------
> > > > SELECT STATEMENT
> > > > INLIST ITERATOR
> > > > BITMAP CONVERSION TO ROWIDS
> > > > BITMAP INDEX SINGLE VALUE TEST_DT_BM
> > > >
> > > > explain plan set statement_id = 'TEST' for
> > > > SELECT
> > > > datekey, count(*)
> > > > FROM
> > > > test
> > > > where datekey in (842, 860)
> > > > group by datekey; --WORKS OK
> > > >
> > > > Q_PLAN
> > > > ------------
> > > > SELECT STATEMENT
> > > > SORT GROUP BY
> > > > TABLE ACCESS FULL PROVTEST --?? Why???
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.
> > > >
> > >
> > > try making the selects the similar....
> > >
> > > explain plan set statement_id = 'TEST' for
> > > SELECT
> > > datekey, count(datekey)
> > > ^^^^^^^
> > > FROM
> > > test
> > > where datekey in (842, 860)
> > > group by datekey;
> > >
> > > --
> > > Michael Krolewski
> > > Rosetta Inpharmatics
> > > mkrolewski_at_rosetta.org
> > > Ususual disclaimers
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 14:55:39 CST