Re: Query execution for intervals

Mikito Harakiri wrote:
> I believe bitmapped indexes behave in linear way, although with very
> generous coefficient.

This experience was from 5 years ago when I experimented with bitmapped plans in some expression engine. It doesn't seem to hold any longer (on today's hardware). I fail to make a test where bitmapped plan outperforms full table scan! Here is what I did:

• Interval boundaries only. Adding more columns would slow down full table scan. create table intervals ( x number, y number );
• Assume that even though the number of intervals is relatively large
• (=10M) the number of intervals that cover any given point is small (~5K)
• Otherwise, there is no hope that interval query could be tuned to be fast insert into intervals select dbms_random.random/13415727. + level/100, dbms_random.random/13455727. + level/100 from dual connect by level < 10000000;

commit;

CREATE BITMAP INDEX xi
ON intervals(x);

CREATE BITMAP INDEX yi
ON intervals(y);

• both indexes select --+ index(i xi) index(i yi) count(1) from intervals i where 500 between x and y; 18 sec
• one index select --+ index(i xi) count(1) from intervals i where 500 between x and y; 16 sec
• FTS select --+full(i) count(1) from intervals i where 500 between x and y; 2.5 sec