<?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-B7E978C1-45CA-554C-8028-D901B97BA2E0" xml:lang="en"><title>ANALYZE Command</title><shortdesc>This guide gives details of how to use the ANALYZE command to help

the optimizer.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

<section id="GUID-1612E196-C269-4D58-A36D-6F8F76C7C2F3"><title>Introduction</title> <p>The ANALYZE command is used to provide

the query optimizer with statistical information about the distribution of

values in a database so that it can make better decisions about which indexes

to use. </p> <p><b>Intended

audience:</b> </p> <p>This document is intended to be used by Symbian platfomr

licensees and third party application developers. </p> </section>

<section id="GUID-3C9732D5-8F18-5835-A7D5-1D17B870E439"><title>Use the ANALYZE

Command to Help the Optimizer</title> <p>A big part of the job of the SQL

compiler is to choose which of many implementation options should be used

for a particular SQL statement. </p> <p>Often a table will have two or more

indexes and the optimizer will need to choose which one of those indexes to

use. Or the optimizer will have to decide between using an index to implement

the WHERE clause or the ORDER BY clause. For a join, the optimizer has to

pick an appropriate order for the table in the join. And so forth. </p> <p>By

default, the only information the optimizer has to go on when trying to choose

between two or more indexes is the database schema. In many cases the schema

alone is not sufficient to make a wise choice. Suppose, for example, you have

a table with two indexes: </p> <codeblock id="GUID-2DE5BC4C-0A22-575F-B1FF-C0B47A0421AB" xml:space="preserve">

CREATE TABLE demo318(a,b,c,data);

CREATE INDEX idx318a ON demo381(a);

CREATE INDEX idx318b ON demo381(b);

</codeblock> <p>And further suppose SQLite is trying to compile a statement

such as the following: </p> <codeblock id="GUID-F0AA8C8F-B6F8-5F50-8AB8-7DE583FDF4F6" xml:space="preserve">

SELECT data FROM demo318 WHERE a=0 AND b=11;

</codeblock> <p>The optimizer has to decide whether it should use idx318a

with the “a=5” term of the WHERE clause or if it should use idx318b with the

“b=11” term. </p> <p>Without additional information, the two approaches are

equivalent and so the choice is arbitrary. But the choice of indexes might

have a big impact on performance. </p> <p>Suppose that the demo318 table contains

one million rows of which 55% have a=0, 40% have a=1, and the rest have a=2

or a=3. If the idx381a index is chosen, it will only narrow down the set of

candidate rows by half. In this situation you are unlikely to notice any performance

gain at all. In fact, in this extreme case, using the index is likely to make

the query slower! </p> <p>On the other hand, let us assume that there are

never more than 2 or 3 rows with the same value for column b. In this case

using index idx381b allows us to reduce the set of candidate rows from one

million to 2 or 3 which will yield a huge speed improvement. </p> <p>So clearly,

in this example we hope that the optimizer chooses index idx318b. But without

knowledge of the table contents and the statistical distributions of values

for the “a” and “b” columns, the optimizer has no way of knowing which of

the idx318a and idx318b indexes will work better. </p> <p>The ANALYZE command

is used to provide the query optimizer with statistical information about

the distribution of values in a database so that it can make better decisions

about which indexes to use. </p> <p>When you run the ANALYZE command, SQLite

creates a special table named SQLITE_STAT1. It then reads the entire database,

gathers statistics and puts those statistics in the SQLITE_STAT1 table so

that the optimizer can find them. ANALYZE is a relatively slow operation,

since it has to read every single row of every single table in your database. </p> <p>The

statistical information generated by ANALYZE is not kept up-to-date by subsequent

changes to the database. But many databases have a relatively constant statistical

profile so running ANALYZE once and using the same statistical profile forever

thereafter is often sufficient. </p> <p>For embedded applications, you can

often get by without ever having to run ANALYZE on a real database on the

embedded device. Instead, generate a template database that contains typical

data for your application and then run ANALYZE on that database on a developer

workstation. Then just copy the content of the resulting SQLITE_STAT1 table

over to the embedded device. </p> <p>The SQLITE_STAT1 table is special in

that you cannot CREATE or DROP it, but you can still SELECT, DELETE, and UPDATE

the SQLITE_STAT1 table. So to create the SQLITE_STAT1 table on the embedded

device, just run ANALYZE when the database is empty. That will take virtually

no time and will leave you with an empty SQLITE_STAT1 table. Then copy the

content of the SQLITE_STAT1 table that was created on the developer workstation

into the database on the embedded device and the optimizer will begin using

it just as if it had been created locally. </p> <p>There is one entry in the

SQLITE_STAT1 table for each index in your schema. Each entry contains the

name of the table being indexed, the name of the index itself, and a string

composed of two or more integers separated by spaces. These integers are the

statistics that the ANALYZE command collects for each index. </p> <p>The first

integer is the total number of entries in the table. The second integer is

the average number of rows in a result from a query that had an equality constraint

on the first term of the index. The third integer (if there is one) is the

average number of rows that would result if the index were used with equality

constraints on the first two terms of the index. And so forth for as many

terms as there are in the index. </p> <p>The smaller the second and subsequent

integers are, the more selective an index is. And the more selective the index

is, the fewer rows have to be processed when using that index. So the optimizer

tries to use indexes with small second and subsequent numbers. </p> <p>The

details are complex and do not really add any understanding to what is going

on. The key point is that the second and subsequent integers in the SQLITE_STAT1

table are a measure of the selectivity of an index and that smaller numbers

are better. </p> <p>The statistics gathered by the ANALYZE command reflect

the state of the database at a single point in time. While making the final

performance tweaks on an application, some developers may want to adjust these

statistics manually to better reflect the kinds of data they expect to encounter

in the field. This is easy to do. The SQLITE_STAT1 table is readable and writable

using ordinary SQL statements. You are free to adjust or modify the statistics

in anyway that seems to performance benefit. </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-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-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</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-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE

CLause Tips</linktext></link>

</related-links></concept>