What are Multidimensional Index Structures in Relational Databases?

Question

I'm currently taking a database class. I was watching some videos on relational databases, and when I was doing some research, I happened to come across this multidimensional indexing topic. Out of curiosity, I tried reading into it a little bit, and I don't exactly understand what it's talking about, as it seems to be a pretty advanced topic. So I was wondering if I could get a little push in the right direction, with some answers to the following questions. I would love some examples (as most of the information I'm finding is research papers) if at all possible. It's not part of my class, but I'm kind of intrigued, and I'd like to know more, to see if it could be useful to me.

1. What is multidimensional indexing?
2. How does it relate to Relational Databases?
3. When and how is it used?
4. Are there any examples?

Thank you very much for your help!

Answer 1

This may be an over-simplification, but I am trying to make it easy to understand.

An index is a sequential listing of column data. An index can be used on any column. Indices are usually placed on the primary key of a table. An index increases the performance of a query. Similarly, when there is more than one column in a primary key, we have a multi-dimensional index.

There are generally two ways to implement a multi-dimensional index:

1. Concatenate multiple indices. An excellent explanation is provided here.

The article explains a scenario. When two companies merge into one, the employeeId of either company is no longer unique. We add a discriminator column, subsidiaryId to differentiate. The query still has to be fast, so we place both the columns into the index.

2. Combine multiple indices

If fields F1 and F2 are a string and an integer, respectively, and # is a character that cannot appear in strings, then the combination of values F1 = 'abcd' and F2 = 123 can be represented by the string 'abcd#123'

As you can guess, both have their own pros and cons.

Examples would be Geographical information systems, range queries (where you are satisfied with getting the probable range of your answer), nearest neighbor queries. Multi-dimensional indices are greatly used in Data cubes, OLAP etc.