Choose any three.
SQLite Requirement Matrix Details
partialindex.html
Index Summary Markup Original
R-17840-50075-33076-39937-50165-09009-15721-26614 tcl slt th3 src
In ordinary indexes, there is exactly one entry in the index for every row in the table.
th3/cov1/index01.test:34
/* IMP: R-17840-50075 */
# EVIDENCE-OF: R-17840-50075 In ordinary indexes, there is exactly one # entry in the index for every row in the table.
R-38062-30057-54968-60320-51568-47118-43997-50197 tcl slt th3 src
In partial indexes, only some subset of the rows in the table have corresponding index entries.
th3/cov1/index01.test:50
/* IMP: R-38062-30057 */
# EVIDENCE-OF: R-38062-30057 In partial indexes, only some subset of the # rows in the table have corresponding index entries.
R-24086-08530-21252-40131-41317-48771-53823-31992 tcl slt th3 src
For example, a partial index might omit entries for which the column being indexed is NULL.
th3/cov1/index01.test:66
/* IMP: R-24086-08530 */
# EVIDENCE-OF: R-24086-08530 For example, a partial index might omit # entries for which the column being indexed is NULL.
R-41964-12887-53267-54582-29615-62168-19001-17157 tcl slt th3 src
Create a partial index by adding a WHERE clause to the end of an ordinary CREATE INDEX statement.
th3/cov1/index01.test:10
/* IMP: R-41964-12887 */
# EVIDENCE-OF: R-41964-12887 Create a partial index by adding a WHERE # clause to the end of an ordinary CREATE INDEX statement.
R-63484-12505-31722-29488-20808-43731-19220-61523 tcl slt th3 src
Canonical usage: syntax/create-index-stmt.html
th3/req1/createidx01.test:10
/* IMP: R-63484-12505 */
# EVIDENCE-OF: R-63484-12505 -- syntax diagram create-index-stmt
R-61970-26480-33028-36412-61944-06610-41249-40014 tcl slt th3 src
Canonical usage: syntax/expr.html
/* IMP: R-61970-26480 */
# EVIDENCE-OF: R-61970-26480 -- syntax diagram expr
R-47443-11280-39343-26300-13209-50193-13307-46522 tcl slt th3 src
Canonical usage: syntax/literal-value.html
/* IMP: R-47443-11280 */
# EVIDENCE-OF: R-47443-11280 -- syntax diagram literal-value
R-54197-31877-57350-43471-52348-15226-37714-44266 tcl slt th3 src
Canonical usage: syntax/raise-function.html
/* IMP: R-54197-31877 */
# EVIDENCE-OF: R-54197-31877 -- syntax diagram raise-function
R-19713-52738-52485-51770-14412-64430-18771-01641 tcl slt th3 src
Canonical usage: syntax/select-stmt.html
/* IMP: R-19713-52738 */
# EVIDENCE-OF: R-19713-52738 -- syntax diagram select-stmt
R-29190-16177-49676-56162-41955-57961-52271-56322 tcl slt th3 src
Canonical usage: syntax/common-table-expression.html
/* IMP: R-29190-16177 */
# EVIDENCE-OF: R-29190-16177 -- syntax diagram common-table-expression
R-60687-03927-28728-46307-01187-04245-57381-09290 tcl slt th3 src
Canonical usage: syntax/compound-operator.html
/* IMP: R-60687-03927 */
# EVIDENCE-OF: R-60687-03927 -- syntax diagram compound-operator
R-15218-64377-10732-10708-39996-49168-39639-07301 tcl slt th3 src
Canonical usage: syntax/join-clause.html
/* IMP: R-15218-64377 */
# EVIDENCE-OF: R-15218-64377 -- syntax diagram join-clause
R-59355-25880-50978-14585-20539-54763-44134-61379 tcl slt th3 src
Canonical usage: syntax/join-constraint.html
/* IMP: R-59355-25880 */
# EVIDENCE-OF: R-59355-25880 -- syntax diagram join-constraint
R-24999-30014-65347-53781-01089-16331-37334-22917 tcl slt th3 src
Canonical usage: syntax/join-operator.html
/* IMP: R-24999-30014 */
# EVIDENCE-OF: R-24999-30014 -- syntax diagram join-operator
R-54382-61764-64572-42659-14322-10734-05041-01494 tcl slt th3 src
Canonical usage: syntax/ordering-term.html
/* IMP: R-54382-61764 */
# EVIDENCE-OF: R-54382-61764 -- syntax diagram ordering-term
R-35473-09323-51070-49381-06532-56561-01297-03559 tcl slt th3 src
Canonical usage: syntax/result-column.html
/* IMP: R-35473-09323 */
# EVIDENCE-OF: R-35473-09323 -- syntax diagram result-column
R-45000-47812-33792-18048-40996-50849-16831-13312 tcl slt th3 src
Canonical usage: syntax/table-or-subquery.html
/* IMP: R-45000-47812 */
# EVIDENCE-OF: R-45000-47812 -- syntax diagram table-or-subquery
R-27503-48393-50904-62536-02444-59493-56551-12481 tcl slt th3 src
Canonical usage: syntax/type-name.html
/* IMP: R-27503-48393 */
# EVIDENCE-OF: R-27503-48393 -- syntax diagram type-name
R-16446-38486-52659-45236-06548-25582-51186-33834 tcl slt th3 src
Canonical usage: syntax/signed-number.html
/* IMP: R-16446-38486 */
# EVIDENCE-OF: R-16446-38486 -- syntax diagram signed-number
R-65458-30393-46621-54017-31997-42172-64963-41590 tcl slt th3 src
Canonical usage: syntax/indexed-column.html
th3/req1/createidx01.test:29
/* IMP: R-65458-30393 */
# EVIDENCE-OF: R-65458-30393 -- syntax diagram indexed-column
R-54611-06855-62690-60121-09688-57757-21462-60136 tcl slt th3 src
The expression following the WHERE clause may contain operators, literal values, and names of columns in the table being indexed.
th3/cov1/index01.test:78
/* IMP: R-54611-06855 */
# EVIDENCE-OF: R-54611-06855 The expression following the WHERE clause # may contain operators, literal values, and names of columns in the # table being indexed.
R-65161-60989-13939-65119-57772-34659-43488-10354 tcl slt th3 src
The WHERE clause may not contain subqueries, references to other tables, non-deterministic functions, or bound parameters.
th3/cov1/index01.test:195
/* IMP: R-65161-60989 */
# EVIDENCE-OF: R-65161-60989 The WHERE clause may not contain # subqueries, references to other tables, non-deterministic functions, # or bound parameters.
R-33488-11573-60565-11312-47099-14861-16674-34821 tcl slt th3 src
Since functions are prohibited in the WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB, MATCH, and REGEXP operators.
th3/cov1/index01.test:245
/* IMP: R-33488-11573 */
# EVIDENCE-OF: R-33488-11573 Since functions are prohibited in the WHERE # clause of a CREATE INDEX statement, so too are the LIKE, GLOB, MATCH, # and REGEXP operators.
R-59958-37183-05497-01446-42261-31297-62677-47395 tcl slt th3 src
Only rows of the table for which the WHERE clause evaluates to true are included in the index.
th3/cov1/index01.test:82
/* IMP: R-59958-37183 */
# EVIDENCE-OF: R-59958-37183 Only rows of the table for which the WHERE # clause evaluates to true are included in the index.
R-36357-08369-42604-06511-16723-17506-10955-34539 tcl slt th3 src
If the WHERE clause expression evaluates to NULL or to false for some rows of the table, then those rows are omitted from the index.
th3/cov1/index01.test:85
/* IMP: R-36357-08369 */
# EVIDENCE-OF: R-36357-08369 If the WHERE clause expression evaluates to # NULL or to false for some rows of the table, then those rows are # omitted from the index.
R-41796-02552-46731-27208-23017-23175-41935-19333 tcl slt th3 src
The columns referenced in the WHERE clause of a partial index can be any of the columns in the table, not just columns that happen to be indexed.
th3/cov1/index01.test:273
/* IMP: R-41796-02552 */
# EVIDENCE-OF: R-41796-02552 The columns referenced in the WHERE clause # of a partial index can be any of the columns in the table, not just # columns that happen to be indexed.
R-27290-56511-04892-59250-62806-15127-45739-19284 tcl slt th3 src
CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;
th3/cov1/index01.test:320
/* IMP: R-27290-56511 */
# EVIDENCE-OF: R-27290-56511 CREATE INDEX po_parent ON # purchaseorder(parent_po) WHERE parent_po IS NOT NULL;
R-05906-34301-40279-37243-33018-07128-06632-24411 tcl slt th3 src
In particular, if one wants to know all "children" of a particular purchase order "?1", the query would be:
SELECT po_num FROM purchaseorder WHERE parent_po=?1;
th3/cov1/index01.test:323
/* IMP: R-05906-34301 */
# EVIDENCE-OF: R-05906-34301 In particular, if one wants to know all # "children" of a particular purchase order "?1", the query would be: # SELECT po_num FROM purchaseorder WHERE parent_po=?1;
R-39082-45490-57705-20308-15536-26647-22917-12593 tcl slt th3 src
The query above will use the po_parent index to help find the answer, since the po_parent index contains entries for all rows of interest.
th3/cov1/index01.test:327
/* IMP: R-39082-45490 */
# EVIDENCE-OF: R-39082-45490 The query above will use the po_parent # index to help find the answer, since the po_parent index contains # entries for all rows of interest.
R-46455-06721-25633-38567-39133-09580-16982-03702 tcl slt th3 src
A partial index definition may include the UNIQUE keyword.
th3/cov1/index01.test:366
/* IMP: R-46455-06721 */
# EVIDENCE-OF: R-46455-06721 A partial index definition may include the # UNIQUE keyword.
R-45667-58607-22220-28108-32382-15512-20537-62688 tcl slt th3 src
If it does, then SQLite requires every entry in the index to be unique.
th3/cov1/index01.test:369
/* IMP: R-45667-58607 */
# EVIDENCE-OF: R-45667-58607 If it does, then SQLite requires every # entry in the index to be unique.
R-07154-34181-57164-55597-27085-41955-25905-44172 tcl slt th3 src
The table might look something like this:
CREATE TABLE person( person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, -- other fields elided );
th3/cov1/index01.test:372
/* IMP: R-07154-34181 */
# EVIDENCE-OF: R-07154-34181 The table might look something like this: # CREATE TABLE person( person_id INTEGER PRIMARY KEY, team_id INTEGER # REFERENCES team, is_team_leader BOOLEAN, -- other fields elided );
R-15250-32031-29902-43107-55645-15949-41885-09210 tcl slt th3 src
The solution to enforcing one leader per team is to create a unique index on team_id but restricted to those entries for which is_team_leader is true:
CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
th3/cov1/index01.test:376
/* IMP: R-15250-32031 */
# EVIDENCE-OF: R-15250-32031 The solution to enforcing one leader per # team is to create a unique index on team_id but restricted to those # entries for which is_team_leader is true: CREATE UNIQUE INDEX # team_leader ON person(team_id) WHERE is_team_leader;
R-62671-45936-15699-15939-47274-39312-22685-45097 tcl slt th3 src
Coincidentally, that same index is useful for locating the team leader of a particular team:
SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
th3/cov1/index01.test:413
/* IMP: R-62671-45936 */
# EVIDENCE-OF: R-62671-45936 Coincidentally, that same index is useful # for locating the team leader of a particular team: SELECT person_id # FROM person WHERE is_team_leader AND team_id=?1;
R-18088-57186-13089-35997-22868-17352-00310-65503 tcl slt th3 src
For example, let the index be
CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;
And let the query be:
SELECT * FROM tab1 WHERE b=6 AND a=7; -- uses partial index
Then the index is usable by the query because the "b=6" term appears in both the index definition and in the query.
th3/cov1/index01.test:425
/* IMP: R-18088-57186 */
# EVIDENCE-OF: R-18088-57186 For example, let the index be CREATE INDEX # ex1 ON tab1(a,b) WHERE a=5 OR b=6; And let the query be: SELECT * FROM # tab1 WHERE b=6 AND a=7; -- uses partial index Then the index is usable # by the query because the "b=6" term appears in both the index # definition and in the query.
R-25216-03908-32391-06837-09807-59608-58830-19572 tcl slt th3 src
The term "b=6" does not match "b=3+3" or "b-6=0" or "b BETWEEN 6 AND 6".
th3/cov1/index01.test:452
/* IMP: R-25216-03908 */
# EVIDENCE-OF: R-25216-03908 The term "b=6" does not match "b=3+3" or # "b-6=0" or "b BETWEEN 6 AND 6".
R-39210-36906-47474-04238-18863-57364-16133-33806 tcl slt th3 src
"b=6" will match to "6=b" as long as "b=6" is on the index and "6=b" is in the query.
th3/cov1/index01.test:477
/* IMP: R-39210-36906 */
# EVIDENCE-OF: R-39210-36906 "b=6" will match to "6=b" as long as "b=6" # is on the index and "6=b" is in the query.
R-43696-56835-52509-34515-30380-28351-43291-45019 tcl slt th3 src
If a term of the form "6=b" appears in the index, it will never match anything.
th3/cov1/index01.test:489
/* IMP: R-43696-56835 */
# EVIDENCE-OF: R-43696-56835 If a term of the form "6=b" appears in the # index, it will never match anything.
R-21637-36677-40497-35509-61399-46252-57328-58576 tcl slt th3 src
If a term in X is of the form "z IS NOT NULL" and if a term in W is a comparison operator on "z" other than "IS", then those terms match.
th3/cov1/index01.test:502
/* IMP: R-21637-36677 */
# EVIDENCE-OF: R-21637-36677 If a term in X is of the form "z IS NOT # NULL" and if a term in W is a comparison operator on "z" other than # "IS", then those terms match.
R-21827-60920-49018-16791-65129-33759-13224-63617 tcl slt th3 src
Example: Let the index be
CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;
Then any query that uses operators =, <, >, <=, >=, <>, IN, LIKE, or GLOB on column "c" would be usable with the partial index because those comparison operators are only true if "c" is not NULL.
th3/cov1/index01.test:506 th3/cov1/index01.test:647
/* IMP: R-21827-60920 */
# EVIDENCE-OF: R-21827-60920 Example: Let the index be CREATE INDEX ex2 # ON tab2(b,c) WHERE c IS NOT NULL; Then any query that uses operators # =, <, >, <=, >=, <>, IN, LIKE, or GLOB on column "c" # would be usable with the partial index because those comparison # operators are only true if "c" is not NULL.
R-05254-00658-23742-34136-13538-61949-20472-35341 tcl slt th3 src
So the following query could use the partial index:
SELECT * FROM tab2 WHERE b=456 AND c<>0; -- uses partial index
th3/cov1/index01.test:514
/* IMP: R-05254-00658 */
# EVIDENCE-OF: R-05254-00658 So the following query could use the # partial index: SELECT * FROM tab2 WHERE b=456 AND c<>0; -- uses # partial index
R-36612-64789-14111-46629-37131-49911-14128-04823 tcl slt th3 src
But the next query can not use the partial index:
SELECT * FROM tab2 WHERE b=456; -- cannot use partial index
th3/cov1/index01.test:594
/* IMP: R-36612-64789 */
# EVIDENCE-OF: R-36612-64789 But the next query can not use the partial # index: SELECT * FROM tab2 WHERE b=456; -- cannot use partial index