Outline

• Indexes for Multidimensional Data
• Quiz
• Query Execution

Introduction

• Last week, we looked at hashed based approaches to single key (note: we are saying single key, not single attribute) indexes, first using a static number of buckets and chaining, then using a dynamically determined number of buckets via either extensible hashing or linear hashing.
• We then talked about how files and indexes are stored for particular DBMSs.
• We ended last day by showing how techniques for single key look up often don't save time for spatial queries or data cube applications.
• We are now going to look at multi-dimensional/multi-key indexing techniques.
• We will start with two hash based approached: partitioned hash tables and grid files.
• We then switched to tree-like index approaches and consider multi-level indexes, kd-tree, and quad trees.

Grid Files

• A grid file is an index structure on more than one attribute.
• We split each attribute dimension by a set of grid lines.
• For Points table example might have lines `x= 100, 200, 300, 400, ...` and `y=100, 200, 300, 400...`.
• To implement the grids lines, we could imagine we have two functions: xgrids(x) and ygrids(y).
• xgrids(x) (ygrids(y) operates in a similar fashion) given a value `x` returns the three grid lines satisfying `x_{i-1} lt x_i le x lt x_{i+1}`.
• These two functions are specified at index creation time.
• Given xgrids(x) and ygrids(y) we then have a hash function `h(x,y)` which first computes `(x_i, y_j)`.
• This gives a rectangle that (x,y) belongs to: `(x_i,x_{i+1}] times (y_j,y_{j+1}]`.
• The hash function then computes based on `(x_i,y_j)` a single block number in the table.
• We can extend this idea in a similar fashion if have more than two dimensions.
• Each bucket has a set of pointers to records of Points in that rectangle.
• If a bucket is too full, we use overflow blocks.
• So to look up a point, we compute `h(x,y)` to get a block number, then looks for the record in this bucket.
• To insert into a grid file, we just determine the rectangle the Point to be inserted belongs to, and add a pointer to the corresponding bucket.
• Note we can implement neighborhood lookup by using xgrids and ygrids to find a set of rectangles that cover the neighborhood we want, then search through all the points in that neighborhood.

Partitioned Hash Files

• A Partitioned Hash Table is a hash table on several attributes `A_1, A_2, ...`; however, we use a special kind of hash function such that the first `k_1` bits output determined by `A_1` only, the next `k_2` bits by `A_2` only, ...
• The buckets mapped to by the hash function are such that they are laid out by dimensions. So if we fix say `k_1=a`, we can easily compute all the hash blocks that have `k_1 = a`.
• Unlike a grid-file nearby points might be hashed to completely different buckets, so can't do nearest neighbor style queries.
• The data in a partitioned hash table will tend to be more uniformly dispersed and so we are less likely to need overflow buckets.
• Partitioned hash table are useful if we only care about partial match lookups (specify some attributes, leave others unspecified).

Multiple Key Indexes

• Multiple key index is a kind of index on more than one key.
• We will consider the two key case -- the more than two attribute case is implemented in a similar way.
• In a two key index, we first make a tree index on the first attribute.
• The leaves of this tree are possible values for this attribute.
• The pointer followed from this value points to a tree index on the second attribute.

Uses of Multiple-Key Indexes

• Supposed we have such an index on Age and Salary.
• If, in a query, we specify both a fixed Age value (say 35) and a fixed Salary (say 20K), the results can be fast.
• However if we wanted all Age's for a given Salary, such an index would be slow if Age was the first top index.
• Multiple key indexes work well for range queries and nearest neighbor queries. (Treat nearest neighbor as a range query)

kd-trees

• kd-trees are a generalization of binary search trees.
• Each non-leaf node has some attribute `A` and some value `V`.
• Leaf nodes contain record pointers to actual records.
• The tree satisfies that beneath a non-leaf node on attribute `A` with check `V`, the records to the left of the node are less than `V`, those to the right are greater than or equal to `V`.

Operations on kd-trees

• Basic search in a kd-tree is to follow the nodes down the tree, following the branch on an attribute by choosing the side that matches the record we are searching for.
• Partial match search involves following both paths out of a node if do not know its value.
• Similarly, for a range query, if the range straddles a query we have to follow both branches.
• Nearest neighbor search is treated as a special kind of range query.
• To do an insertion:
  • We first do a basic lookup search to find the leaf node the record should be inserted into.
  • If this node is not full, we add the record there.
  • If not, we choose the attribute that has the most possible values amongst those in the leaf node.
  • We then split the node in two based on the median value of this attribute and create a new interior node using this attribute and value.
• To optimize kd-trees for disk rather than main memory, rather than split on one value `V` at a node, split `n` ways based on block size.

Quiz

Which of the following statements is true?

1. Linear hash tables make use of an indirection lookup before accessing hash blocks.
2. Extensible hashing increases the bucket size by 1 whenever the overall fill factor of the table is greater than 85%.
3. The Key Sequenced Data Set (KSDS) format of IBM is similar to the older ISAM format.

Quad-trees

• Quad-trees are a kind of two attribute index.
• To keep things simple, call these attributes x, and y.
• Leaf nodes contain record pointers.
• Non leaf nodes store for each attribute the highest and lowest value that might appear in the whole tree for that attribute.
• Let `mid_x = (low_x + high_x)/2` and `mid_y = (low_y + high_y)/2`.
• Non leaf nodes also store four child node pointers for each quadrant given by the four rectangles ((`low_x, low_y`), (`mid_x, mid_y`)), ((`mid_x, low_y`), (`high_x, mid_y`)), ((`low_x, mid_y`), (`low_x, high_y`)), and ((`mid_x, mid_y`), (`high_x, high_y`)).
• Quad-tree operations are similar to kd-trees.
• For search down a tree, we follow the appropriate choice of one of the four child node pointers based on the record we are searching for.
• To insert, we search to a leaf block and insert there, if possible.
• If not, we split the node into four subregions and a parent node (percolating this up the tree).
  
• In the above a * indicates one blocks worth of data.
• Quad-tree can be generalized if have keys on more than two attributes.

R-Trees

• An R-tree node consists of a bounding region data structure (usually a rectangle), together with either pointer to child R-tree nodes, or to the data itself (leaf case.)
• R-tree can be used to store not only points, but also more complicated objects like building footprints, roads, etc.
• The leaf nodes of an R-tree are supposed to have pointers to all the records that lie within the bounding region of the root.
• Regions are allowed to overlap.
• The algorithms used for insertion starts at the root of the tree, and grows its region if needed, then a child is selected (often heuristically based on the child region that best holds the object) and then the process is repeated.
• If a leaf node is too full, then it is split, and the splitting is propagated back up the tree.
• R-trees are useful for "Where am I?" queries: I.e., what regions in the R-tree contain a given point.
• To answer such a query, we start at the root and ask if it contains the point. We then traverse the children of the root to see which (maybe more than one) contain the point. If a child node contains the point, we recursive search its children for containment.
• Below is an example region for a node and its subregions
  

Bitmap Indexes

• A `n`-bit vector `V` is a string of 0's and 1's of length `n`.
• A bitmap index for a field `F` is a collection of n-bit-vectors, one for each value of `F`.
• As an example, suppose we have a table Car with an attribute Color.
• If Car had six rows, we might get a bitmap index for the different possible values for Color that looks something like the below:

  Color	Vector
  red	100100
  blue	010010
  green	001001

• A 1 above indicates that in that row of Car the given color value occurred.
• So the first Car row's Color value was red, the second Car row's Color value was blue, etc.

Uses of Bitmaps

• Bitmaps can be very fast for operations like intersection and union, since these map to ANDs and ORs
• They are also good for range queries.

Compressed Bitmaps

• Frequently, we have lots more 0's than 1's in a given bitmap.
• So it makes sense to do things like run-length encoding to compress the bitmap.
• There are algorithm for doing each of the operations we mentioned on the compressed bitmaps.

Query Execution

• We are going to start talking about how query results are computed by the DBMS.
• An overview of the operations involved might look like:
  

An Algebra for Queries

• One of the first steps in evaluating a query is to turn the original SQL into an internal representation, based on relational algebra, that we can then manipulate.
• We will view our algebra as operating on bags rather than sets.
• Bags (sometimes called multi-sets) are collections where order doesn't matter but where we do allow duplicates. For example, {{1, 2, 1,3}} = {{2, 3, 1, 1}} are the same multi-set. Neither of these is the same multi-set as {{1, 2, 3}}.
• We use bags since some of our operation like projecting onto attributes (columns) might result in outputs with duplicates.