Improved Query Performance with Variant Indexes

Patrick E. O'Neil and Dallan Quass.
University of Massachusetts at Boston, and Stanford
Scribe by: Zuyu Zhang

One-line Summary

Overview/Main Points

• Background
  • Select * from Student S where S.sid = 123456789
    • For B⁺-tree on sid is great because s.sid is high cardinality (distinct values)
  • Select * from Student S where S.age = 20 AND S.major = "CS"
    • Age and major are low cardinality.
    • Scan the table if no index.
    • B⁺-tree index built on age and major, but low performance
      • Rid is big (several bytes).
      • Scanning long index list may not faster than table scan.
• Bitmap Index (Sybase IQ)
  • Sometimes it is a good idea
    • low cardinality ⇒ few bitmaps.
    • slightly cardinality ⇒ compresses really well (Run-length encoding or general compress algo).
    • Anding & oring, etc are blazingly fast.
    • “count” queries (population count) work really well.
  • Map bitmap to page
    • Same # records on each page: easy; just mod.
    • Varying # records per page?
      • Why varying # records?
        • Records may be a various size.
        • Fast insertion.
        • Removing items does not have to move others.
      • Calculate max records per page by assuming every page has these many records.
      • Add a “ missing ” bitmap index for predicates like NOT “ CS ”.
  • The bit-slice index (64 bit at most)
    • Suppose we have 4 bit integer field
    • Answer queries like a column with odd number.
• Joins using bitmap index
  • A common query
    • Student (sid, sname, did)
    • Dep (did, dname)
    • What is the dname of the department of student with id "X"?
  • "normal" B⁺-tree index on student id
  • "Join Index" B⁺-tree on join attribute
  
  • Bitmap join index

Relevance

Flaws