<?xml version="1.0" encoding="utf-8"?>
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->
<!-- This component and the accompanying materials are made available under the terms of the License 
"Eclipse Public License v1.0" which accompanies this distribution, 
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->
<!-- Initial Contributors:
    Nokia Corporation - initial contribution.
Contributors: 
-->
<!DOCTYPE concept
  PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
<concept id="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A" xml:lang="en"><title>Avoid
Transient Tables</title><shortdesc>This guide gives advice for avoiding the use of transient tables.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>
<section id="GUID-191FA663-E058-49EF-A514-008C2755CAC2"><title>Introduction</title> <p>Transient tables are nameless entities
that exist for the duration of a single SQL statement and are automatically
deleted at the conclusion of the statement. It is best to avoid queries that
need transient tables. </p> <p><b>Intended
audience:</b> </p> <p>This document is intended to be used by Symbian platform
licensees and third party application developers. </p> </section>
<section id="GUID-62920E16-50D3-504A-9E8C-B835CF374A8B"><title>Avoid Queries
that Require Transient Tables</title> <p>Complex queries sometimes require
SQLite to compute intermediate results that are stored in <i>transient tables</i>.
Transient tables are nameless entities that exist for the duration of a single
SQL statement and are automatically deleted at the conclusion of the statement. </p> <p>We
use the term transient to describe these tables rather than temporary to avoid
confusion with TEMP tables. TEMP tables are tables that are private to a particular
database connection and which persist for the duration of that database connection.
TEMP tables have names and work like regular tables with the exceptions that
they are only visible to the current database connection and are automatically
deleted when the connection is closed. Transient tables, in contrast, are
invisible internal holding areas for intermediate results that exist only
until the end of the current SQL statement. </p> <p>Transient tables will
be stored either in RAM or on disk depending on device configuration. The
Symbian default configuration is to store transient tables on disk although
downstream device manufacturers may alter this. </p> <p>When configured to
store transient tables on disk, in reality a combination of memory and file
space is used to hold the table. Transient tables go through the same page
cache mechanism that regular database files go through. So as long as the
transient tables do not grow too large, they will always be held in memory
in the page cache rather than being written to disk. Information is only written
to disk when the cache overflows. </p> <p>When transient tables are configured
to be stored in memory rather than in a file, the cache is considered infinitely
large and will never overflow and thus nothing will ever be written out to
the file system. Storing transient tables in memory rather than in files avoids
the overhead of file I/O but requires potentially unbounded amounts of memory.
Using file-based transient tables puts an upper bound on the amount of memory
required but adds file I/O overhead. </p> <p>One strategy for dealing with
transient tables is to avoid them all together. If you never use transient
tables then it does not really matter if they are stored in memory or on disk.
The remainder of this section will enumerate all of the things that might
provoke SQLite to create a transient table. If you avoid all of these things
in your code, then you never need to worry about where your transient tables
are stored. </p> <p>A transient table is created whenever you use the DISTINCT
keyword in a query: </p> <codeblock id="GUID-EDCC26CB-7620-5F84-807C-736B2AB46CC7" xml:space="preserve">
SELECT DISTINCT name FROM artist;
</codeblock> <p>The DISTINCT keyword guarantees that each row of the result
set will be different from all other rows. In order to enforce this SQLite
creates a transient table that stores all prior output rows from the query.
If a new row can be found in the transient table then the new row is skipped.
The same situation occurs when DISTINCT is used within an aggregate function: </p> <codeblock id="GUID-6A14E006-3233-51E2-9FB1-CC26895F2452" xml:space="preserve">
SELECT avg(DISTINCT cnt) FROM playlist;
</codeblock> <p>In this context the DISTINCT keyword means that the aggregate
function is only applied to distinct elements of the result. As before, a
transient table is used to record prior values of the result so that SQLite
can tell if new results have been seen before. </p> <p>The UNION, INTERSECT,
and EXCEPT operators used to create compound queries always generate a distinct
set of rows. Even though the DISTINCT keyword does not appear, it is implied
for these connectors, and a transient table is used to enforce the distinctness.
In contrast, the UNION ALL operator does not require a transient table. </p> <p>A
transient table might be used to implement an ORDER BY or GROUP BY clause.
SQLite always tries to use an index to satisfy an ORDER BY or GROUP BY clause
if it can. But if no indexes are available which can satisfy the ORDER BY
or GROUP BY, then the entire results set is loaded into a transient table
and sorted there. </p> <p>Subqueries on the right-hand side of the IN operator
use a transient table. Consider an example: </p> <codeblock id="GUID-D6162DA3-7C9D-5C4E-ABF4-E83209354B79" xml:space="preserve">
SELECT * FROM ex334a WHERE id IN (SELECT id FROM ex334b WHERE amt&gt;7);
</codeblock> <p>The results of the (SELECT id FROM ex334b WHERE amt&gt;7) subquery
are stored in a transient table. Then the value of the <i>id</i> column is
checked against this table for each row of the outer query to determine if
that row should be included in the result set. </p> <p>Sometimes subqueries
in the FROM clause of a query will result in a transient table. This is not
always the case because SQLite tries very hard to convert subqueries in the
FROM clause into a join that does not use subqueries. SQLite uses the term <i>flattening</i> to
describe the conversion of FROM clause subqueries into joins. Flattening is
an optimization that makes queries run faster. But in some cases, flattening
cannot occur. When the flattening optimization is inhibited, the results of
the subqueries are stored in transient tables and then a separate query is
run against those transient tables to generate the final results. </p> <p>Consider
the following schema and query: </p> <codeblock id="GUID-F8CD6651-09CA-52FD-AAF5-799B087A0456" xml:space="preserve">
CREATE TABLE t1(a,b,c);
CREATE TABLE t2(x,y,z);
SELECT * FROM t1 JOIN (SELECT x,y FROM t2);
</codeblock> <p>The subquery in the FROM clause is plainly visible in the
SELECT statement above. But if the subquery were disguised as a view, it might
be less noticeable. A view is really just a macro that serves as a place-holder
for a subquery. So the SELECT statement above is equivalent to the following: </p> <codeblock id="GUID-B5DE2E2A-3034-534C-B808-E68B3086F071" xml:space="preserve">
CREATE VIEW v2 AS SELECT x, y FROM t2;
SELECT * FROM t1 JOIN v2;
</codeblock> <p>In either case above, whether the subquery is stated explicitly
or is implied by the use of a view, flattening occurs and the query is converted
into this: </p> <codeblock id="GUID-0B933985-4520-5E0E-9762-13CCAB3D186A" xml:space="preserve">
SELECT a,b,c,x,y FROM t1 JOIN t2;
</codeblock> <p>Had flattening not occurred, it would have been necessary
to evaluate the v2 view or the subquery into a transient table then execute
the outer query using the transient table as one of the two tables in the
join. SQLite prefers to flatten the query because a flattened query generally
uses fewer resources and is better able to take advantage of indexes. The
rules for determining when flattening occurs and when it does not are complex.
Flattening occurs if all of the following conditions in the outer query and
in the subquery are satisfied: </p> <ul>
<li id="GUID-C2006064-D688-50F2-BDAA-0903746BBDCA"><p>The subquery and the
outer query do not both use aggregates. </p> </li>
<li id="GUID-8B0A124C-D2A1-51C3-80B2-37E53A22BEA8"><p>The subquery is not
an aggregate or the outer query is not a join. </p> </li>
<li id="GUID-265A05AB-6C73-598E-9B5E-D602E3ACF399"><p>The subquery is not
the right operand of a left outer join, or the subquery is not itself a join. </p> </li>
<li id="GUID-F85FC64A-22E9-550C-B24D-7A8D52AC39E0"><p>The subquery is not
DISTINCT or the outer query is not a join. </p> </li>
<li id="GUID-A78B6644-6DBE-584B-9216-3415ADA1E35B"><p>The subquery is not
DISTINCT or the outer query does not use aggregates. </p> </li>
<li id="GUID-B86B1D2C-CC0D-5C04-A2C5-BC3D26069964"><p>The subquery does not
use aggregates or the outer query is not DISTINCT. </p> </li>
<li id="GUID-D89420C6-6AD0-5B4A-9A1D-DF9CE690BA7D"><p>The subquery has a FROM
clause. </p> </li>
<li id="GUID-671D51CA-0C05-5610-8A66-0A7B795270B6"><p>The subquery does not
use LIMIT or the outer query is not a join. </p> </li>
<li id="GUID-81632F3C-3E30-53B8-94C4-214498C11A34"><p>The subquery does not
use LIMIT or the outer query does not use aggregates. </p> </li>
<li id="GUID-6E30A9D8-F758-58D8-8A60-2D1F98DC571D"><p>The subquery does not
use aggregates or the outer query does not use LIMIT. </p> </li>
<li id="GUID-55523D18-9421-582C-8CB4-601BC6BBA8A0"><p>The subquery and the
outer query do not both have ORDER BY clauses. </p> </li>
<li id="GUID-12AD267C-F596-5188-8075-14D29CAB66DF"><p>The subquery is not
the right term of a LEFT OUTER JOIN or the subquery has no WHERE clause. </p> </li>
<li id="GUID-1E253D10-A3B3-528A-A599-6E000040FAF3"><p>The subquery and outer
query do not both use LIMIT </p> </li>
<li id="GUID-1FC29A55-C941-59CE-AE16-23C5F867069C"><p>The subquery does not
use OFFSET </p> </li>
</ul> <p>Nobody really expects a programmer to memorize or even understand
the above set of flattening rules. As a short-cut, perhaps it is best to remember
that a complicated subquery or view in the FROM clause of a complicated query
might defeat the flattening optimization and thus require the use of transient
tables. </p> <p>One other obscure use of transient tables is when there is
an INSTEAD OF DELETE or INSTEAD OF UPDATE trigger on a view. When such triggers
exists and a DELETE or an UPDATE is executed against that view then a transient
table is created which stores copies of the rows to be deleted or updated.
Since it is unusual to have INSTEAD OF triggers in the first place this case
rarely arises. </p> <p>In summary, transient tables are used to implement
the following features: </p> <ul>
<li id="GUID-AA436587-D5B8-57A4-A256-AA255B316086"><p>The DISTINCT keyword
or other situations where distinct results are required such as compound queries
using UNION, INTERSECT, or EXCEPT. </p> </li>
<li id="GUID-84FC9506-4E85-5F7E-A96A-45EC239360DA"><p>ORDER BY or GROUP BY
clauses that cannot be satisfied by indexes. </p> </li>
<li id="GUID-59EAEE54-98A0-5647-B4E4-9A213342D46D"><p>Subqueries on the right-hand
side of the IN operator. </p> </li>
<li id="GUID-BCB93FF3-F959-5292-A3D7-C23CD436DC4E"><p>Subqueries or views
in the FROM clause of a query that cannot be flattened. </p> </li>
<li id="GUID-552BE4BC-2242-5E26-85C0-20CB99C188A1"><p>DELETE or UPDATE against
a view with INSTEAD OF triggers. </p> </li>
</ul> <p>The resource-limited nature of the environment, where file I/O is
expensive and memory is scarce, means you will be well served to avoid these
constructs and thus avoid the need for transient tables. </p> </section>
</conbody><related-links>
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext>
</link>
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server
Guide</linktext></link>
<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext>
</link>
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext>
</link>
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile
Corruption</linktext></link>
<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index
Tips</linktext></link>
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion
Tips</linktext></link>
<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema
Tips</linktext></link>
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext>
</link>
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement
Tips</linktext></link>
<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext>
</link>
<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE
Command</linktext></link>
<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE
CLause Tips</linktext></link>
</related-links></concept>