Q: bitmaps and grouping

From: <peter.wenker_at_usa.net>
Date: Mon, 27 Nov 2000 23:04:35 GMT
Message-ID: <8vup9u$g72$1@nnrp1.deja.com>

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