Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
CREATE INDEX
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The CREATE INDEX statement allows for creating indexes on table columns.
CREATE INDEX
In its simplest form, the statement can be used like this:
// Create an index on a single column
create.createIndex("index").on("table", "column").execute();
// Create an index on several columns
create.createIndex("index").on("table", "column1", "column2").execute();
CREATE UNIQUE INDEX
In many dialects, there is a possibility of creating a unique index, which acts like a constraint (see ALTER TABLE or CREATE TABLE), but is not really a constraint. Most dialects will create an index automatically to enforce a UNIQUE constraint, so using a constraint instead may seem a bit cleaner. A UNIQUE INDEX is created like this:
// Create an index on a single column
create.createUniqueIndex("index").on("table", "column").execute();
// Create an index on several columns
create.createUniqueIndex("index").on("table", "column1", "column2").execute();
Sorted indexes
In most dialects, indexes have their columns sorted ascendingly by default. If you wish to create an index with a differing sort order, you can do so by providing the order explicitly:
// Create a sorted index on several columns
create.createIndex("index").on(
table(name("table")),
field(name("column1")).asc(),
field(name("column2")).desc()
).execute();
Covering indexes (with INCLUDE clause)
A few dialects support an INCLUDE clause when creating an index. This can be useful to create covering indexes. These are indexes that "cover" the needs of an entire query, such that no secondary lookup needs to be done in a heap table or clustered index, after finding only parts of the projection in the index data structure. The data from the columns of the INCLUDE clause will be located only in the index leaf nodes (useful for projections), not in the index tree structure (useful for searches), which reduces index maintenance overhead, and index size.
If a dialect does not support this clause, jOOQ will simply add the INCLUDE columns into the ordinary index column list.
// Create a covering index with included columns
create.createIndex("index").on("table", "search_column").include("projection_column").execute();
Partial indexes (with WHERE clause)
A few dialects support a WHERE clause when creating an index. This is very useful to drastically reduce the size of an index, and thus index maintenance, if only parts of the data of a column need to be included in the index.
// Create a partial index
create.createIndex("index").on("table", "column").where(field(name("column")).gt(0)).execute();
Feedback
Do you have any feedback about this page? We'd love to hear it!