<?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 xml:lang="en" id="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C"><title>SQL Expressions</title><prolog><metadata><keywords/></metadata></prolog><conbody><p>This document explains how the optimizer changes expressions. </p> <section><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 OS 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><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>