### Using a Multiple-key index

• Performance of Multiple-key index on common multi-dim queries

• Partial Match queries

 The query specifies conditions on some dimensions but not on all dimensions

• Multiple-key index is useful when partial information given on the first level index

Example:

• Find all people wih age = 25

Explanation:

• Use the level 1 index on age to find the index blocks for age = 25

• Then:

 Scan all entries in the salary index file (= list of blocks) indexed by age= 25 to access the records

• Note:

 Multiple-key index for partial match query is most useful when the search value is the first level index Multiple-key index is less useful when the first dimension is not specified

Example:

• Multiple-key index is not very useful in the following query:

 Find all people who earn \$60,000 who buy jewelry.

How this query is processed using a multiple-key index:

• Access the first age value:

Then find 60K in the index file for age = 25:

When found, read the data record

• Access the second age value:

Then find 60K in the index file for age = 30:

When found, read the data record

• And so on !!!

• Result:

 We must read all index files at the lower levels This could result in many disk operations

• Range queries

 Find objects that are located either partial or wholly within a certain range

Example:

• Find all people with:

 ``` 35 ≤ age ≤ 50 50K ≤ salary ≤ 100 ```

Solution:

• Use age = 35 in the first index and find the first value that is ≥ 35:

We only need to search a limited number of lower level index files

• Nearest neighbor queries:

• The multiple key index can help in the processing of Nearest neighbor queries

BUT:

 We must use expanding range search algorithm in "nearby branches" of the index tree

I will illustrate the procedure with an example...

• Problem:

• Find the nearest neighbor to (age=45, salary=250)

For simplicity, we define the following distance measure:

 ``` distance(A, B) = | A.age - B.age | + | A.salary - B.salary | ```

• The expanding range search algorithm:

• Find the nearest bucket in the level 1 index:

Find the nearest bucket in the level 2 index within the index file of level 1:

Compute the search distance value:

 ``` Search distance = | 45 - 45 | + | 250 - 270 | = 20 ```

• Use the search distance to prune away branches of the first level index ( too far away to be a candidates)

Expand the search in the "candidate" branches:

• If the |45 - age| < 20 (search distance) then:

 Search for the nearest neighbor in the corresponding branch of the subtree

Example:

Update the search distance value:

 ``` Search distance = | 45 - 40 | + | 250 - 260 | = 15 ```

• Repeat: use the updated search distance to prune away branches of the first level index ( too far away to be a candidates)

Expand the search in the "candidate" branches:

And so on....

• Where-am-I queries:

 Multiple-key index are not useful in Where-am-I queries... (Multiple-key index can only index points, not objects (with length and width))