Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about other dbms?
Oracle has an index type called a Bitmap Index which it describes as...
A database index in which the database stores a bitmap for each index key instead of a list of rowids.
If a table has a bitmap index or a hint is used, it can use a bitmap access plan on regular B-tree indexes. Bitmap indexes can be joined, union-ed, and intersected.
There is an excellent explanation at use-the-index-luke.com where it includes the following implementations of combining multiple B-Tree indexes:
MySQL: MySQL has an index merge optimization starting with release 5.0.
Oracle The Oracle database uses BITMAP CONVERSIONs to combine multiple indexes on the fly (introduced with 9i).
PostgreSQL PostgreSQL uses bitmaps to combine multiple indexes since version 8.1.
SQL Server SQL Server can use multiple indexes ("Index Intersect") starting with V7.0 using a hash algorithm.
See also this StackOverflow question in which the first answer says that SQL Server does something similar to Bitmapped indexes using index intersection.
Efficient and Flexible Bitmap Indexing for Complex Similarity Queries is the closest reference I have found associating Bitmapped indexes with the word multidimensional. Multidimensional seems to be more of a way to use indexes rather than an attribute of them.
SQL Server may perform Index intersection:
The SQL Server optimizer has many algorithms and operators to put together reusable execution plans for queries that you run against a system.
One is the ability to perform index intersection and/or union, which basically means that it can use multiple indexes for a query against one table and perform intersect or union operations to return a result set.
Being able to use multiple indexes against a table allows you to create a number of narrow indexes that the optimizer can put together in different ways for different query plans.
This in turn minimizes the need for a multitude of specialized indexes, which minimizes index storage space and modification impact.
Also, from the documentation for Query Tuning Recommendations:
Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query. Consider the sample query:
SELECT * FROM lineitem WHERE partkey BETWEEN 17000 AND 17100 AND shipdate BETWEEN '1/1/1994' AND '1/31/1994' ```
SQL Server can exploit indexes on both the partkey and shipdate columns, and then perform a hash match between the two subsets to obtain the index intersection.
Is this what you mean?
Possibly Oracle bitmap indexes are that what you are looking for. Oracle Index types.
I think they permit union and intersection. But I admit, that I'm not familiar with them.