patternMinor
Multidimensional indices in databases
Viewed 0 times
indicesmultidimensionaldatabases
Problem
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?
Solution
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:
DB2:
DB2 supports multiple index access on
LUW 9r7 (using a dynamic bitmap) and
on zOS v10.
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.
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:
DB2:
DB2 supports multiple index access on
LUW 9r7 (using a dynamic bitmap) and
on zOS v10.
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.
Context
StackExchange Database Administrators Q#1755, answer score: 9
Revisions (0)
No revisions yet.