<?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>