Re: Q: bitmaps and grouping

Barbara,

It's just a shot in the dark but I've had similiar problems with b-tree indexes as well. In some cases I was able to get the query to rewrite to the index by putting a NOT NULL constraint on the column. In some cases this was necessary even when there was a PK or UNIQUE constraint on the column...I had to explicitly place a NOT NULL constraint on top of it.

In general, Oracle makes better decisions the more information you give it about the rules in your data. For example, in the case of multi-table joins where there are FKs enforced by the application but not by Oracle constraints (perhaps for performance reasons) you can still define the FK with a DISABLE NOVALIDATE clause; that way the FK is defined but not "enforced" by Oracle, and yet Oracle can now use that information to make better data access decisions.

When you get this behavior try spelling out *every little thing* to Oracle, even if it's with DISABLE NOVALIDATE and see if that helps.

Hope that helps. Cheers --Peter

In article <pJFU5.639$TA6.82078_at_nntp1.onemain.com>,   "Barbara Kennedy" <barbken_at_teleport.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 15:01:39 CST