tempfiles.html

嵌入式数据库sqlite 3.5.9的文档

<p>Queries that contain subqueries must sometime evaluate
the subqueries separately and store the results in a temporary
table, then use the content of the temporary table to evaluate
the outer query.
We call this "materializing" the subquery.
The query optimizer in SQLite attempts to avoid materializing,
but sometimes it is not easily avoidable.
The temporary tables created by materialization are each stored
in their own separate temporary file, which is automatically
deleted at the conclusion of the query.
The size of these temporary tables depends on the amount of
data in the materialization of the subquery, of course.
</p>

<p>
A subquery on the right-hand side of IN operator must often
be materialized.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 WHERE ex1.a IN (SELECT b FROM ex2);
</pre></blockquote>

<p>
In the query above, the subquery "SELECT b FROM ex2" is evaluated
and its results are stored in a temporary table (actually a temporary
index) that allows one to determine whether or not a value ex2.b
exists using a simple binary search.  Once this table is constructed,
the outer query is run and for each prospective result row a check
is made to see if ex1.a is contained within the temporary table.
The row is output only if the check is true.
</p>

<p>
To avoid creating the temporary table, the query might be rewritten
as follows:
</p>

<blockquote><pre>
SELECT * FROM ex1 WHERE EXISTS(SELECT 1 FROM ex2 WHERE ex2.b=ex1.a);
</pre></blockquote>

<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>

<p>
If the right-hand side of an IN operator can be list of values
as in the following:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (1,2,3);
</pre></blockquote>
<p>
List values on the right-hand side of IN are treated as a 
subquery that must be materialized.  In other words, the
previous statement acts as if it were:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL
                              SELECT 2 UNION ALL
                              SELECT 3);
</pre></blockquote>
<p>
A temporary index is always used to hold the values of the
right-hand side of an IN operator when that right-hand side
is a list of values.
</p>

<p>
Subqueries might also need to be materialized when they appear
in the FROM clause of a SELECT statement.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
</pre></blockquote>

<p>
Depending on the query, SQLite might need to materialize the 
"(SELECT b FROM ex2)" subquery into a temporary table, then
perform the join between ex1 and the temporary table.  The
query optimizer tries to avoid this by "flattening" the
query.  In the previous example the query can be flattened,
and SQLite will automatically transform the query into
</p>

<blockquote><pre>
SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
</blockquote></pre>

<p>
More complex queries may or may not be able to employ query
flattening to avoid the temporary table.  Whether or not
the query can be flattened depends on such factors as whether
or not the subquery or outer query contain aggregate functions,
ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth.
The rules for when a query and an cannot be flattened are
very complex and are beyond the scope of this document.
</p>

<h3>2.6 Transient Indices</h3>

<p>
SQLite may make use of transient indices to
implement SQL language features such as:
</p>

<ul>
<li>An ORDER BY or GROUP BY clause</li>
<li>The DISTINCT keyword in an aggregate query</li>
<li>Compound SELECT statements joined by UNION, EXCEPT, or INTERSECT</li>
</ul>

<p>
Each transient index is stored in its own temporary file.
The temporary file for a transient index is automatically deleted
at the end of the statement that uses it.
</p>

<p>
SQLite strives to implement ORDER BY clauses using a preexisting
index.  If an appropriate index already exists, SQLite will walk
the index, rather than the underlying table, to extract the 
requested information, and thus cause the rows to come out in
the desired order.  But if SQLite cannot find an appropriate index
it will evaluate the query and store each row in a transient index
whose data is the row data and whose key is the ORDER BY terms.
After the query is evaluated, SQLite goes back and walks the
transient index from beginning to end in order to output the
rows in the desired order.
</p>

<p>
SQLite implements GROUP BY by ordering the output rows in the
order suggested by the GROUP BY terms.  Each output row is
compared to the previous to see if it starts a new "group".
The ordering by GROUP BY terms is done in exactly the same way
as the ordering by ORDER BY terms.  A preexisting index is used
if possible, but if no suitable index is available, a transient
index is created.
</p>

<p>
The previous two paragraphs describe the implementation of SQLite
as of version 3.5.8.  There are known problems with this approach
for very large results sets - result sets that are larger than the
available disk cache.  Future versions of SQLite will likely address
this deficiency by completely reworking the sort algorithm for 
cases when no suitable preexisting sort index is available.  The
new sort algorithm will also use temporary files, but not in the
same way as the current implementation, the the temporary files
for the new implementation will probably not be index files.
</p>

<p>
The DISTINCT keyword on an aggregate query is implemented by
creating an transient index in a temporary file and storing
each result row in that index.  As new result rows are computed
a check is made to see if they already exist in the transient
index and if they do the new result row is discarded.
</p>

<p>
The UNION operator for compound queries is implemented by creating
a transient index in a temporary file and storing the results
of the left and right subquery in the transient index, discarding
duplicates.  After both subqueries have been evaluated, the
transient index is walked from beginning to end to generate the final output.
</p>

<p>
The EXCEPT operator for compound queries is implemented by creating
a transient index in a temporary file, storing the results of the
left subquery in this transient index, then removing the result 
from right subquery from the transient index, and finally walking
the index from beginning to end to obtain the final output.
</p>

<p>
The INTERSECT operator for compound queries is implemented by
creating two separate transient indices, each in a separate
temporary file.  The left and right subqueries are evaluated
each into a separate transient index.  Then the two indices
are walked together and entries that appear in both indices
are output.
</p>

<p>
Note that the UNION ALL operator for compound queries does not
use transient indices by itself (though of course the right
and left subqueries of the UNION ALL might use transient indices
depending on how they are composed.)

<h3>2.7 Transient Database Used By 
<a href="lang_vacuum.html">VACUUM</a></h3>

<p>
The 
<a href="lang_vacuum.html">VACUUM</a> command works by creating a temporary file
and then rebuilding the entire database into that temporary
file.  Then the content of the temporary file is copied back
into the original database file and the temporary file is
deleted.
</p>

<p>
The temporary file created by the 
<a href="lang_vacuum.html">VACUUM</a> command exists only
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The rollback journal, master journal,
and statement journal files are always written
to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback,
master, and statement journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> 
<a href="pragma.html#syntax">PRAGMA</a>,
and on the size of the temporary file.
</p>

<p>
The TEMP_STORE compile-time parameter is a #define whose value is
an integer between 0 and 3, inclusive.  The meaning of the
TEMP_STORE compile-time parameter is as follows:
</p>

<ol type="1">
<li value="0">
Temporary files are always stored on disk regardless of the setting
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
<li value="1">
Temporary files are stored on disk by default but this can be
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
<li value="2">
Temporary files are stored in memory by default but this can be
overridden by the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
<li value="3">
Temporary files are always stored in memory regardless of the setting
of the <a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</li>
</ol>

<p>
The default value of the TEMP_STORE compile-time parameter is 1,
which means to store temporary files on disk but provide the option
of overriding the behavior using the
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</p>

<p>
The <a href="pragma.html#pragma_temp_store">temp_store pragma</a> has
an integer value which also influences the decision of where to store
temporary files.  The values of the the temp_store pragma have the
following meanings:
</p>

<ol type="1">
<li value="0">
Use either disk or memory storage for temporary files as determined
by the TEMP_STORE compile-time parameter.
</li>
<li value="1">
If the TEMP_STORE compile-time parameter specifies memory storage for
temporary files, then override that decision and use disk storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
<li value="2">
If the TEMP_STORE compile-time parameter specifies disk storage for
temporary files, then override that decision and use memory storage instead.
Otherwise follow the recommendation of the TEMP_STORE compile-time
parameter.
</li>
</ol>

<p>
The default setting for the 
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> is 0,
which means to following the recommendation of TEMP_STORE compile-time
parameter.
</p>

<p>
To reiterate, the TEMP_STORE compile-time parameter an the 
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> only
influence the temporary files other than the rollback journal
and the master journal.  The rollback journal and the master
journal are always written to disk regardless of the settings of
the TEMP_STORE compile-time parameter and the
<a href="pragma.html#pragma_temp_store">temp_store pragma</a>.
</p>

<h2>4.0 Other Temporary File Optimizations</h2>

<p>
SQLite uses a page cache of recently read and written database
pages.  This page cache is used not just for the main database
file but also for transient indices and tables stored in temporary
files.  If SQLite needs to use a temporary index or table and
the TEMP_STORE compile-time parameter and the
<a href="pragma.html#pragma_temp_store">temp_store pragma</a> are
set to store temporary tables and index on disk, the information
is still initially stored in memory in the page cache.  The 
temporary file is not opened and the information is not truly
written to disk until the page cache is full.
</p>

<p>
This means that for many common cases where the temporary tables
and indices are small (small enough to fit into the page cache)
no temporary files are created and no disk I/O occurs.  Only
when the temporary data becomes too large to fit in RAM does
the information spill to disk.
</p>

<p>
Each temporary table and index is given its own page cache
which can store a maximum number of database pages determined
by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter.
(The default value is 500 pages.)
The maximum number of database pages in the page cache is the
same for every temporary table and index.  The value cannot
be changed at run-time or on a per-table or per-index basis.
Each temporary file gets its own private page cache with its
own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.
</p>

<hr><small><i>
This page last modified 2008/04/26 11:22:07 UTC
</i></small></div></body></html>