<?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>=7 AND x<=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>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>(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()&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()&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>