Re: Oracle 7.3.2 and bitmap indexes

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sun, 24 Oct 1999 13:23:09 +0800
Message-ID: <381297BD.4E29@yahoo.com>

michael_bialik_at_my-deja.com wrote:
>
> Hi.
>
> I am using Cost Based Optimizer with Oracle 7.3.2.3.
> When I execute
> SELECT * FROM tab_1
> WHERE <some conditions> AND
> status IN ( 1,5,8,9,11 );
> ( STATUS field has about 7-8 distinct values and BIT-MAP index
> is defined on that field ) - Optimizer ignores IN clause
> completly and display rows with status 2/3/etc.
> When I drop the bitmap index on status, or use
> status + 0 IN (1,5,8,9,11) - it work fine.
>
> Any suggestions, folks?
>
> Michael.
>
> P.S. I tried to find some info on Oracle.technet, but no go.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

7.3.2.3 had some bugs in the optimiser (not necessarily bitmap index related but definitely CBO related) where you could get rows back that should not have been included. I saw one recently with INDEX_DESC causing all sorts of nasties...

7.3.3.5 seemed to be lowest jump we could make before the dramas disappeared - the alternative being to delete the stats for this table or use the RULE hint.

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Sun Oct 24 1999 - 00:23:09 CDT