--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Symbian3/SDK/Source/GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita	Wed Mar 31 11:11:55 2010 +0100
@@ -0,0 +1,117 @@
+<?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-2A2920E0-5D40-5358-BC0C-8572CEFE078C" xml:lang="en"><title>SQL Expressions</title><shortdesc>This document explains how the optimizer changes expressions. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>
+<section id="GUID-D895B0EA-EC70-4664-BDBB-D49D05AB1360"><title>Introduction</title> <p>The way an expression is written really
+does matter. When written well expressions not only clearly state what is
+intended but they can also improve the performance of the SQL engine significantly.
+This document provides several tips designed to help you improve your SQL
+expressions. </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-5E95EACE-CC82-55CD-BF88-C8C92A3222E8"><title>Expression
+Rewriting Optimizations</title> <p>In an earlier section we described how
+the optimizer would only make use of an index if one of the columns being
+indexed occurred on one side of certain equality and comparison operators
+(“<b>=</b> ”, “<b>IN</b> ”, “<b>&lt;</b> ”, “<b>&gt;</b> ”, “<b>&lt;=</b> ”,
+“<b>&gt;=</b> ”, and sometimes “<b>IS NULL</b> ”). </p> <p>While this is technically
+true, prior to the stage of the analysis where the optimizer is looking for
+these kinds of expressions, it may have first modified the <codeph>WHERE</codeph> clause
+(or the <codeph>ON</codeph> or <codeph>HAVING</codeph> clause) from what was
+originally entered by the programmer. </p> <p>The next few paragraphs will
+describe some of these rewriting rules. </p> <p>The query optimizer always
+rewrites the <codeph>BETWEEN</codeph> operator as a pair of inequalities.
+So, for example, if the input SQL is this: </p> <codeblock id="GUID-B864D593-8201-5A83-B8CF-55A4A1B8F42F" xml:space="preserve">
+SELECT * FROM demo324 WHERE x BETWEEN 7 AND 23;
+</codeblock> <p>What the query optimizer ultimately sees is this: </p> <codeblock id="GUID-CC868858-4B31-5626-A85D-78816D033E69" xml:space="preserve">
+SELECT * FROM demo324 WHERE x&gt;=7 AND x&lt;=23;
+</codeblock> <p>In this revised form, the optimizer might be able to use an
+index on the “x” column to speed the operation of the query. </p> <p>Another
+rewriting rule is that a disjunction of two or more equality tests against
+the same column is changed into a single IN operator. So if you write: </p> <codeblock id="GUID-AC72BB25-5BC4-520A-A93E-F8BC9D57A437" xml:space="preserve">
+SELECT * FROM demo324 WHERE x=7 OR x=23 OR x=47;
+</codeblock> <p>The WHERE clause will be rewritten into the following form: </p> <codeblock id="GUID-03269149-6080-5039-8817-A2A2A58195D2" xml:space="preserve">
+SELECT * FROM demo324 WHERE x IN (7,23,47);
+</codeblock> <p>The original format was not a candidate for use of indexes.
+But after the disjunction is converted into a single IN operator the usual
+index processing logic applies and the query can be made much faster. </p> <p>In
+order for this rewriting rule to be applied, however, all terms of the disjunction
+must be equality comparisons against the same column. It will not work to
+have a disjunction involving two or more columns or involving expressions.
+So, for instance, the following statements will not be optimized: </p> <codeblock id="GUID-2D2F87A3-BD6F-5849-819F-BA0226E4E215" xml:space="preserve">
+SELECT * FROM demo324 WHERE x=7 OR y=23;
+SELECT * FROM demo324 WHERE x=7 OR +x=23;
+</codeblock> </section>
+<section id="GUID-4A75E712-CCF5-568A-894E-2684BA7A9D79"><title> Put Constant
+Subexpressions inside Subqueries</title> <p>The query parser and compiler
+in SQLite are designed to be small, fast, and lean. A consequence of this
+design is that SQLite does not do much in the way of constant folding or common
+subexpression elimination. SQLite evaluates SQL expressions mostly as written. </p> <p>One
+way to work around the lack of constant folding in SQLite is to enclose constant
+subexpressions within a subquery. SQLite does optimize constant subqueries
+– it evaluates them once, remembers the result, and then reuses that result
+repeatedly. </p> <p>An example will help clarify how this works. Suppose you
+have a table that contains a timestamp recorded as the fractional julian day
+number: </p> <codeblock id="GUID-0A40E590-A590-5AE2-9BC3-9EE627F92207" xml:space="preserve">
+CREATE TABLE demo325(tm DATE, data BLOB);
+</codeblock> <p>A query against this table to find all entries after November
+8, 2006 might look like the following: </p> <codeblock id="GUID-89C0CA35-B2FC-5217-9E79-52C6C92CEFF8" xml:space="preserve">
+SELECT data FROM demo325 WHERE tm&gt;julianday('2006-11-08');
+</codeblock> <p>This query works fine. The problem is that the “julianday('2006-11-08')”
+function gets called repeatedly, once for each row tested, even though the
+function returns the same value each time. It is much more efficient to call
+the function one time only and reuse the result over and over. You can accomplish
+this by moving the function call inside a subquery as follows: </p> <codeblock id="GUID-326760B0-E190-572D-ADD6-03786B6CB4FD" xml:space="preserve">
+SELECT data FROM demo325 WHERE tm&gt;(SELECT julianday('2006-11-08'));
+</codeblock> <p>There are, of course, some cases where multiple evaluations
+of a function in the WHERE clause is desirable. For example, suppose you want
+to return roughly one out of every eight records, chosen at random. A suitable
+query would be: </p> <codeblock id="GUID-1E734B78-91B6-542E-9EDC-52A378944D48" xml:space="preserve">
+SELECT data FROM demo325 WHERE (random()&amp;7)==0;
+</codeblock> <p>In this case, moving the function evaluation into a subquery
+would not work as desired: </p> <codeblock id="GUID-34B1C0C6-09E7-5994-9DD9-ABB4D5327BA7" xml:space="preserve">
+SELECT data FROM demo325 WHERE (SELECT random()&amp;7)==0;
+</codeblock> <p>In this last example, the result would be either all of the
+records in the table (probability 12.5%) or none of them (probability 87.5%).
+The difference here, of course, is that the <codeph>random()</codeph> function
+is not constant whereas the <codeph>julianday()</codeph> function is. </p> <p>The
+SQL compiler does not have any way of knowing this so it always assumes the
+worst: that every function works like <codeph>random()</codeph> and can potentially
+return a different answer even with the same inputs. Use a subquery if you
+truly want to make a subexpression constant. </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-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient
+Tables</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-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