Re: optimizing SQL's w/ multiple (and variable) range based predicates against very wide table

Hi Mladen,

I'm afraid my knowledge is really only from 11.2. Some google fu suggests that it was possible as far back as Oracle 9 but I can only find an example that includes the star query transformation https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90237/schemas.htm . Hinting at opt_param('optimizer_features_enable' '8.0.0') still allows a hinted bitmap index range scan (I checked on 19c livesql to confirm that my opt_param hint was being accepted), although I don't think that necessarily confirms it was possible in 8i.

I've only found bitmap conversion to rowids a problem when there are masses of rowids to create - the sort of situation where it's likely the optimizer has made a dramatic mistake with some cardinality. I have also seen it many times in queries that shouldn't need to access the table at all but the CBO decides rowids are necessary.

I don't agree with the "unordered hash tables" comment, the branch blocks of bitmap indexes are exactly like btree index branches, index keys are in order. It is the leaf blocks that contain the bitmap data. Julian Dyke has an internals presentation
http://juliandyke.com/Presentations/Presentations.html#BitmapIndexInternals that goes over the structure. He also states that the bitmap index range scan was around as early as 7.3.3.

Perhaps your preference against bitmaps was justified in earlier versions but for me they have been hugely beneficial to allow multiple unknown predicates be used effectively with indexes (when the optimizer does its job well).

Thanks,
Andy

On Sat, 26 Jan 2019 at 20:03, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> Hi Andy,
>
> You are right, I checked the documentation:
>
> https://docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#TGSQL95210
>
> My "knowledge" is probably from the version 10 database. Do you know
> when did range scans become possible with bitmap indexes? However, there
> is a dreaded "BITMAP CONVERSION TO ROWIDS" which I tend to avoid at all
> costs because from my experience it uses a lot of CPU and memory. It
> can, somewhat inaccurately, be described as a conversion of bitmap index
> to B*Tree index. However, given the fact that bitmap indexes are
> essentially unordered hash tables, I would still avoid range scans on
> bitmap indexes. Bitmap indexes are excellent for equality predicates. If
> the range predicates are frequently needed, I would rather consider a
> normal, B*Tree index.
>
> Regards
>
>
> On 1/26/19 2:20 PM, Andy Sayer wrote:
> > Mladen,
> > Bitmap indexes work perfectly fine with range predicates. Your example
> > is just a small table with high selectivity filter: it’s cheaper to
> > full table scan than use indexes to read a fifth of the table.
> >
> >
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l