Slideshow:
Answering Partial Match queries using a Multiple-key index
Example:
One scan of the Level 2 index file will find all the data records !!
Answering Partial Match queries using a Multiple-key index
Example:
Processing:
Answering Partial Match queries using a Multiple-key index
Example:
Processing:
Answering Partial Match queries using a Multiple-key index
Example:
Processing:
Answering Partial Match queries using a Multiple-key index
Example:
Processing:
Answering Range queries using a Multiple-key index
Solution:
Answering "Nearest Neighbor" queries using a Multiple-key index
How to find the nearest (smallest distance) neighbor:
Use the expanding range search algorithm (with pruning)
Answering "Nearest Neighbor" queries using a Multiple-key index
Find the nearest neighbor of (age=45, salary=250)
We need a good candidate point:
Answering "Nearest Neighbor" queries using a Multiple-key index
Find the nearest neighbor of (age=45, salary=250)
We need a good candidate point:
Answering "Nearest Neighbor" queries using a Multiple-key index
Find the nearest neighbor of (age=45, salary=250)
We need a good candidate point:
Answering "Nearest Neighbor" queries using a Multiple-key index
Prune away the first level indexes that exceeds the candidate distance:
Search in the unpruned branches to find a better candidate (and prune further when found)
Answering "Nearest Neighbor" queries using a Multiple-key index
Continued search in an unpruned branch:
Answering "Nearest Neighbor" queries using a Multiple-key index
Use the better candidate to prune away 65 because |45−65| = 20 > 15:
Answering "Wher-am-I" queries using a Multiple-key index
-
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
- Use the level 1
index on age to
find the index blocks
for age = 25
- Find all people wih
age = 25
-
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
- We must read
all index files
at the lower levels
- Multiple-key index
for partial match query is
most
useful when
the search value is
the first level index
-
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 ≤ 100Solution:
- 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
- Use age = 35 in the
first index and find
the first value that is
≥ 35:
-
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 |
- Find the nearest neighbor
to (age=45, salary=250)
- 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....
- Find the
nearest bucket in the
level 1 index:
- The multiple key index
can help
in the processing
of Nearest neighbor queries
-
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))
- Multiple-key index
are not useful
in Where-am-I queries...