On Aug 4, 11:51 am, Helma <bad_elef...@hotmail.com> wrote:
> > Ideally you might arrange the design such that the small number of
> > rows you typically want to identify are flagged with a 'Y' (say) and
> > the rest left null. Then a standard btree index on that column would
> > be compact and efficient.
>
> Redesign? What's wrong with my suggestion to create an index on only
> the Y value's?
>
> H.

Perhaps because FBIs can lead to wrong results returned prior to the
10.2.0.3 patchset being applied?

Paging Noons!

-bdbafh

Richard Foote wrote:
>
> "Thomas Kellerer" <JUAXQOSZFGQQ@spammotel.com> wrote in message
> news:5hgnqqF3l1n69U1@mid.individual.net...
> > Richard Foote wrote:
> >> "Thomas Kellerer" <JUAXQOSZFGQQ@spammotel.com> wrote in message
> >> news:5hg5aiF3kia4nU1@mid.individual.net...
> >>> ciapecki wrote:
> >>>> Hi,
> >>>>
> >>>> Does indexing a very big table (about 5Mio records) on the columnA
> >>>> which can hold only values Y,N,<NULL> make sense?
> >>>>
> >>> Yes, that's what bitmap indexes were made for.
> >>>
> >>
> >> Hi Thomas
> >>
> >> A Bitmap index is of no use if both Y and N are roughly evenly
> >> distributed and you have no other predicate in the query.
> >>
> >> Returning approximately 2.5 millions rows through a bitmap index would be
> >> dramatically slower than a full table scan.
> >>
> >> A single bitmap index only would be useless in this scenario, even more
> >> so if the table is subjected to any transactional based DML load.
> >>
> >
> > Hi Richard,
> >
> > thanks for the pointing this out.
> > I wasn't aware of that, but it does sound reasonable.
> >
> > But after all the Concepts manual says:
> >
> > "If the number of distinct values of a column is less than 1% of the
> > number of rows in the table, or if the values in a column are repeated
> > more than 100 times, then the column is a candidate for a bitmap index."
> >
> > Actually a bit further down in the Concepts manual there is an example
> > very similar to the OP's situation:
> >
> > "There are only three possible values for marital status and region, two
> > possible values for gender, and four for income level. Therefore, it is
> > appropriate to create bitmap indexes on these columns"
> >
>
> Hi Thomas
>
> There are a number of classic myths associated with bitmap indexes and yes,
> Oracle is as guilty as anyone in propagating them. One is that bitmap
> indexes are only useful for low cardinality columns. The above definition is
> better than many I've read but it's still one of those rules of thumbs that
> is not entirely accurate as column values outside of the definition could
> possibly be candidates for a bitmap index.
>
> The other classic myth is that a *single* bitmap index on a very low
> cardinality column (as in the OPs example) can be very efficiently utilised
> to retrieve the required number of rows. But if this means retrieving 50% or
> 33% or 25% etc of all rows in a huge table, then it not going to be very
> efficient at all when compared to the poor old full table scan.
>
> Note in the above example, it mentions 4 different columns, not one column.
> These four columns when *combined* could possibly reduce the final result
> set to a small enough subset of required rows that would make retrieving
> them one at a time through the rowids a possibly attractive option. For
> example, there may not be that many single males that live in Canberra that
> have a really low income ...
>
> At the end of the day, it comes back to the overall selectivity of
> predicates and can combinations of bitmaps when and/or/not together produce
> a small enough set of rowids to make it all worthwhile to read the bitmap
> blocks, perform the set logic and retrieve the resultant rowids one at a
> time when compared with other alternatives (such as the full table scan).
>
> The answer is almost certainly a big no for a single bitmap index on a low
> cardinality column.
>
> Cheers
>
> Richard

Unless all you do is COUNT

(Not disagreeing with RF here - just adding to his argument that these
things need to looked at on a case by case basis - there's no single
statement the covers all the possibilities)
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------