Re: Q: bitmaps and grouping

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.
Received on Mon Nov 27 2000 - 21:34:17 CST