7 Nokia Corporation - initial contribution. |
7 Nokia Corporation - initial contribution. |
8 Contributors: |
8 Contributors: |
9 --> |
9 --> |
10 <!DOCTYPE concept |
10 <!DOCTYPE concept |
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
12 <concept xml:lang="en" id="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A"><title>Avoid Transient Tables</title><prolog><metadata><keywords/></metadata></prolog><conbody><p>This guide gives advice for avoiding the use of transient tables. </p> <section><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 OS 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 filesystem. 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"> |
12 <concept id="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A" xml:lang="en"><title>Avoid |
|
13 Transient Tables</title><shortdesc>This guide gives advice for avoiding the use of transient tables.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
|
14 <section id="GUID-191FA663-E058-49EF-A514-008C2755CAC2"><title>Introduction</title> <p>Transient tables are nameless entities |
|
15 that exist for the duration of a single SQL statement and are automatically |
|
16 deleted at the conclusion of the statement. It is best to avoid queries that |
|
17 need transient tables. </p> <p><b>Intended |
|
18 audience:</b> </p> <p>This document is intended to be used by Symbian platform |
|
19 licensees and third party application developers. </p> </section> |
|
20 <section id="GUID-62920E16-50D3-504A-9E8C-B835CF374A8B"><title>Avoid Queries |
|
21 that Require Transient Tables</title> <p>Complex queries sometimes require |
|
22 SQLite to compute intermediate results that are stored in <i>transient tables</i>. |
|
23 Transient tables are nameless entities that exist for the duration of a single |
|
24 SQL statement and are automatically deleted at the conclusion of the statement. </p> <p>We |
|
25 use the term transient to describe these tables rather than temporary to avoid |
|
26 confusion with TEMP tables. TEMP tables are tables that are private to a particular |
|
27 database connection and which persist for the duration of that database connection. |
|
28 TEMP tables have names and work like regular tables with the exceptions that |
|
29 they are only visible to the current database connection and are automatically |
|
30 deleted when the connection is closed. Transient tables, in contrast, are |
|
31 invisible internal holding areas for intermediate results that exist only |
|
32 until the end of the current SQL statement. </p> <p>Transient tables will |
|
33 be stored either in RAM or on disk depending on device configuration. The |
|
34 Symbian default configuration is to store transient tables on disk although |
|
35 downstream device manufacturers may alter this. </p> <p>When configured to |
|
36 store transient tables on disk, in reality a combination of memory and file |
|
37 space is used to hold the table. Transient tables go through the same page |
|
38 cache mechanism that regular database files go through. So as long as the |
|
39 transient tables do not grow too large, they will always be held in memory |
|
40 in the page cache rather than being written to disk. Information is only written |
|
41 to disk when the cache overflows. </p> <p>When transient tables are configured |
|
42 to be stored in memory rather than in a file, the cache is considered infinitely |
|
43 large and will never overflow and thus nothing will ever be written out to |
|
44 the file system. Storing transient tables in memory rather than in files avoids |
|
45 the overhead of file I/O but requires potentially unbounded amounts of memory. |
|
46 Using file-based transient tables puts an upper bound on the amount of memory |
|
47 required but adds file I/O overhead. </p> <p>One strategy for dealing with |
|
48 transient tables is to avoid them all together. If you never use transient |
|
49 tables then it does not really matter if they are stored in memory or on disk. |
|
50 The remainder of this section will enumerate all of the things that might |
|
51 provoke SQLite to create a transient table. If you avoid all of these things |
|
52 in your code, then you never need to worry about where your transient tables |
|
53 are stored. </p> <p>A transient table is created whenever you use the DISTINCT |
|
54 keyword in a query: </p> <codeblock id="GUID-EDCC26CB-7620-5F84-807C-736B2AB46CC7" xml:space="preserve"> |
13 SELECT DISTINCT name FROM artist; |
55 SELECT DISTINCT name FROM artist; |
14 </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"> |
56 </codeblock> <p>The DISTINCT keyword guarantees that each row of the result |
|
57 set will be different from all other rows. In order to enforce this SQLite |
|
58 creates a transient table that stores all prior output rows from the query. |
|
59 If a new row can be found in the transient table then the new row is skipped. |
|
60 The same situation occurs when DISTINCT is used within an aggregate function: </p> <codeblock id="GUID-6A14E006-3233-51E2-9FB1-CC26895F2452" xml:space="preserve"> |
15 SELECT avg(DISTINCT cnt) FROM playlist; |
61 SELECT avg(DISTINCT cnt) FROM playlist; |
16 </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"> |
62 </codeblock> <p>In this context the DISTINCT keyword means that the aggregate |
|
63 function is only applied to distinct elements of the result. As before, a |
|
64 transient table is used to record prior values of the result so that SQLite |
|
65 can tell if new results have been seen before. </p> <p>The UNION, INTERSECT, |
|
66 and EXCEPT operators used to create compound queries always generate a distinct |
|
67 set of rows. Even though the DISTINCT keyword does not appear, it is implied |
|
68 for these connectors, and a transient table is used to enforce the distinctness. |
|
69 In contrast, the UNION ALL operator does not require a transient table. </p> <p>A |
|
70 transient table might be used to implement an ORDER BY or GROUP BY clause. |
|
71 SQLite always tries to use an index to satisfy an ORDER BY or GROUP BY clause |
|
72 if it can. But if no indexes are available which can satisfy the ORDER BY |
|
73 or GROUP BY, then the entire results set is loaded into a transient table |
|
74 and sorted there. </p> <p>Subqueries on the right-hand side of the IN operator |
|
75 use a transient table. Consider an example: </p> <codeblock id="GUID-D6162DA3-7C9D-5C4E-ABF4-E83209354B79" xml:space="preserve"> |
17 SELECT * FROM ex334a WHERE id IN (SELECT id FROM ex334b WHERE amt>7); |
76 SELECT * FROM ex334a WHERE id IN (SELECT id FROM ex334b WHERE amt>7); |
18 </codeblock> <p>The results of the (SELECT id FROM ex334b WHERE amt>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"> |
77 </codeblock> <p>The results of the (SELECT id FROM ex334b WHERE amt>7) subquery |
|
78 are stored in a transient table. Then the value of the <i>id</i> column is |
|
79 checked against this table for each row of the outer query to determine if |
|
80 that row should be included in the result set. </p> <p>Sometimes subqueries |
|
81 in the FROM clause of a query will result in a transient table. This is not |
|
82 always the case because SQLite tries very hard to convert subqueries in the |
|
83 FROM clause into a join that does not use subqueries. SQLite uses the term <i>flattening</i> to |
|
84 describe the conversion of FROM clause subqueries into joins. Flattening is |
|
85 an optimization that makes queries run faster. But in some cases, flattening |
|
86 cannot occur. When the flattening optimization is inhibited, the results of |
|
87 the subqueries are stored in transient tables and then a separate query is |
|
88 run against those transient tables to generate the final results. </p> <p>Consider |
|
89 the following schema and query: </p> <codeblock id="GUID-F8CD6651-09CA-52FD-AAF5-799B087A0456" xml:space="preserve"> |
19 CREATE TABLE t1(a,b,c); |
90 CREATE TABLE t1(a,b,c); |
20 CREATE TABLE t2(x,y,z); |
91 CREATE TABLE t2(x,y,z); |
21 SELECT * FROM t1 JOIN (SELECT x,y FROM t2); |
92 SELECT * FROM t1 JOIN (SELECT x,y FROM t2); |
22 </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"> |
93 </codeblock> <p>The subquery in the FROM clause is plainly visible in the |
|
94 SELECT statement above. But if the subquery were disguised as a view, it might |
|
95 be less noticeable. A view is really just a macro that serves as a place-holder |
|
96 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"> |
23 CREATE VIEW v2 AS SELECT x, y FROM t2; |
97 CREATE VIEW v2 AS SELECT x, y FROM t2; |
24 SELECT * FROM t1 JOIN v2; |
98 SELECT * FROM t1 JOIN v2; |
25 </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"> |
99 </codeblock> <p>In either case above, whether the subquery is stated explicitly |
|
100 or is implied by the use of a view, flattening occurs and the query is converted |
|
101 into this: </p> <codeblock id="GUID-0B933985-4520-5E0E-9762-13CCAB3D186A" xml:space="preserve"> |
26 SELECT a,b,c,x,y FROM t1 JOIN t2; |
102 SELECT a,b,c,x,y FROM t1 JOIN t2; |
27 </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><linktext/></link><link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent |
103 </codeblock> <p>Had flattening not occurred, it would have been necessary |
28 Datafile Corruption</linktext> </link> <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
104 to evaluate the v2 view or the subquery into a transient table then execute |
29 Tips</linktext> </link> <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
105 the outer query using the transient table as one of the two tables in the |
30 Tips</linktext> </link> <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
106 join. SQLite prefers to flatten the query because a flattened query generally |
31 Tips</linktext> </link> <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL |
107 uses fewer resources and is better able to take advantage of indexes. The |
32 Expressions</linktext> </link> <link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
108 rules for determining when flattening occurs and when it does not are complex. |
33 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 |
109 Flattening occurs if all of the following conditions in the outer query and |
34 Command</linktext> </link> <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE CLause |
110 in the subquery are satisfied: </p> <ul> |
35 Tips</linktext> </link> </related-links></concept> |
111 <li id="GUID-C2006064-D688-50F2-BDAA-0903746BBDCA"><p>The subquery and the |
|
112 outer query do not both use aggregates. </p> </li> |
|
113 <li id="GUID-8B0A124C-D2A1-51C3-80B2-37E53A22BEA8"><p>The subquery is not |
|
114 an aggregate or the outer query is not a join. </p> </li> |
|
115 <li id="GUID-265A05AB-6C73-598E-9B5E-D602E3ACF399"><p>The subquery is not |
|
116 the right operand of a left outer join, or the subquery is not itself a join. </p> </li> |
|
117 <li id="GUID-F85FC64A-22E9-550C-B24D-7A8D52AC39E0"><p>The subquery is not |
|
118 DISTINCT or the outer query is not a join. </p> </li> |
|
119 <li id="GUID-A78B6644-6DBE-584B-9216-3415ADA1E35B"><p>The subquery is not |
|
120 DISTINCT or the outer query does not use aggregates. </p> </li> |
|
121 <li id="GUID-B86B1D2C-CC0D-5C04-A2C5-BC3D26069964"><p>The subquery does not |
|
122 use aggregates or the outer query is not DISTINCT. </p> </li> |
|
123 <li id="GUID-D89420C6-6AD0-5B4A-9A1D-DF9CE690BA7D"><p>The subquery has a FROM |
|
124 clause. </p> </li> |
|
125 <li id="GUID-671D51CA-0C05-5610-8A66-0A7B795270B6"><p>The subquery does not |
|
126 use LIMIT or the outer query is not a join. </p> </li> |
|
127 <li id="GUID-81632F3C-3E30-53B8-94C4-214498C11A34"><p>The subquery does not |
|
128 use LIMIT or the outer query does not use aggregates. </p> </li> |
|
129 <li id="GUID-6E30A9D8-F758-58D8-8A60-2D1F98DC571D"><p>The subquery does not |
|
130 use aggregates or the outer query does not use LIMIT. </p> </li> |
|
131 <li id="GUID-55523D18-9421-582C-8CB4-601BC6BBA8A0"><p>The subquery and the |
|
132 outer query do not both have ORDER BY clauses. </p> </li> |
|
133 <li id="GUID-12AD267C-F596-5188-8075-14D29CAB66DF"><p>The subquery is not |
|
134 the right term of a LEFT OUTER JOIN or the subquery has no WHERE clause. </p> </li> |
|
135 <li id="GUID-1E253D10-A3B3-528A-A599-6E000040FAF3"><p>The subquery and outer |
|
136 query do not both use LIMIT </p> </li> |
|
137 <li id="GUID-1FC29A55-C941-59CE-AE16-23C5F867069C"><p>The subquery does not |
|
138 use OFFSET </p> </li> |
|
139 </ul> <p>Nobody really expects a programmer to memorize or even understand |
|
140 the above set of flattening rules. As a short-cut, perhaps it is best to remember |
|
141 that a complicated subquery or view in the FROM clause of a complicated query |
|
142 might defeat the flattening optimization and thus require the use of transient |
|
143 tables. </p> <p>One other obscure use of transient tables is when there is |
|
144 an INSTEAD OF DELETE or INSTEAD OF UPDATE trigger on a view. When such triggers |
|
145 exists and a DELETE or an UPDATE is executed against that view then a transient |
|
146 table is created which stores copies of the rows to be deleted or updated. |
|
147 Since it is unusual to have INSTEAD OF triggers in the first place this case |
|
148 rarely arises. </p> <p>In summary, transient tables are used to implement |
|
149 the following features: </p> <ul> |
|
150 <li id="GUID-AA436587-D5B8-57A4-A256-AA255B316086"><p>The DISTINCT keyword |
|
151 or other situations where distinct results are required such as compound queries |
|
152 using UNION, INTERSECT, or EXCEPT. </p> </li> |
|
153 <li id="GUID-84FC9506-4E85-5F7E-A96A-45EC239360DA"><p>ORDER BY or GROUP BY |
|
154 clauses that cannot be satisfied by indexes. </p> </li> |
|
155 <li id="GUID-59EAEE54-98A0-5647-B4E4-9A213342D46D"><p>Subqueries on the right-hand |
|
156 side of the IN operator. </p> </li> |
|
157 <li id="GUID-BCB93FF3-F959-5292-A3D7-C23CD436DC4E"><p>Subqueries or views |
|
158 in the FROM clause of a query that cannot be flattened. </p> </li> |
|
159 <li id="GUID-552BE4BC-2242-5E26-85C0-20CB99C188A1"><p>DELETE or UPDATE against |
|
160 a view with INSTEAD OF triggers. </p> </li> |
|
161 </ul> <p>The resource-limited nature of the environment, where file I/O is |
|
162 expensive and memory is scarce, means you will be well served to avoid these |
|
163 constructs and thus avoid the need for transient tables. </p> </section> |
|
164 </conbody><related-links> |
|
165 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
|
166 </link> |
|
167 <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
|
168 Guide</linktext></link> |
|
169 <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
|
170 </link> |
|
171 <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
|
172 </link> |
|
173 <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
|
174 Corruption</linktext></link> |
|
175 <link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
|
176 Tips</linktext></link> |
|
177 <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
|
178 Tips</linktext></link> |
|
179 <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
|
180 Tips</linktext></link> |
|
181 <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
|
182 </link> |
|
183 <link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
|
184 Tips</linktext></link> |
|
185 <link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
|
186 </link> |
|
187 <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
|
188 Command</linktext></link> |
|
189 <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
|
190 CLause Tips</linktext></link> |
|
191 </related-links></concept> |