author | Graeme Price <GRAEME.PRICE@NOKIA.COM> |
Fri, 15 Oct 2010 14:32:18 +0100 | |
changeset 15 | 307f4279f433 |
parent 14 | 578be2adaf3e |
permissions | -rw-r--r-- |
14
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
1 |
<?xml version="1.0" encoding="utf-8"?> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
2 |
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
3 |
<!-- This component and the accompanying materials are made available under the terms of the License |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
4 |
"Eclipse Public License v1.0" which accompanies this distribution, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
5 |
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
6 |
<!-- Initial Contributors: |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
7 |
Nokia Corporation - initial contribution. |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
8 |
Contributors: |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
9 |
--> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
10 |
<!DOCTYPE concept |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
11 |
PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
12 |
<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 |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
13 |
the optimizer.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
14 |
<section id="GUID-1612E196-C269-4D58-A36D-6F8F76C7C2F3"><title>Introduction</title> <p>The ANALYZE command is used to provide |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
15 |
the query optimizer with statistical information about the distribution of |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
16 |
values in a database so that it can make better decisions about which indexes |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
17 |
to use. </p> <p><b>Intended |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
18 |
audience:</b> </p> <p>This document is intended to be used by Symbian platfomr |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
19 |
licensees and third party application developers. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
20 |
<section id="GUID-3C9732D5-8F18-5835-A7D5-1D17B870E439"><title>Use the ANALYZE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
21 |
Command to Help the Optimizer</title> <p>A big part of the job of the SQL |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
22 |
compiler is to choose which of many implementation options should be used |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
23 |
for a particular SQL statement. </p> <p>Often a table will have two or more |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
24 |
indexes and the optimizer will need to choose which one of those indexes to |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
25 |
use. Or the optimizer will have to decide between using an index to implement |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
26 |
the WHERE clause or the ORDER BY clause. For a join, the optimizer has to |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
27 |
pick an appropriate order for the table in the join. And so forth. </p> <p>By |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
28 |
default, the only information the optimizer has to go on when trying to choose |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
29 |
between two or more indexes is the database schema. In many cases the schema |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
30 |
alone is not sufficient to make a wise choice. Suppose, for example, you have |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
31 |
a table with two indexes: </p> <codeblock id="GUID-2DE5BC4C-0A22-575F-B1FF-C0B47A0421AB" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
32 |
CREATE TABLE demo318(a,b,c,data); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
33 |
CREATE INDEX idx318a ON demo381(a); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
34 |
CREATE INDEX idx318b ON demo381(b); |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
35 |
</codeblock> <p>And further suppose SQLite is trying to compile a statement |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
36 |
such as the following: </p> <codeblock id="GUID-F0AA8C8F-B6F8-5F50-8AB8-7DE583FDF4F6" xml:space="preserve"> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
37 |
SELECT data FROM demo318 WHERE a=0 AND b=11; |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
38 |
</codeblock> <p>The optimizer has to decide whether it should use idx318a |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
39 |
with the “a=5” term of the WHERE clause or if it should use idx318b with the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
40 |
“b=11” term. </p> <p>Without additional information, the two approaches are |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
41 |
equivalent and so the choice is arbitrary. But the choice of indexes might |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
42 |
have a big impact on performance. </p> <p>Suppose that the demo318 table contains |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
43 |
one million rows of which 55% have a=0, 40% have a=1, and the rest have a=2 |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
44 |
or a=3. If the idx381a index is chosen, it will only narrow down the set of |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
45 |
candidate rows by half. In this situation you are unlikely to notice any performance |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
46 |
gain at all. In fact, in this extreme case, using the index is likely to make |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
47 |
the query slower! </p> <p>On the other hand, let us assume that there are |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
48 |
never more than 2 or 3 rows with the same value for column b. In this case |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
49 |
using index idx381b allows us to reduce the set of candidate rows from one |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
50 |
million to 2 or 3 which will yield a huge speed improvement. </p> <p>So clearly, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
51 |
in this example we hope that the optimizer chooses index idx318b. But without |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
52 |
knowledge of the table contents and the statistical distributions of values |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
53 |
for the “a” and “b” columns, the optimizer has no way of knowing which of |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
54 |
the idx318a and idx318b indexes will work better. </p> <p>The ANALYZE command |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
55 |
is used to provide the query optimizer with statistical information about |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
56 |
the distribution of values in a database so that it can make better decisions |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
57 |
about which indexes to use. </p> <p>When you run the ANALYZE command, SQLite |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
58 |
creates a special table named SQLITE_STAT1. It then reads the entire database, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
59 |
gathers statistics and puts those statistics in the SQLITE_STAT1 table so |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
60 |
that the optimizer can find them. ANALYZE is a relatively slow operation, |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
61 |
since it has to read every single row of every single table in your database. </p> <p>The |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
62 |
statistical information generated by ANALYZE is not kept up-to-date by subsequent |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
63 |
changes to the database. But many databases have a relatively constant statistical |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
64 |
profile so running ANALYZE once and using the same statistical profile forever |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
65 |
thereafter is often sufficient. </p> <p>For embedded applications, you can |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
66 |
often get by without ever having to run ANALYZE on a real database on the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
67 |
embedded device. Instead, generate a template database that contains typical |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
68 |
data for your application and then run ANALYZE on that database on a developer |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
69 |
workstation. Then just copy the content of the resulting SQLITE_STAT1 table |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
70 |
over to the embedded device. </p> <p>The SQLITE_STAT1 table is special in |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
71 |
that you cannot CREATE or DROP it, but you can still SELECT, DELETE, and UPDATE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
72 |
the SQLITE_STAT1 table. So to create the SQLITE_STAT1 table on the embedded |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
73 |
device, just run ANALYZE when the database is empty. That will take virtually |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
74 |
no time and will leave you with an empty SQLITE_STAT1 table. Then copy the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
75 |
content of the SQLITE_STAT1 table that was created on the developer workstation |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
76 |
into the database on the embedded device and the optimizer will begin using |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
77 |
it just as if it had been created locally. </p> <p>There is one entry in the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
78 |
SQLITE_STAT1 table for each index in your schema. Each entry contains the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
79 |
name of the table being indexed, the name of the index itself, and a string |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
80 |
composed of two or more integers separated by spaces. These integers are the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
81 |
statistics that the ANALYZE command collects for each index. </p> <p>The first |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
82 |
integer is the total number of entries in the table. The second integer is |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
83 |
the average number of rows in a result from a query that had an equality constraint |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
84 |
on the first term of the index. The third integer (if there is one) is the |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
85 |
average number of rows that would result if the index were used with equality |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
86 |
constraints on the first two terms of the index. And so forth for as many |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
87 |
terms as there are in the index. </p> <p>The smaller the second and subsequent |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
88 |
integers are, the more selective an index is. And the more selective the index |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
89 |
is, the fewer rows have to be processed when using that index. So the optimizer |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
90 |
tries to use indexes with small second and subsequent numbers. </p> <p>The |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
91 |
details are complex and do not really add any understanding to what is going |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
92 |
on. The key point is that the second and subsequent integers in the SQLITE_STAT1 |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
93 |
table are a measure of the selectivity of an index and that smaller numbers |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
94 |
are better. </p> <p>The statistics gathered by the ANALYZE command reflect |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
95 |
the state of the database at a single point in time. While making the final |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
96 |
performance tweaks on an application, some developers may want to adjust these |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
97 |
statistics manually to better reflect the kinds of data they expect to encounter |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
98 |
in the field. This is easy to do. The SQLITE_STAT1 table is readable and writable |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
99 |
using ordinary SQL statements. You are free to adjust or modify the statistics |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
100 |
in anyway that seems to performance benefit. </p> </section> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
101 |
</conbody><related-links> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
102 |
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
103 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
104 |
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
105 |
Guide</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
106 |
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
107 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
108 |
<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
109 |
Tables</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
110 |
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
111 |
Corruption</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
112 |
<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
113 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
114 |
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
115 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
116 |
<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
117 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
118 |
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
119 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
120 |
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
121 |
Tips</linktext></link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
122 |
<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
123 |
</link> |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
124 |
<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
578be2adaf3e
Week 32 contribution of PDK documentation content. See release notes for details. Fixes bug Bug 3582
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
5
diff
changeset
|
125 |
CLause Tips</linktext></link> |
5
f345bda72bc4
Week 12 contribution of PDK documentation_content. See release notes for details. Fixes Bug 2054, Bug 1583, Bug 381, Bug 390, Bug 463, Bug 1897, Bug 344, Bug 1319, Bug 394, Bug 1520, Bug 1522, Bug 1892"
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
3
diff
changeset
|
126 |
</related-links></concept> |