Re: Q: bitmaps and grouping

From: Mike Krolewski <mkrolewski_at_rosetta.org>
Date: Mon, 27 Nov 2000 23:15:18 GMT
Message-ID: <8vupu1$gpf$1@nnrp1.deja.com>

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.

Received on Mon Nov 27 2000 - 17:15:18 CST