Re: Query execution for intervals
From: Mikito Harakiri <mikharakiri_nospaum_at_yahoo.com>
Date: 9 Feb 2006 11:43:12 -0800
Message-ID: <1139514192.886855.166610_at_z14g2000cwz.googlegroups.com>
Date: 9 Feb 2006 11:43:12 -0800
Message-ID: <1139514192.886855.166610_at_z14g2000cwz.googlegroups.com>
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