Documentation Source Text

Check-in [4b78e06344]
Login

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

Overview
Comment:Update documentation for SQLITE_ENABLE_STAT3.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4b78e063442a018e9169800abea87c98698ef894
User & Date: drh 2011-10-07 23:26:17
Context
2011-10-11
12:36
Fix requirements marks on ANALYZE documentation. check-in: 5eb8cc7380 user: drh tags: trunk
2011-10-07
23:26
Update documentation for SQLITE_ENABLE_STAT3. check-in: 4b78e06344 user: drh tags: trunk
2011-09-30
14:43
Fix the maximum database size in the limits.html document. check-in: 1579da9239 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/compile.in.

447
448
449
450
451
452
453








454
455
456
457
458
459
460
461
462
463
464
465
466
467

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}

COMPILE_OPTION {SQLITE_ENABLE_STAT2} {








  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  a 10-sample histogram of the data in each index and store that histogram
  in the <b>sqlite_stat2</b> table.  The query planner will then use the
  histogram data to help it estimate how many rows will be selected by a
  <a href="optoverview.html#rangequery">range constraint</a> in a WHERE clause.
}

COMPILE_OPTION {SQLITE_ENABLE_UPDATE_DELETE_LIMIT} {
  This option enables an optional ORDER BY and LIMIT clause on 
  [UPDATE] and [DELETE] statements.

  <p>If this option is defined, then it must also be 







>
>
>
>
>
>
>
>



|
|
|
<







447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467

468
469
470
471
472
473
474

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}

COMPILE_OPTION {SQLITE_ENABLE_STAT2} {
  This option used to cause the [ANALYZE] command to collect
  index histogram data in the <b>sqlite_stat2</b> table.  But that
  functionality was superceded by [SQLITE_ENABLE_STAT3] as of
  SQLite version 3.7.9.  The SQLITE_ENABLE_STAT2 compile-time option
  is now a no-op.
}

COMPILE_OPTION {SQLITE_ENABLE_STAT3} {
  This option adds additional logic to the [ANALYZE] command and to
  the [query planner] that can help SQLite to chose a better query plan
  under certain situations.  The [ANALYZE] command is enhanced to collect
  histogram data from each index and store that data
  in the <b>sqlite_stat3</b> table.  The query planner will then use the
  histogram data to help it make better index choices.

}

COMPILE_OPTION {SQLITE_ENABLE_UPDATE_DELETE_LIMIT} {
  This option enables an optional ORDER BY and LIMIT clause on 
  [UPDATE] and [DELETE] statements.

  <p>If this option is defined, then it must also be 

Changes to pages/lang.in.

197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214


215
216
217
218
219
220
221
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT2] option, then additional histogram data is
collected and stored in <b>sqlite_stat2</b>.
Future enhancements may create
additional tables with the same name pattern except with the "1"
or "2" changed to a different digit.</p>

<p> ^The [DROP TABLE] and [ALTER TABLE] commands do
not work on the <b>sqlite_stat1</b> or <b>sqlite_stat2</b> tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.


Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as







|
|


|

|
|



>
>







197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
and indices in that one database are analyzed.  
^If the argument is a table name, then only that table and the
indices associated with that table are analyzed.  ^If the argument
is an index name, then only that one index is analyzed.</p>

<p> ^The default implementation stores all statistics in a single
table named <b>sqlite_stat1</b>.  ^If SQLite is compiled with the
[SQLITE_ENABLE_STAT3] option, then additional histogram data is
collected and stored in <b>sqlite_stat3</b>.
Future enhancements may create
additional tables with the same name pattern except with the "1"
or "3" changed to larger digits.</p>

<p> ^The [ALTER TABLE] command does
not work on the <b>sqlite_stat1</b> or <b>sqlite_stat3</b> tables,
but all the content of those tables can be queried using [SELECT]
and can be deleted, augmented, or modified using the [DELETE],
[INSERT], and [UPDATE] commands.
^The [DROP TABLE] command does work on <b>sqlite_stat1</b> and
<b>sqlite_stat3</b> as of SQLite version 3.7.9.
Appropriate care should be used when changing the content of the statistics
tables as invalid content can cause SQLite to select inefficient
query plans.  Generally speaking, one should not modify the content of
the statistics tables by any mechanism other than invoking the
ANALYZE command.</p>

<p> ^Statistics gathered by ANALYZE are <u>not</u> automatically updated as

Changes to pages/optoverview.in.

766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
  the range constraint on column x should reduce the search space by
  a factor of 10,000 whereas the range constraint on column y should
  reduce the search space by a factor of only 10.  So the ex2i1 index
  should be preferred.
}
PARAGRAPH {
  ^SQLite will make this determination, but only if it has been compiled
  with [SQLITE_ENABLE_STAT2].  ^The [SQLITE_ENABLE_STAT2] option causes
  the [ANALYZE] command to collect a histogram of column content in the
  <b>sqlite_stat2</b> table and to use this histogram to make a better
  guess at the best query to use for range constraints such as the above.
}
PARAGRAPH {
  ^The histogram data is only useful if the right-hand side of the constraint
  is a simple compile-time constant or [parameter] and not an expression.
}
PARAGRAPH {







|

|







766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
  the range constraint on column x should reduce the search space by
  a factor of 10,000 whereas the range constraint on column y should
  reduce the search space by a factor of only 10.  So the ex2i1 index
  should be preferred.
}
PARAGRAPH {
  ^SQLite will make this determination, but only if it has been compiled
  with [SQLITE_ENABLE_STAT3].  ^The [SQLITE_ENABLE_STAT3] option causes
  the [ANALYZE] command to collect a histogram of column content in the
  <b>sqlite_stat3</b> table and to use this histogram to make a better
  guess at the best query to use for range constraints such as the above.
}
PARAGRAPH {
  ^The histogram data is only useful if the right-hand side of the constraint
  is a simple compile-time constant or [parameter] and not an expression.
}
PARAGRAPH {