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@xxxxxxxxx> 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
