Editing Join (SQL) (section)

===Join indexes===
Join indexes are [[database index]]es that facilitate the processing of join queries in [[data warehouse]]s: they are currently (2012) available in implementations by [[Oracle database|Oracle]]<ref>Oracle Bitmap Join Indexes.
{{cite web
|url=https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html#GUID-3286EBA4-0D5B-423D-815B-997A3E4B4B6C
|title=Database Concepts - 5 Indexes and Index-Organized Tables - Bitmap Join Indexes
|access-date=2024-06-23
<!--
|url-status=dead
|archive-url=https://web.archive.org/web/20240623094250/https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html#GUID-3286EBA4-0D5B-423D-815B-997A3E4B4B6C
|archive-date=2024-06-23
-->
}}</ref> and [[Teradata]].<ref>Teradata Join Indexes.
{{cite web
|url=https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Data-Definition-Language-Syntax-and-Examples/Index-Statements/CREATE-JOIN-INDEX
|title=SQL Data Definition Language Syntax and Examples - CREATE JOIN INDEX
|access-date=2024-06-23
<!--
|url-status=dead
|archive-url=At the moment the archiving sites are unable to archive the page.
|archive-date=2024-06-23
-->
}}</ref>

In the Teradata implementation, specified columns, aggregate functions on columns, or components of date columns from one or more tables are specified using a syntax similar to the definition of a [[database view]]: up to 64 columns/column expressions can be specified in a single join index. Optionally, a column that defines the [[primary key]] of the composite data may also be specified: on parallel hardware, the column values are used to partition the index's contents across multiple disks. When the source tables are updated interactively by users, the contents of the join index are automatically updated. Any query whose WHERE clause specifies any combination of columns or column expressions that are an exact subset of those defined in a join index (a so-called "covering query") will cause the join index, rather than the original tables and their indexes, to be consulted during query execution.

The Oracle implementation limits itself to using [[bitmap index]]es. A ''bitmap join index'' is used for low-cardinality columns (i.e., columns containing fewer than 300 distinct values, according to the Oracle documentation): it combines low-cardinality columns from multiple related tables. The example Oracle uses is that of an inventory system, where different suppliers provide different parts. The [[database schema|schema]] has three linked tables: two "master tables", Part and Supplier, and a "detail table", Inventory. The last is a many-to-many table linking Supplier to Part, and contains the most rows. Every part has a Part Type, and every supplier is based in the US, and has a State column. There are not more than 60 states+territories in the US, and not more than 300 Part Types. The bitmap join index is defined using a standard three-table join on the three tables above, and specifying the Part_Type and Supplier_State columns for the index. However, it is defined on the Inventory table, even though the columns Part_Type and Supplier_State are "borrowed" from Supplier and Part respectively.

As for Teradata, an Oracle bitmap join index is only utilized to answer a query when the query's WHERE clause specifies columns limited to those that are included in the join index.