--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Symbian3/SDK/Source/GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita	Wed Mar 31 11:11:55 2010 +0100
@@ -0,0 +1,191 @@
+<?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>
\ No newline at end of file