Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Q: bitmaps and grouping

Re: Q: bitmaps and grouping

From: <pwenker_at_my-deja.com>
Date: Mon, 27 Nov 2000 23:34:58 GMT
Message-ID: <8vur32$hms$1@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 - 17:34:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US