Dynamic optimization of index scans restricted by Booleans
Gennady Antoshenkov · 2002
When index retrieval is restricted to a range or singleton, an index scan is not done in its entirety because key portions before and after the range are skipped. Likewise, in some production databases, gaps between multiple ranges are skipped. However, ranges on the second attribute of a composite key are considered unproductive for key skip because they do not constitute a key range. This is not so. Restriction age=40 for index [sex,age] can be viewed as ORed singletons "female"/spl par/40 OR "male"/spl par/40 and gaps around them can be skipped using a fraction of I/Os needed for a full index scan. In this paper, a novel method of skipping gaps at index scan is introduced which, during index scan, detects practically all gaps for arbitrary Boolean restrictions and skips them. The efficiency of this technology is illustrated using a prototype implementation.