Documentation Source Text

Check-in [3e022afe53]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Corrections to partial index documentation.
Timelines: family | ancestors | descendants | both | partial-indices
Files: files | file ages | folders
SHA1: 3e022afe533234604852e79c1f7c7ca974450cd4
User & Date: drh 2013-08-02 18:27:01
Context
2013-08-02
18:27
Enable partial indices. check-in: f990657458 user: drh tags: trunk
18:27
Corrections to partial index documentation. Closed-Leaf check-in: 3e022afe53 user: drh tags: partial-indices
03:43
Add documentation on the partial index implementation. check-in: 5723efb011 user: drh tags: partial-indices
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to matrix.tcl.

   600    600       set req [string map {&lt; < &gt; > &#91; [ &#93; ] &amp; &} $req]
   601    601       set req [string trim $req]
   602    602       set rno R-[md5-10x8 $req]
   603    603       set shortrno [string range $rno 0 12]
   604    604       append out "<a name=\"$rno\"></a><font color=\"blue\"><b>\n"
   605    605       set link "<a href=\"$matrixpath#$rno\" style=\"color: #0000ff\">"
   606    606       append out "$link$shortrno</a>:\[</b></font>"
   607         -    if {$proof($rno)>=2} {
          607  +    if {![info exists proof($rno)]} {
          608  +      set clr red
          609  +    } elseif {$proof($rno)>=2} {
   608    610         set clr green
   609    611       } elseif {$proof($rno)==1} {
   610    612         set clr orange
   611    613       } else {
   612    614         set clr red
   613    615       }
   614    616       append out "<font color=\"$clr\">$orig</font>\n"

Changes to pages/partialindex.in.

     7      7   <h2>1.0 Introduction</h2>
     8      8   
     9      9   <p>
    10     10   A partial index is an index over a subset of the rows of a table.
    11     11   </p>
    12     12   
    13     13   <p>
    14         -In ordinary indexes, there is exactly one entry in the index for every
    15         -row in the table.  In partial index, only some subset of the rows in the
    16         -table have corresponding index entries.  For example, a partial index might
           14  +^In ordinary indexes, there is exactly one entry in the index for every
           15  +row in the table.  ^In partial index, only some subset of the rows in the
           16  +table have corresponding index entries.  ^For example, a partial index might
    17     17   omit entries for which the column being indexed is NULL.  When used 
    18     18   judiciously, partial indexes can result in smaller database files and
    19     19   improvements in both query and write performance.
    20     20   </p>
    21     21   
    22     22   <h2>2.0 Creating Partial Indexes</h2>
    23     23   
    24     24   <p>
    25         -Create a partial index by adding a WHERE clause to the end of an 
           25  +^Create a partial index by adding a WHERE clause to the end of an 
    26     26   ordinary [CREATE INDEX] statement.
    27     27   </p>
    28     28   
    29     29   <tcl>BubbleDiagram create-index-stmt</tcl>
    30     30   
    31     31   <p>
    32     32   Any index that includes the WHERE clause at the end is considered to be
    33     33   a partial index.  Indexes that omit the WHERE clause (or indexes that
    34     34   are created by UNIQUE or PRIMARY KEY constraints inside of CREATE TABLE
    35     35   statements) are ordinary full indexes.
    36     36   </p>
    37     37   
    38     38   <p>
    39         -The expression following the WHERE clause may contain operators,
           39  +^The expression following the WHERE clause may contain operators,
    40     40   literal values, and names of columns in the table being indexed.
    41         -The WHERE clause may <em>not</em> contains subqueries, references to other
           41  +^The WHERE clause may <em>not</em> contains subqueries, references to other
    42     42   tables, functions, or [bound parameters].  The LIKE, GLOB, MATCH,
    43     43   and REGEXP operators in SQLite as functions by the same name.
    44         -Since functions are prohibited in the 
           44  +^Since functions are prohibited in the 
    45     45   WHERE clause of a CREATE INDEX statement, so too are the LIKE, GLOB,
    46     46   MATCH, and REGEXP operators.</p>
    47     47   
    48     48   <p>
    49         -Only rows of the table for which the WHERE clause evaluates to true
    50         -are included in the index.  If the WHERE clause expression evaluates 
           49  +^Only rows of the table for which the WHERE clause evaluates to true
           50  +are included in the index.  ^If the WHERE clause expression evaluates 
    51     51   to NULL or to false for some row of the table, then those rows are omitted 
    52     52   from the index.
    53     53   </p>
    54     54   
    55     55   <p>
    56         -The columns referenced in the WHERE clause of a partial index can be
           56  +^The columns referenced in the WHERE clause of a partial index can be
    57     57   any of the columns in the table, not just columns that happen to be
    58     58   indexed.  However, it is very common for the WHERE clause
    59     59   expression of a partial index to be a simple expression on the column
    60     60   being indexed.  The following is a typical example:</p>
    61     61   
    62     62   <blockquote>
    63         -CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;
           63  +^(CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL;)^
    64     64   </blockquote>
    65     65   
    66     66   <p>In the example above, if most purchase orders do not have a "parent"
    67     67   purchase order, then most parent_po values will be NULL.  That means only
    68     68   a small subset of the rows in the purchaseorder table will be indexed.
    69     69   Hence the index will take up much less space.  And changes to the original
    70     70   purchaseorder table will run faster since the po_parent index only needs
    71     71   to be updated for those exceptional rows where parent_po is not NULL.
    72         -But the index is still useful for querying.  In particular, if one wants
           72  +But the index is still useful for querying.  ^(In particular, if one wants
    73     73   to know all "children" of a particular purchase order "?1", the query
    74     74   would be:
    75     75   
    76     76   <blockquote>
    77     77   SELECT po_num FROM purchaseorder WHERE parent_po=?1;
    78         -</blockquote>
           78  +</blockquote>)^
    79     79   
    80         -<p>The query above will use the po_parent index to help find the answer,
           80  +<p>^The query above will use the po_parent index to help find the answer,
    81     81   since the po_parent index contains entries for all rows of interest.
    82     82   Note that since po_parent is smaller than a full index, the query will
    83     83   likely run faster too.</p>
    84     84   
    85     85   <h3>2.1 Unique Partial Indexes</h3>
    86     86   
    87         -<p>A partial index definition may include the UNIQUE keyword.  If it
           87  +<p>^A partial index definition may include the UNIQUE keyword.  ^If it
    88     88   does, then SQLite requires every entry <em>in the index</em> to be unique.
    89     89   This provides a mechanism for enforcing uniqueness across some subset of
    90     90   the rows in a table.</p>
    91     91   
    92     92   <p>For example, suppose you have a database of the members of a large
    93     93   organization where each person is assigned to a particular "team".  
    94         -Each team has a "leader" who is also a member of that team.  The
           94  +Each team has a "leader" who is also a member of that team.  ^(The
    95     95   table might look something like this:</p>
    96     96   
    97     97   <blockquote><pre>
    98     98   CREATE TABLE person(
    99     99     person_id       INTEGER PRIMARY KEY,
   100    100     team_id         INTEGER REFERENCES team,
   101    101     is_team_leader  BOOLEAN,
   102         -  -- other fields omitted
          102  +  -- other fields elided
   103    103   );
   104         -</pre></blockquote>
          104  +</pre></blockquote>)^
   105    105   
   106    106   <p>The team_id field cannot be unique because there usually multiple people
   107    107   on the same time.  One cannot make the combination of team_id and is_team_leader
   108         -unique since there are usually multiple non-leaders on each team.  The
          108  +unique since there are usually multiple non-leaders on each team.  ^(The
   109    109   solution to enforcing one leader per team is to create a unique index
   110    110   on team_id but restricted to those entries for which is_team_leader is
   111    111   true:</p>
   112    112   
   113    113   <blockquote>
   114         -CREATE INDEX team_leader ON person(team_id) WHERE is_team_leader;
   115         -</blockquote>
          114  +CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader;
          115  +</blockquote>)^
   116    116   
   117         -<p>Coincidentally, that same index is useful for locating the team leader
          117  +^(<p>Coincidentally, that same index is useful for locating the team leader
   118    118   of a particular time:</p>
   119    119   
   120    120   <blockquote>
   121    121   SELECT person_id FROM person WHERE is_team_leader AND team_id=?1;
   122         -</blockquote>
          122  +</blockquote>)^
   123    123   
   124    124   <h2>3.0 Queries Using Partial Indexes</h2>
   125    125   
   126    126   <p>Let X be the expression in the WHERE claues of a partial
   127    127   index, and let W be the WHERE clause of a query that uses the
   128    128   table that is indexed.  Then, the query is permitted to use 
   129    129   the partial index if W&#x21d2;X, where the &#x21d2; operator
................................................................................
   140    140   are these:
   141    141   
   142    142   <ol>
   143    143   <li><p>If W is AND-connected terms and X is
   144    144   OR-connected terms and if any term of W
   145    145   appears as a term of X, then the partial index is usable.</p>
   146    146   
   147         -<p>For example, let the index be
          147  +<p>^(For example, let the index be
   148    148   <blockquote>
   149    149   CREATE INDEX ex1 ON tab1(a,b) WHERE a=5 OR b=6;
   150    150   </blockquote>
   151    151   <p>And let the query be:
   152    152   <blockquote>
   153    153   SELECT * FROM tab1 WHERE b=6 AND a=7; <i>-- uses partial index</i>
   154    154   </blockquote>
   155    155   <p>Then the index is usable by the query because the "b=6" term appears
   156         -in both the index definition and in the query.  Remember: terms in the
          156  +in both the index definition and in the query.)^  Remember: terms in the
   157    157   index should be OR-connected and terms in the query should be AND-connected.</p>
   158    158   
   159    159   <p>The terms in W and X must match exactly.  SQLite does not
   160    160   do algebra to try to get them to look the same.
   161         -The term "b=6" does not match "b=3+3" or "b-6=0" or "b BETWEEN 6 AND 6".
   162         -"b=6" will match to "6=b" as long as "b=6" is on the index and "6=b" is
   163         -in the query.  If a term of the form "6=b" appears in the index, it will
          161  +^The term "b=6" does not match "b=3+3" or "b-6=0" or "b BETWEEN 6 AND 6".
          162  +^"b=6" will match to "6=b" as long as "b=6" is on the index and "6=b" is
          163  +in the query.  ^If a term of the form "6=b" appears in the index, it will
   164    164   never match anything.</p>
   165    165   
   166         -<li><p>If a term in X is of the form "z IS NOT NULL" and if a term in
          166  +<li><p>^If a term in X is of the form "z IS NOT NULL" and if a term in
   167    167          W is a comparison operator on "z" other than "IS", then those
   168    168          terms match.</p>
   169    169   
   170         -<p>Example:  Let the index by
          170  +^(<p>Example:  Let the index by
   171    171   <blockquote>
   172    172   CREATE INDEX ex2 ON tab2(b,c) WHERE c IS NOT NULL;
   173    173   </blockquote>
   174    174   <p>Then any query that uses operators =, &lt;, &gt;, &lt;=, &gt;=, &lt;&gt;,
   175    175   or IN on column "c" would be usable with the partial index because those
   176         -comparison operators are only true if "c" is not NULL.  So the following
          176  +comparison operators are only true if "c" is not NULL.)^  ^(So the following
   177    177   query could use the partial index:
   178    178   <blockquote>
   179         -SELECT * FROM tab2 WHERE b=456 AND c<>0;  <i>-- uses partial index</i>
   180         -</blockquote>
   181         -<p>But the next query can not use the partial index:
          179  +SELECT * FROM tab2 WHERE b=456 AND c&lt;&gt;0;  <i>-- uses partial index</i>
          180  +</blockquote>)^
          181  +^(<p>But the next query can not use the partial index:
   182    182   <blockquote>
   183    183   SELECT * FROM tab2 WHERE b=456;  <i>-- cannot use partial index</i>
   184         -</blockquote>
          184  +</blockquote>)^
   185    185   <p>The latter query can not use the partial index because there might be
   186    186   rows in the table with b=456 and and where c is NULL.  But those rows would
   187    187   not be in the partial index.
   188    188   </ol>
   189    189   
   190    190   <p>These two rules describe how the query planner for SQLite works as of
   191    191   this writing (2013-08-01).  And the rules above will always be honored.