Indexes
Information about what indexes (other than basic B-/B+ tree indexes) are supported natively.
| R-/R+ tree | Hash | Expression | Partial | Reverse | Bitmap | GiST | GIN | Full-text | Spatial | FOT | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 4th Dimension | ? | Cluster | ? | ? | ? | ? | ? | ? | Yes | ? | ? |
| ADABAS | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | |
| Adaptive Server Enterprise | No | No | Yes | No | Yes | No | No | No | Yes | ? | |
| Advantage Database Server | No | No | Yes | No | Yes | Yes | No | No | Yes | ? | |
| Apache Derby | No | No | No | No | No | No | No | No | No | ? | |
| CUBRID | No | No | No | No | Yes | No | No | No | ? | ? | |
| Drizzle | No | No | No | No | No | No | No | No | No | ? | |
| DB2 | No | ? | Yes | No | Yes | Yes | No | No | Yes | ? | |
| Empress Embedded Database | Yes | No | No | Yes | No | Yes | No | No | No | ? | |
| Firebird | No | No | Yes | No | Yes 1 | No | No | No | No | ? | |
| HSQLDB | No | No | No | No | No | No | No | No | No | ? | |
| H2 | No | Yes | No | No | No | No | No | No | Yes | ? | |
| Informix Dynamic Server | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| Ingres | Yes | Yes | Ingres v10 | No | No | Ingres v10 | No | No | No | ? | |
| InterBase | No | No | No | No | No | No | No | No | No | ? | |
| Linter SQL RDBMS10 | No | No | No | No | No | No | No | No | Yes | ? | |
| LucidDB | No | No | No | No | No | Yes | No | No | No | ? | |
| MaxDB | No | No | No | No | No | No | No | No | No | ? | |
| Microsoft Access (JET) | No | No | No | No | No | No | No | No | No | ? | |
| Microsoft Visual Foxpro | No | No | Yes | Yes | Yes2 | Yes | No | No | No | ? | |
| Microsoft SQL Server | ? | Non/Cluster & fill factor | Yes3 | Yes4 | No3 | No | No | No | Yes | Yes | |
| Microsoft SQL Server Compact (Embedded Database) | No | No | No | No | No | No | No | No | No | ? | |
| MonetDB/SQL | No | Yes | No | No | No | No | No | No | ? | ? | |
| MySQL | MyISAM tables only | MEMORY, Cluster (NDB), InnoDB,5 tables only | No | No | No | No | No | No | MyISAM tables only | MyISAM tables only | ? |
| Oracle | Yes 11 | Cluster Tables | Yes | Yes 6 | Yes | Yes | No | No | Yes | Yes | |
| Oracle Rdb | No | Yes | ? | No | No | ? | No | No | ? | ? | |
| OpenLink Virtuoso | Yes | Cluster | Yes | Yes | No | Yes | No | No | Yes | ? | |
| Polyhedra DBMS | No | Yes | No | No | No | No | No | No | No | No | |
| PostgreSQL | Yes | Yes | Yes | Yes | Yes7 | Yes8 | Yes | Yes | Yes | PostGIS | |
| RDM Embedded | No | Yes | No | Yes | Yes | No | No | No | No | No | No |
| RDM Server | No | No | No | Yes | Yes | No | No | No | No | No | No |
| ScimoreDB | No | No | No | No | No | No | No | No | Yes | ? | |
| SQL Anywhere | No | No | No | No | No | No | No | No | Yes | ? | |
| SQLite | Yes | No | No | No | Yes | No | No | No | Yes | SpatiaLite | |
| Teradata | No | Yes | Yes | Yes | No | Yes | No | No | ? | ? | |
| UniVerse | Yes | Yes | Yes3 | Yes3 | Yes3 | No | No | No | ? | Yes | |
| Xeround Cloud Database | No | Yes | No | No | No | No | No | No | No | No | |
| R-/R+ tree | Hash | Expression | Partial | Reverse | Bitmap | GiST | GIN | Full-text | Spatial | FOT |
Note (1): The users need to use a function from freeAdhocUDF library or similar.
Note (2): Can be implemented for most data types using expression-based indexes.
Note (3): Can be emulated by indexing a computed column (doesn't easily update) or by using an "Indexed View" (proper name not just any view works)
Note (4): Can be implemented by using an indexed view.
Note (5): InnoDB automatically generates adaptive hash index entries as needed.
Note (6): Can be implemented using Function-based Indexes in Oracle 8i and higher, but the function needs to be used in the sql for the index to be used.
Note (7): A PostgreSQL functional index can be used to reverse the order of a field.
Note (8): PostgreSQL will likely support on-disk bitmap indexes in a future version. Version 8.2 supports a related technique known as "in-memory bitmap scans".
Note (10): B+ tree and full-text only for now.
Note (11): R-Tree indexing available in base edition with Locator but some functionality requires Personal Edition or Enterprise Edition with Spatial option
Read more about this topic: Comparison Of Relational Database Management Systems
Famous quotes containing the word indexes:
“Our memories are card indexes consulted and then returned in disorder by authorities whom we do not control.”
—Cyril Connolly (1903–1974)