Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: bitmaps and grouping
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.
> >
>
>
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Nov 27 2000 - 17:34:58 CST