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

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