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.
