Re: bitmap conversion on a index that is not bitmapped ???

"Fedock, John (KAM.RHQ)" wrote:
>
> Stephan,
>
> That is correct. The old behavior is not what I want. I have rebuilt and reanalyzed, deleted stats - tried all sorts of combinations and I cannot get it to use the bitmapped access as it once did. Again - these are not bitmapped indexes to begin with.
>
> John

John,

   Interesting. Please understand that, as Gopal pointed out, thoes bitmapped indexes really are built 'on the fly' as a (as it appears, efficient) way to boost the query speed. The question, and I don't have the answer (but perhaps the eminent specialist of event 10053 on the list could shed some light) is what incites the optimizer to do this. Low cardinality is obviously a factor, but it cannot be the only one since I guess that the cardinality of your column musn't have changed much. This is a shot in the dark, but I'd certainly consider the clustering factor too. If keys with similar values are clustered, my guess is that bitmaps will contain big swathes of 1s or 0s or whatever, and Oracle will be able to pick lots of blocks of interest in one pass. If, however, the distribution of 'good' rows among 'bad' rows is pretty uniform, there is not much point in taking the trouble of building the bitmapped index. Would be interesting to play with the stats on a development database.

HTH, SF

> -----Original Message-----
> Sent: Wednesday, October 15, 2003 4:29 PM
> To: Multiple recipients of list ORACLE-L
>
> K Gopalakrishnan wrote:
> >
> > John:
> >
> > Optimizer is a smart boy!!! He knows the column has few distinct values
> > and decides the BITMAP access would be appropriate and making BITMAP
> > plans from the BTree indexes. If you delete the stats for that index,
> > you will get the old behavior.
> >
> > KG
> >
> > =====
> > Have a nice day !!
> > ------------------------------------------------------------
> > Best Regards,
> > K Gopalakrishnan,
> > Bangalore, INDIA.
> > --
>
> ... Unfortunately the old behaviour seems to be the bad one. I guess
> that the solution would rather be to rebuild indexes on a regular basis
> ...
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).