CREATE INDEX
NAME
CREATE INDEX: make an index on a table (extension)
SYNOPSIS
<create index statement> ::=
CREATE INDEX <index name> ON <table name>
( <key part> [ { , <key part> }... ] )
[ USING <method name> ]
[ WITH ( <index option> [ { , <index option> }... ] ) ]
<key part> ::= <column name> | ( <value expression> )
<index option> ::= <option name> = { <option value> | DEFAULT }
DESCRIPTION
CREATE INDEX makes an index on columns of a base table, kept by an index
method: btree, a B-link tree, the default; hash, a linear hash table;
rtree, a spatial tree over coordinate columns or fixed-SRID geometries;
fulltext, an inverted index of a text column's words; json, an
inverted index of a column's JSON documents' paths and values;
xml_path, xml_value, xml_property or xml_primary, of an XML
column's paths and values; adjacency, the edges of an edge table kept
by their endpoints, for following a graph; labels, an element table's
rows numbered for a graph's label tests; reachability, which vertices
an edge table's edges lead to, for pruning a graph's searches; or a
method a plug-in registers. The planner uses an index when its cost
says the index is the cheaper way to find a query's rows: by the
comparisons of its columns with constants in a WHERE clause, BETWEEN,
IN lists of constants, ORs of these of one column, and LIKE patterns
that begin with characters; hash by equalities and IN lists alone. A
btree index read for an ORDER BY of its columns, or for ranges of
them an OR or an IN list names, gives the rows in its order, so they
are not sorted again. A join whose condition equates an indexed column
with a column of the other table, an inner join or a LEFT outer join
whose right table is the indexed one, may look up each row of the other
table in the index, when they are few, rather than read the indexed
table whole. A part of a WHERE clause, or of an ON clause, that can
raise an error, as a division can, is evaluated on every row of the
tables it reads, which are then read whole, so the error is raised
whatever the plan. An index changes no result: only how fast it comes.
A plug-in method may also support a filter such as WHERE f(column, ?).
The registered function takes two arguments, returns BOOLEAN or null and
uses an indexed column in either argument position. The other argument
is a constant or parameter. The method declares which functions it
supports; the planner chooses a lookup by cost, as it does for a
comparison (RULES.md D34).
The index may return extra rows, called candidates. The executor fetches each visible row and checks the full WHERE condition before returning it. Only functions that promise the same answer for the same inputs, no side effects, no errors for accepted arguments and null for a null argument can use this lookup. A filter that can raise keeps a full table read, so an index cannot hide its error (D15). This Boolean function contract is separate from the built-in geometry lookup described below. See Indexes for function calls for a runnable example and the Rust API.
An rtree index takes two REAL, FLOAT or DOUBLE PRECISION columns in
(X, Y) order for points, or four in (min X, min Y, max X, max Y) order
for rectangles. Coordinates must be finite; each minimum must be at most
its maximum. WHERE comparisons can constrain several coordinates at once,
including ranges on both X and Y. The index checks each comparison against
the stored coordinates and provides no SQL key order. It omits rows with
a NULL coordinate, so the planner uses it only when those rows cannot
answer the query. An inner join can probe the index with coordinate bounds
from each row of the other operand. Both table orders and projections of
the indexed table's columns are considered; candidate rows still satisfy
the complete join condition.
An rtree can also index one GEOMETRY(srid) column. It stores the
geometry's envelope and can supply candidates for
ST_INTERSECTS(column, value) = 1, where value is a constant or parameter,
and for the other relationships true only of values that meet, ST_RELATE
of a pattern asking them to meet among them.
The executor fetches the visible geometry and checks the full condition;
an envelope match alone never establishes intersection. The column must
enforce one SRID. NULLs are omitted and empty values never intersect.
ST_DISTANCE(column, value [, unit]) <= limit, or <, reads the
envelopes within the limit. Inner and outer joins, LEFT, RIGHT and FULL,
can also probe it from the other operand's geometry; the full ON
condition checks every candidate pair, and an outer join keeps the rows
no probe matched, with nulls. ST_DISTANCE ordered with FETCH FIRST,
ascending or descending, with a unit or without, can also use this
index, refining each
candidate's geometry before deciding which rows to return. See
Spatial Data for its rules and the
Spatial Guide for runnable examples.
An rtree made over rows already in the table is built whole and packed
from the root down, its nodes nine tenths full, its sorts spilling past
MEMORY.WORK_BYTES. Rows written while it is built are kept. A node that
deletes leave empty or under two fifths full is taken out or merged, the
nodes above it too when that leaves them sparse, and its space reused
once no query can still read it. The index grows shorter as it empties
(RULES.md D139). See
Spatial Data.
A fulltext index takes one FULLTEXT column, or one character string
column with no collation. It keeps each word of the column's text and
the rows holding it, and supplies the rows for CONTAINS(column, value) = 1 and TEXT_MATCHES(column, value), where value is a constant or
parameter, and the best ranked rows of a Contains filter ordered by
RANK(column, value) DESC with FETCH FIRST. The executor checks each
row it finds. Its options are FULLTEXT.LANGUAGE, FULLTEXT.STOP_WORDS,
FULLTEXT.K1, FULLTEXT.B and FULLTEXT.RANKING, which names BM25 or a
ranking the program registered. See Full-Text Search for its rules and the
full-text guide for runnable examples.
A json index takes one column of JSON documents, a character string
with no collation or of type JSON. It answers JSON_EXISTS, JSON_VALUE
compared equal to a constant or parameter, and JSON_CONTAINS, of a call
of the column whose other arguments are constants or parameters and
that gives null or false on an error, never ERROR ON ERROR. It may find
more documents than the call holds of, and each row it finds is checked
by the whole WHERE clause. A document that is no JSON text is found by
every lookup, so its error is raised as a scan raises it. Its options
are JSON.PATHS_ONLY, ON or OFF, the default, which keeps paths and no
values, and JSON.MAX_DEPTH, 0 to 128, the default, the most member
names of a path it keeps; changing either makes it anew. See the
JSON guide for runnable examples.
An xml_path, xml_value, xml_property or xml_primary index keys
one XML column: each element and attribute of its values, by its path,
its value as a string and as a number, and its place in document order,
each method in its own order (XML indexes,
Partitioned Tables);
xml_primary keeps each node's value whole up to 512 octets, so it
answers of a row from the index alone. No comparison of SQL reads it, as
XML values are not comparable: the planner reads it for the paths of
XMLEXISTS, XMLQUERY and XMLTABLE (Queries an XML index reads
for). A value with more
elements and attributes than it keys,
or a name or path longer than the database's page size holds, is refused
as it is written.
An adjacency index takes the columns of an edge table's source
endpoint, then those of its destination, then any edge properties it
covers. ADJACENCY.SOURCE_COLUMNS, 1 by default, says how many columns
are the source, and ADJACENCY.INCLUDE, 0 by default, how many of the
last are properties included; the columns between are the destination.
An included column must be NOT NULL. ADJACENCY.DIRECTION is BOTH, the
default, OUT or IN: it keeps each edge under its source, under its
destination, or both, so a graph is followed forward, backward, or
either way at the cost of one lookup a step. A row with a null in its
key has no entry. A column with a collation is refused. The planner
reads it for an equality of the source columns, or of the source and
destination, as a join's lookup too.
Its entries are kept in a B-link tree, with each vertex's count of edges. Maintenance compacts them into compressed sparse rows in memory, held of the memory budget and of ADJACENCY.CSR_BYTES, 256 MiB by default; a lookup reads them, and the tree for a vertex written since, so it finds what the tree holds. An index whose compacted form would not fit is read from its tree alone. Changing ADJACENCY.CSR_BYTES takes effect in place; changing any other option makes the index anew. INFORMATION_SCHEMA.ADJACENCY_INDEXES shows each one's options.
A labels index takes any NOT NULL columns of a table, its key in a
property graph by custom: CREATE LABEL INDEX makes them
(CREATE LABEL INDEX). It numbers the table's
rows densely, in memory, of the memory budget, and keeps its entries in
a B-link tree. A GRAPH_TABLE over a graph of the table reads its
members as bitmaps of the labels, for label tests and for the vertices
a pattern starts at. The planner never reads it for a WHERE clause. A
row's number is kept while one of its versions has an entry, and
maintenance numbers the rows again once more than an eighth of the
numbers are of rows gone. A nullable column is refused, as a row with
a null in its key would have no entry.
A reachability index takes the columns of an edge table's source
endpoint, then as many of its destination, of the same types in order:
REACHABILITY.SOURCE_COLUMNS, half of the columns by default, says how
many are the source. CREATE REACHABILITY INDEX makes one
(CREATE REACHABILITY INDEX). It keeps
its edges in a B-link tree and, in memory, of the memory budget,
labels of each vertex by pruned landmark labelling, so whether one
vertex reaches another is one merge of two lists. They are made as the
index is first read, kept exact as edges are added, and dropped when an
edge is taken back, as when a deleted row is collected, until
maintenance makes them again; meanwhile, and if the budget refuses
them, the index answers nothing, and searches are not pruned. A
GRAPH_TABLE reads it to drop the starts of a search that reach no end.
The planner never reads it for a WHERE clause. A column with a
collation is refused, and so are ends of different types.
The index is built from the rows there now, while other transactions go on reading and writing the table; it is then kept up to date with each change. It is made at once, apart from any transaction: no ROLLBACK undoes it.
An index's name is an identifier, not of a schema: no two indexes of the
database have one name. The options WITH sets are those the method
declares and those plug-ins declare for every index; of the built-in
methods, only fulltext, json, adjacency and reachability declare any. INFORMATION_SCHEMA.INDEXES lists the indexes, and
INDEX_OPTIONS their options.
A key part may be an expression in parentheses, of the table's columns
alone. Such an index is kept of each row's value of the expression, and
the planner reads it for a query's expression of the same form: one
written the same way, of the same columns and constants, so A + B
and not B + A. It is probed by =, a range, BETWEEN and IN of the
expression; a btree index gives ORDER BY of it its order, and so a
GROUP BY ordered by it, when the expression is never null, as a sum of
columns NOT NULL is. EXPLAIN shows the expression it probes, and
INDEXES the key part as written, in parentheses. An UPDATE that changes
no column the expression reads leaves its entry as it is.
An expression is a key part only if it can raise no error of any row
and gives the same value of the same row every time (RULES.md
D92): a column, a constant, a negation, a sum, difference or
product of exact numbers whose every value fits 18 digits, a CASE of
such, UPPER and LOWER of a character string, and JSON_VALUE,
JSON_QUERY and JSON_EXISTS of a character string or JSON column that
give null or a constant on an error, read no time zone (no datetime(),
time_tz() or timestamp_tz() in the path) and return no type with
one. A quotient, a CAST, a subquery, CURRENT_DATE, a sum that can
overflow, or ERROR ON ERROR is refused. So a lookup by the expression
skips no row whose error a scan would raise. Only btree and hash
take an expression. A column the expression reads cannot be renamed;
dropped, it drops the index with it.
An index takes no UNIQUE: a UNIQUE or PRIMARY KEY constraint makes an
index of its own, named for it, as ANN.STAFF.UNIQUE1.
An index on a temporary table is one of each session's rows (Temporary Tables): each session makes its entries, in memory, of its own rows, as its next statement begins. A session whose transaction had its rows of the table open then uses the index only once that transaction ends; until then, a statement that would read or write through it is refused (SQLSTATE TB911), and is run again after COMMIT or ROLLBACK.
An index of a partitioned table is a template (extension): an index of its method, options and key is made on each of its partitions, now and as each is made or attached, named by the template, the partition's schema and its name apart by a NUL character. DROP INDEX and ALTER INDEX of the template drop or alter each of them. A hybrid index of a MODEL keeps one table of chunks of the rows of every partition (Partitioned Tables).
RULES
- The session has no transaction open: COMMIT or ROLLBACK first.
- The table is a base table, not a view, and its user owns the table's schema.
- Each column is the table's, and each expression reads the table's columns alone, can raise no error and is deterministic.
- No index has the name.
- The method is registered; its name is matched in any case. On a
temporary table, it is
btree,hash,rtree,fulltext,json,adjacency,labelsorreachability. Of a key part that is an expression, it isbtreeorhash. - The columns are of types the method keys: of
xml_path,xml_value,xml_propertyandxml_primary, one column of an XML type; of the others, types whose values compare, so not of an XML type. - Each option is declared, given once, of a value it accepts.
PRIVILEGES
The table's schema is its user's.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 25000 | -917 | The session has a transaction open |
| 2A000 | -103 | A rule above is broken, as by an expression that can raise an error |
| 2A000 | -104 | Its user does not own the table's schema |
| 0A000 | -900 | rtree is asked to index an unsupported column count or type |
| 0A000 | -900 | A value of the table is past what an XML index keys |
| TB910 | -910 | An rtree coordinate is non-finite or a rectangle has reversed bounds |
| 0A000 | -900 | fulltext is asked to index other than one FULLTEXT column or one character string column with no collation |
| 0A000 | -900 | json is asked to index other than one character string column with no collation or one JSON column |
| 0A000 | -900 | adjacency is asked to index a column with a collation, or a nullable included column |
| 0A000 | -900 | adjacency options leave no source or no destination column |
| 0A000 | -900 | labels is asked to index a nullable column or an expression |
| 0A000 | -900 | reachability is asked to index ends of different column counts or types, a column with a collation or an expression |
EXAMPLES
Of a table STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30), DEPT CHAR(3)):
CREATE INDEX STAFF_DEPT ON STAFF (DEPT);
CREATE INDEX STAFF_NAME ON STAFF (NAME) USING HASH;
SELECT INDEX_NAME, METHOD_NAME, IS_UNIQUE, COLUMN_NAMES FROM INFORMATION_SCHEMA.INDEXES
WHERE TABLE_NAME = 'STAFF' ORDER BY INDEX_NAME;
OK
OK
INDEX_NAME | METHOD_NAME | IS_UNIQUE | COLUMN_NAMES
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
ANN.STAFF.UNIQUE1 | btree | YES | ID
STAFF_DEPT | btree | NO | DEPT
STAFF_NAME | hash | NO | NAME
(3 rows)
A dispatch map with numeric courier coordinates. Index X and Y in that order, then ask for couriers in the visible window:
COMMIT;
CREATE TABLE COURIERS (
ID INTEGER PRIMARY KEY,
NAME VARCHAR(12),
X DOUBLE PRECISION NOT NULL,
Y DOUBLE PRECISION NOT NULL
);
INSERT INTO COURIERS VALUES
(1, 'Amina', 100, 100), (2, 'Bo', 750, 250), (3, 'Chao', 1500, 200);
COMMIT;
CREATE INDEX COURIERS_XY ON COURIERS (X, Y) USING RTREE;
SELECT NAME FROM COURIERS
WHERE X BETWEEN 0 AND 1000 AND Y BETWEEN 0 AND 400
ORDER BY ID;
OK
OK
OK
OK
OK
NAME
--------------
Amina
Bo
(2 rows)
Use the same coordinates to find each courier's dispatch area. These areas are rectangles in the same local metre grid; a courier on their shared edge would match both:
SELECT C.NAME, A.AREA_NAME
FROM COURIERS C JOIN
(VALUES ('West', 0E0, 0E0, 1000E0, 400E0),
('East', 1000E0, 0E0, 2000E0, 400E0))
AS A (AREA_NAME, MIN_X, MIN_Y, MAX_X, MAX_Y)
ON C.X >= A.MIN_X AND C.X <= A.MAX_X
AND C.Y >= A.MIN_Y AND C.Y <= A.MAX_Y
ORDER BY C.ID, A.AREA_NAME;
NAME | AREA_NAME
--------------+-----------
Amina | West
Bo | West
Chao | East
(3 rows)
A courier history of ten thousand positions, indexed after it is loaded, so the index is packed whole, then most of it deleted, which gives the emptied nodes' space back:
COMMIT;
CREATE TABLE TRACKS (ID INTEGER PRIMARY KEY, X DOUBLE PRECISION NOT NULL, Y DOUBLE PRECISION NOT NULL);
INSERT INTO TRACKS
WITH RECURSIVE N (I, X, Y) AS (
VALUES (CAST(0 AS INTEGER), CAST(0 AS INTEGER), CAST(0 AS INTEGER))
UNION ALL SELECT I + 1, CASE WHEN Y = 99 THEN X + 1 ELSE X END, CASE WHEN Y = 99 THEN 0 ELSE Y + 1 END
FROM N WHERE I < 9999)
SELECT I, X, Y FROM N;
COMMIT;
CREATE INDEX TRACKS_XY ON TRACKS (X, Y) USING RTREE;
SELECT COUNT(*) AS SEEN FROM TRACKS WHERE X BETWEEN 10 AND 14 AND Y BETWEEN 0 AND 9;
DELETE FROM TRACKS WHERE X < 90;
COMMIT;
SELECT COUNT(*) AS SEEN FROM TRACKS WHERE X BETWEEN 88 AND 92 AND Y BETWEEN 0 AND 9;
OK
OK
OK
OK
OK
SEEN
----------------------
50
(1 row)
OK
OK
SEEN
----------------------
30
(1 row)
A GEOMETRY location is indexed by its one column, of a fixed SRID. A depot index finds the depots in a map window:
COMMIT;
CREATE TABLE PICKUP_DEPOTS (
ID INTEGER PRIMARY KEY,
NAME VARCHAR(12),
LOCATION GEOMETRY(0)
);
INSERT INTO PICKUP_DEPOTS VALUES
(1, 'Canal depot', ST_POINT(100, 700)),
(2, 'Market depot', ST_POINT(1000, 300));
COMMIT;
CREATE INDEX PICKUP_DEPOTS_LOCATION ON PICKUP_DEPOTS (LOCATION) USING RTREE;
SELECT NAME FROM PICKUP_DEPOTS
WHERE ST_INTERSECTS(LOCATION, ST_MAKEENVELOPE(0, 0, 500, 1000)) = 1
ORDER BY ID;
OK
OK
OK
OK
OK
NAME
--------------
Canal depot
(1 row)
The same index finds the depots near a point, and those near each stop, a stop near none kept with nulls:
SELECT NAME FROM PICKUP_DEPOTS WHERE ST_DISTANCE(LOCATION, ST_POINT(900, 300)) <= 150 ORDER BY ID;
CREATE TABLE STOPS (ID INTEGER PRIMARY KEY, SPOT GEOMETRY(0));
INSERT INTO STOPS VALUES (1, ST_POINT(120, 700)), (2, ST_POINT(500, 500));
SELECT S.ID, D.NAME FROM STOPS S LEFT JOIN PICKUP_DEPOTS D
ON ST_DISTANCE(S.SPOT, D.LOCATION) < 50 ORDER BY S.ID;
NAME
--------------
Market depot
(1 row)
OK
OK
ID | NAME
--------------+--------------
1 | Canal depot
2 | NULL
(2 rows)
The planner may still read so small a table whole, as it chooses by cost. The Spatial Guide has service-area joins and nearest-depot searches.
A full-text index of notes, its stop words its own:
CREATE TABLE NOTES (ID INTEGER PRIMARY KEY, BODY VARCHAR(200));
INSERT INTO NOTES VALUES (1, 'Call the depot about the late van'), (2, 'Van serviced, depot informed');
COMMIT;
CREATE INDEX NOTES_BODY ON NOTES (BODY) USING FULLTEXT WITH (FULLTEXT.STOP_WORDS = 'the, about');
SELECT ID FROM NOTES WHERE TEXT_MATCHES(BODY, 'depot van') ORDER BY ID;
OK
OK
OK
OK
ID
--------------
1
2
(2 rows)
An XML index of a catalogue's entries, and one refused of a column not of an XML type:
COMMIT;
CREATE TABLE CATALOGUE (ID INTEGER, ENTRY XML(DOCUMENT));
COMMIT;
CREATE INDEX CATALOGUE_PATHS ON CATALOGUE (ENTRY) USING XML_PATH;
CREATE INDEX CATALOGUE_IDS ON CATALOGUE (ID) USING XML_PATH;
SELECT INDEX_NAME, METHOD_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'CATALOGUE';
OK
OK
OK
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): index CATALOGUE_IDS of method xml_path keys one column, of an XML type (RULES.md D106)
INDEX_NAME | METHOD_NAME
--------------------------------------------------------------+--------------------------------------------------------------
CATALOGUE_PATHS | xml_path
(1 row)
Indexes refused, V a view:
COMMIT;
CREATE INDEX STAFF_X ON STAFF (NOPE);
CREATE INDEX STAFF_X ON STAFF (DEPT) USING NOPE;
CREATE INDEX STAFF_DEPT ON STAFF (DEPT);
CREATE INDEX STAFF_X ON V (ID);
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.STAFF has no column NOPE (at character 31)
turborel: SQLCODE -103 (SQLSTATE 2A000): there is no index method NOPE (at character 43)
turborel: SQLCODE -103 (SQLSTATE 2A000): an index named STAFF_DEPT already exists (at character 13)
turborel: SQLCODE -103 (SQLSTATE 2A000): ANN.V is not a base table (at character 24)
Indexes of expressions: a parcel's code in any case, its gross weight, and the city of its label, a JSON text, or null if the label is none:
COMMIT;
CREATE TABLE PARCELS (
ID INTEGER PRIMARY KEY,
CODE VARCHAR(12),
WEIGHT INTEGER NOT NULL,
PACKING INTEGER NOT NULL,
LABEL VARCHAR(200)
);
INSERT INTO PARCELS VALUES
(1, 'ab-1', 120, 5, '{"city":"Oslo"}'),
(2, 'AB-2', 80, 4, '{"city":"Bergen"}'),
(3, 'cd-3', 200, 10, 'not json');
COMMIT;
CREATE INDEX PARCELS_CODE ON PARCELS ((UPPER(CODE)));
CREATE INDEX PARCELS_GROSS ON PARCELS ((WEIGHT + PACKING), ID);
CREATE INDEX PARCELS_CITY ON PARCELS
((JSON_VALUE(LABEL, 'lax $.city' NULL ON ERROR))) USING HASH;
SELECT ID FROM PARCELS WHERE UPPER(CODE) = 'AB-2';
SELECT ID, WEIGHT + PACKING AS GROSS FROM PARCELS ORDER BY WEIGHT + PACKING;
SELECT ID FROM PARCELS WHERE JSON_VALUE(LABEL, 'lax $.city' NULL ON ERROR) = 'Oslo';
SELECT INDEX_NAME, COLUMN_NAMES FROM INFORMATION_SCHEMA.INDEXES
WHERE TABLE_NAME = 'PARCELS' AND IS_UNIQUE = 'NO' ORDER BY INDEX_NAME;
COMMIT;
OK
OK
OK
OK
OK
OK
OK
ID
--------------
2
(1 row)
ID | GROSS
--------------+----------------------
2 | 84
1 | 125
3 | 210
(3 rows)
ID
--------------
1
(1 row)
INDEX_NAME | COLUMN_NAMES
--------------------------------------------------------------+--------------------------------------------------------------
PARCELS_CITY | (JSON_VALUE(LABEL, 'lax $.city' NULL ON ERROR))
PARCELS_CODE | (UPPER(CODE))
PARCELS_GROSS | (WEIGHT + PACKING), ID
(3 rows)
OK
Expressions refused: a quotient raises an error of a row whose PACKING is 0, and ERROR ON ERROR one of a label that is no JSON text:
CREATE INDEX PARCELS_X ON PARCELS ((WEIGHT / PACKING));
CREATE INDEX PARCELS_X ON PARCELS ((JSON_VALUE(LABEL, 'lax $.city' ERROR ON ERROR)));
CREATE INDEX PARCELS_X ON PARCELS ((UPPER(CODE))) USING RTREE;
turborel: SQLCODE -103 (SQLSTATE 2A000): the key part (WEIGHT / PACKING) of index PARCELS_X: (WEIGHT / PACKING) can raise an error or is not deterministic, so it is no key part of an index (RULES.md D92) (at character 35)
turborel: SQLCODE -103 (SQLSTATE 2A000): the key part (JSON_VALUE(LABEL, 'lax $.city' ERROR ON ERROR)) of index PARCELS_X: JSON_VALUE 'lax $.city' RETURNING CHARACTER VARYING(32767) NULL ON EMPTY ERROR ON ERROR(LABEL) can raise an error or is not deterministic, so it is no key part of an index (RULES.md D92) (at character 35)
turborel: SQLCODE -103 (SQLSTATE 2A000): a rtree index has no key part that is an expression, only btree and hash do (RULES.md D92) (at character 35)
An adjacency index of the transfers between accounts, each transfer's amount included, its options shown, and an account's transfers out found by it; an index of one column has no destination, and is refused:
CREATE TABLE TRANSFERS (
ID INTEGER PRIMARY KEY,
PAYER INTEGER NOT NULL,
PAYEE INTEGER NOT NULL,
AMOUNT INTEGER NOT NULL
);
INSERT INTO TRANSFERS VALUES (1, 10, 20, 500), (2, 10, 30, 75), (3, 20, 30, 120), (4, 30, 10, 60);
COMMIT;
CREATE INDEX TRANSFERS_ADJ ON TRANSFERS (PAYER, PAYEE, AMOUNT) USING ADJACENCY
WITH (ADJACENCY.INCLUDE = 1);
CREATE INDEX TRANSFERS_X ON TRANSFERS (PAYER) USING ADJACENCY;
SELECT INDEX_NAME, SOURCE_COLUMNS, INCLUDED_COLUMNS, DIRECTION
FROM INFORMATION_SCHEMA.ADJACENCY_INDEXES;
SELECT PAYEE, AMOUNT FROM TRANSFERS WHERE PAYER = 10 ORDER BY PAYEE;
COMMIT;
OK
OK
OK
OK
turborel: SQLCODE -900 (SQLSTATE 0A000): the adjacency index method keys an edge by its source's columns, then its destination's, then those included: a key of 1 column(s), 1 of the source and 0 included, has no source or no destination
INDEX_NAME | SOURCE_COLUMNS | INCLUDED_COLUMNS | DIRECTION
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
TRANSFERS_ADJ | 1 | 1 | BOTH
(1 row)
PAYEE | AMOUNT
--------------+--------------
20 | 500
30 | 75
(2 rows)
OK
A session with a transaction open:
SELECT 1 FROM STAFF WHERE ID = 1;
CREATE INDEX STAFF_Y ON STAFF (DEPT);
-----
(0 rows)
turborel: SQLCODE -917 (SQLSTATE 25000): an index statement runs outside a transaction: commit or roll back first
STANDARD
An extension: neither SQL-86 nor SQL-92 has indexes.
SEE ALSO
ALTER INDEX, DROP INDEX, CREATE TABLE, CREATE LABEL INDEX, CREATE REACHABILITY INDEX, Full-Text Search, Spatial Data, XML indexes