Finish Multidimensional Data, Query Execution

CS157b

Chris Pollett

Feb 21, 2018

Outline

Multiple key index is a kind of index on more than one attribute.
We will consider the two attribute case -- the more than two attribute case is implemented in a similar way.
In 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.

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 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`.

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.

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.

Suppose our records consisted of order pairs of dice rolls, `(a,b)` where `a,b \in \{1,2,3,4,5,6\}`. Exactly one record can fit into a block.
Randomly generate 5 such records and insert them into a QuadTree. If a pair falls on the boundary of a splitting say it belongs to the upper, more right quad. Draw the quad (using ASCII graphics preferably) after each insert.

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 pointer to all the records that lie within the bounding region of the root.
Regions are allowed to overlap.
The algorithms used for insertion start at the root of the tree, and grow 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.
Below is an example region for a node and its subregions

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.

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.

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.