1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: ANALYZE</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>ANALYZE</h2><h4><a href="syntaxdiagrams.html#analyze-stmt">analyze-stmt:</a></h4><blockquote> <img alt="syntax diagram analyze-stmt" src="images/syntax/analyze-stmt.gif"></img> </blockquote>
123
<p> The ANALYZE command gathers statistics about tables and
124
indices and stores the collected information
125
in <a href="fileformat2.html#intschema">internal tables</a> of the database where the query optimizer can
126
access the information and use it to help make better query planning choices.
127
If no arguments are given, all attached databases are
128
analyzed. If a database name is given as the argument, then all tables
129
and indices in that one database are analyzed.
130
If the argument is a table name, then only that table and the
131
indices associated with that table are analyzed. If the argument
132
is an index name, then only that one index is analyzed.</p>
134
<p> The default implementation stores all statistics in a single
135
table named "<a href="fileformat2.html#stat1tab">sqlite_stat1</a>". If SQLite is compiled with the
136
<a href="compile.html#enable_stat3">SQLITE_ENABLE_STAT3</a> option, then additional histogram data is
137
collected and stored in <a href="fileformat2.html#stat3tab">sqlite_stat3</a>.
138
Older versions of SQLite would make use of the <a href="fileformat2.html#stat2tab">sqlite_stat2</a> table
139
when compiled with <a href="compile.html#enable_stat2">SQLITE_ENABLE_STAT2</a> but all recent versions of
140
SQLite ignore the sqlite_stat2 table.
141
Future enhancements may create
142
additional <a href="fileformat2.html#intschema">internal tables</a> with the same name pattern except with
143
final digit larger than "3".</p>
145
<p> The <a href="lang_altertable.html">ALTER TABLE</a> command does
146
not work on the sqlite_stat1 or sqlite_stat3 tables,
147
but all the content of those tables can be queried using <a href="lang_select.html">SELECT</a>
148
and can be deleted, augmented, or modified using the <a href="lang_delete.html">DELETE</a>,
149
<a href="lang_insert.html">INSERT</a>, and <a href="lang_update.html">UPDATE</a> commands.
150
The <a href="lang_droptable.html">DROP TABLE</a> command works on sqlite_stat1 and
151
sqlite_stat3 as of SQLite version 3.7.9.
152
Appropriate care should be used when changing the content of the statistics
153
tables as invalid content can cause SQLite to select inefficient
154
query plans. Generally speaking, one should not modify the content of
155
the statistics tables by any mechanism other than invoking the
158
<p> Statistics gathered by ANALYZE are <u>not</u> automatically updated as
159
the content of the database changes. If the content of the database
160
changes significantly, or if the database schema changes, then one should
161
consider rerunning the ANALYZE command in order to update the statistics.</p>
163
<p> The query planner might not notice manual changes to the
164
sqlite_stat1 and/or sqlite_stat3 tables. An application
165
can force the query planner to reread the statistics tables by running
166
<b>ANALYZE sqlite_master</b>. </p>
added
removed
lang_analyze.html
