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.
/* 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.
/* 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.
/* 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.
/* 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-35139-30552-47861-57461-03204-44110-27426-60914 tcl slt th3 src
Canonical usage: syntax/create-index-stmt.html
/* IMP: R-35139-30552 */
# EVIDENCE-OF: R-35139-30552 -- syntax diagram create-index-stmt
R-05949-14992-08035-18700-31063-00582-56557-23299 tcl slt th3 src
Canonical usage: syntax/expr.html
/* IMP: R-05949-14992 */
# EVIDENCE-OF: R-05949-14992 -- syntax diagram expr
R-40210-12599-36231-03864-46176-41928-03203-14839 tcl slt th3 src
Canonical usage: syntax/filter-clause.html
/* IMP: R-40210-12599 */
# EVIDENCE-OF: R-40210-12599 -- syntax diagram filter-clause
R-36911-54969-15236-22853-38940-32913-12354-42363 tcl slt th3 src
Canonical usage: syntax/literal-value.html
/* IMP: R-36911-54969 */
# EVIDENCE-OF: R-36911-54969 -- syntax diagram literal-value
R-39106-18215-63620-21950-01426-37390-34436-15307 tcl slt th3 src
Canonical usage: syntax/over-clause.html
/* IMP: R-39106-18215 */
# EVIDENCE-OF: R-39106-18215 -- syntax diagram over-clause
R-43427-44402-29451-17649-47289-60254-55729-32554 tcl slt th3 src
Canonical usage: syntax/frame-spec.html
/* IMP: R-43427-44402 */
# EVIDENCE-OF: R-43427-44402 -- syntax diagram frame-spec
R-27998-48590-15759-55327-24057-45544-61498-20485 tcl slt th3 src
Canonical usage: syntax/ordering-term.html
/* IMP: R-27998-48590 */
# EVIDENCE-OF: R-27998-48590 -- syntax diagram ordering-term
R-11045-52870-41853-22612-28711-29067-44165-34250 tcl slt th3 src
Canonical usage: syntax/raise-function.html
/* IMP: R-11045-52870 */
# EVIDENCE-OF: R-11045-52870 -- syntax diagram raise-function
R-07822-20600-60008-25390-21808-35959-11282-32675 tcl slt th3 src
Canonical usage: syntax/select-stmt.html
/* IMP: R-07822-20600 */
# EVIDENCE-OF: R-07822-20600 -- syntax diagram select-stmt
R-48339-53472-43967-43694-30651-22239-39991-26499 tcl slt th3 src
Canonical usage: syntax/common-table-expression.html
/* IMP: R-48339-53472 */
# EVIDENCE-OF: R-48339-53472 -- syntax diagram common-table-expression
R-34484-27817-31904-59996-06509-56853-54385-06908 tcl slt th3 src
Canonical usage: syntax/compound-operator.html
/* IMP: R-34484-27817 */
# EVIDENCE-OF: R-34484-27817 -- syntax diagram compound-operator
R-62819-25014-14742-19526-07437-11316-46744-34017 tcl slt th3 src
Canonical usage: syntax/join-clause.html
/* IMP: R-62819-25014 */
# EVIDENCE-OF: R-62819-25014 -- syntax diagram join-clause
R-22251-01544-43178-13583-65163-47242-05139-28044 tcl slt th3 src
Canonical usage: syntax/join-constraint.html
/* IMP: R-22251-01544 */
# EVIDENCE-OF: R-22251-01544 -- syntax diagram join-constraint
R-37592-60550-55049-61415-11391-65005-23058-12335 tcl slt th3 src
Canonical usage: syntax/join-operator.html
/* IMP: R-37592-60550 */
# EVIDENCE-OF: R-37592-60550 -- syntax diagram join-operator
R-27998-48590-15759-55327-24057-45544-61498-20485 tcl slt th3 src
Canonical usage: syntax/ordering-term.html
/* IMP: R-27998-48590 */
# EVIDENCE-OF: R-27998-48590 -- syntax diagram ordering-term
R-55434-02683-06673-33913-45638-17712-08878-40551 tcl slt th3 src
Canonical usage: syntax/result-column.html
/* IMP: R-55434-02683 */
# EVIDENCE-OF: R-55434-02683 -- syntax diagram result-column
R-48195-34360-11192-44367-16579-00892-09818-30155 tcl slt th3 src
Canonical usage: syntax/table-or-subquery.html
/* IMP: R-48195-34360 */
# EVIDENCE-OF: R-48195-34360 -- syntax diagram table-or-subquery
R-48345-61330-56074-37373-15233-53014-54747-65452 tcl slt th3 src
Canonical usage: syntax/window-defn.html
/* IMP: R-48345-61330 */
# EVIDENCE-OF: R-48345-61330 -- syntax diagram window-defn
R-43427-44402-29451-17649-47289-60254-55729-32554 tcl slt th3 src
Canonical usage: syntax/frame-spec.html
/* IMP: R-43427-44402 */
# EVIDENCE-OF: R-43427-44402 -- syntax diagram frame-spec
R-62486-51739-58335-52148-04934-35692-56858-32635 tcl slt th3 src
Canonical usage: syntax/type-name.html
/* IMP: R-62486-51739 */
# EVIDENCE-OF: R-62486-51739 -- syntax diagram type-name
R-11175-37461-37361-55818-19791-03595-13461-03099 tcl slt th3 src
Canonical usage: syntax/signed-number.html
/* IMP: R-11175-37461 */
# EVIDENCE-OF: R-11175-37461 -- syntax diagram signed-number
R-07519-27231-14174-14468-02729-55022-65176-33366 tcl slt th3 src
Canonical usage: syntax/indexed-column.html
/* IMP: R-07519-27231 */
# EVIDENCE-OF: R-07519-27231 -- 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.
/* 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.
/* 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-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.
/* 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.
/* 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.
/* 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;
/* 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;
/* 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.
/* 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.
/* 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.
/* 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 );
/* 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;
/* 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;
/* 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.
/* 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".
/* 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.
/* 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.
/* 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.
/* 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.
/* 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
/* 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
/* 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