4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
12 ** This file contains code associated with the ANALYZE command.
14 ** The ANALYZE command gather statistics about the content of tables
15 ** and indices. These statistics are made available to the query planner
16 ** to help it make better decisions about how to perform queries.
18 ** The following system tables are or have been supported:
20 ** CREATE TABLE sqlite_stat1(tbl, idx, stat);
21 ** CREATE TABLE sqlite_stat2(tbl, idx, sampleno, sample);
22 ** CREATE TABLE sqlite_stat3(tbl, idx, nEq, nLt, nDLt, sample);
23 ** CREATE TABLE sqlite_stat4(tbl, idx, nEq, nLt, nDLt, sample);
25 ** Additional tables might be added in future releases of SQLite.
26 ** The sqlite_stat2 table is not created or used unless the SQLite version
27 ** is between 3.6.18 and 3.7.8, inclusive, and unless SQLite is compiled
28 ** with SQLITE_ENABLE_STAT2. The sqlite_stat2 table is deprecated.
29 ** The sqlite_stat2 table is superseded by sqlite_stat3, which is only
30 ** created and used by SQLite versions 3.7.9 and later and with
31 ** SQLITE_ENABLE_STAT3 defined. The functionality of sqlite_stat3
32 ** is a superset of sqlite_stat2. The sqlite_stat4 is an enhanced
33 ** version of sqlite_stat3 and is only available when compiled with
34 ** SQLITE_ENABLE_STAT4 and in SQLite versions 3.8.1 and later. It is
35 ** not possible to enable both STAT3 and STAT4 at the same time. If they
36 ** are both enabled, then STAT4 takes precedence.
38 ** For most applications, sqlite_stat1 provides all the statistics required
39 ** for the query planner to make good choices.
41 ** Format of sqlite_stat1:
43 ** There is normally one row per index, with the index identified by the
44 ** name in the idx column. The tbl column is the name of the table to
45 ** which the index belongs. In each such row, the stat column will be
46 ** a string consisting of a list of integers. The first integer in this
47 ** list is the number of rows in the index. (This is the same as the
48 ** number of rows in the table, except for partial indices.) The second
49 ** integer is the average number of rows in the index that have the same
50 ** value in the first column of the index. The third integer is the average
51 ** number of rows in the index that have the same value for the first two
52 ** columns. The N-th integer (for N>1) is the average number of rows in
53 ** the index which have the same value for the first N-1 columns. For
54 ** a K-column index, there will be K+1 integers in the stat column. If
55 ** the index is unique, then the last integer will be 1.
57 ** The list of integers in the stat column can optionally be followed
58 ** by the keyword "unordered". The "unordered" keyword, if it is present,
59 ** must be separated from the last integer by a single space. If the
60 ** "unordered" keyword is present, then the query planner assumes that
61 ** the index is unordered and will not use the index for a range query.
63 ** If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
64 ** column contains a single integer which is the (estimated) number of
65 ** rows in the table identified by sqlite_stat1.tbl.
67 ** Format of sqlite_stat2:
69 ** The sqlite_stat2 is only created and is only used if SQLite is compiled
70 ** with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
71 ** 3.6.18 and 3.7.8. The "stat2" table contains additional information
72 ** about the distribution of keys within an index. The index is identified by
73 ** the "idx" column and the "tbl" column is the name of the table to which
74 ** the index belongs. There are usually 10 rows in the sqlite_stat2
75 ** table for each index.
77 ** The sqlite_stat2 entries for an index that have sampleno between 0 and 9
78 ** inclusive are samples of the left-most key value in the index taken at
79 ** evenly spaced points along the index. Let the number of samples be S
80 ** (10 in the standard build) and let C be the number of rows in the index.
81 ** Then the sampled rows are given by:
83 ** rownumber = (i*C*2 + C)/(S*2)
85 ** For i between 0 and S-1. Conceptually, the index space is divided into
86 ** S uniform buckets and the samples are the middle row from each bucket.
88 ** The format for sqlite_stat2 is recorded here for legacy reference. This
89 ** version of SQLite does not support sqlite_stat2. It neither reads nor
90 ** writes the sqlite_stat2 table. This version of SQLite only supports
93 ** Format for sqlite_stat3:
95 ** The sqlite_stat3 format is a subset of sqlite_stat4. Hence, the
96 ** sqlite_stat4 format will be described first. Further information
97 ** about sqlite_stat3 follows the sqlite_stat4 description.
99 ** Format for sqlite_stat4:
101 ** As with sqlite_stat2, the sqlite_stat4 table contains histogram data
102 ** to aid the query planner in choosing good indices based on the values
103 ** that indexed columns are compared against in the WHERE clauses of
106 ** The sqlite_stat4 table contains multiple entries for each index.
107 ** The idx column names the index and the tbl column is the table of the
108 ** index. If the idx and tbl columns are the same, then the sample is
109 ** of the INTEGER PRIMARY KEY. The sample column is a blob which is the
110 ** binary encoding of a key from the index. The nEq column is a
111 ** list of integers. The first integer is the approximate number
112 ** of entries in the index whose left-most column exactly matches
113 ** the left-most column of the sample. The second integer in nEq
114 ** is the approximate number of entries in the index where the
115 ** first two columns match the first two columns of the sample.
116 ** And so forth. nLt is another list of integers that show the approximate
117 ** number of entries that are strictly less than the sample. The first
118 ** integer in nLt contains the number of entries in the index where the
119 ** left-most column is less than the left-most column of the sample.
120 ** The K-th integer in the nLt entry is the number of index entries
121 ** where the first K columns are less than the first K columns of the
122 ** sample. The nDLt column is like nLt except that it contains the
123 ** number of distinct entries in the index that are less than the
126 ** There can be an arbitrary number of sqlite_stat4 entries per index.
127 ** The ANALYZE command will typically generate sqlite_stat4 tables
128 ** that contain between 10 and 40 samples which are distributed across
129 ** the key space, though not uniformly, and which include samples with
132 ** Format for sqlite_stat3 redux:
134 ** The sqlite_stat3 table is like sqlite_stat4 except that it only
135 ** looks at the left-most column of the index. The sqlite_stat3.sample
136 ** column contains the actual value of the left-most column instead
137 ** of a blob encoding of the complete index key as is found in
138 ** sqlite_stat4.sample. The nEq, nLt, and nDLt entries of sqlite_stat3
139 ** all contain just a single integer which is the same as the first
140 ** integer in the equivalent columns in sqlite_stat4.
142 #ifndef SQLITE_OMIT_ANALYZE
143 #include "sqliteInt.h"
145 #if defined(SQLITE_ENABLE_STAT4)
148 #elif defined(SQLITE_ENABLE_STAT3)
154 # undef SQLITE_STAT4_SAMPLES
155 # define SQLITE_STAT4_SAMPLES 1
157 #define IsStat34 (IsStat3+IsStat4) /* 1 for STAT3 or STAT4. 0 otherwise */
160 ** This routine generates code that opens the sqlite_statN tables.
161 ** The sqlite_stat1 table is always relevant. sqlite_stat2 is now
162 ** obsolete. sqlite_stat3 and sqlite_stat4 are only opened when
163 ** appropriate compile-time options are provided.
165 ** If the sqlite_statN tables do not previously exist, it is created.
167 ** Argument zWhere may be a pointer to a buffer containing a table name,
168 ** or it may be a NULL pointer. If it is not NULL, then all entries in
169 ** the sqlite_statN tables associated with the named table are deleted.
170 ** If zWhere==0, then code is generated to delete all stat table entries.
172 static void openStatTable(
173 Parse
*pParse
, /* Parsing context */
174 int iDb
, /* The database we are looking in */
175 int iStatCur
, /* Open the sqlite_stat1 table on this cursor */
176 const char *zWhere
, /* Delete entries for this table or index */
177 const char *zWhereType
/* Either "tbl" or "idx" */
179 static const struct {
183 { "sqlite_stat1", "tbl,idx,stat" },
184 #if defined(SQLITE_ENABLE_STAT4)
185 { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" },
186 { "sqlite_stat3", 0 },
187 #elif defined(SQLITE_ENABLE_STAT3)
188 { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
189 { "sqlite_stat4", 0 },
191 { "sqlite_stat3", 0 },
192 { "sqlite_stat4", 0 },
196 sqlite3
*db
= pParse
->db
;
198 Vdbe
*v
= sqlite3GetVdbe(pParse
);
199 int aRoot
[ArraySize(aTable
)];
200 u8 aCreateTbl
[ArraySize(aTable
)];
203 assert( sqlite3BtreeHoldsAllMutexes(db
) );
204 assert( sqlite3VdbeDb(v
)==db
);
207 /* Create new statistic tables if they do not exist, or clear them
208 ** if they do already exist.
210 for(i
=0; i
<ArraySize(aTable
); i
++){
211 const char *zTab
= aTable
[i
].zName
;
213 if( (pStat
= sqlite3FindTable(db
, zTab
, pDb
->zDbSName
))==0 ){
214 if( aTable
[i
].zCols
){
215 /* The sqlite_statN table does not exist. Create it. Note that a
216 ** side-effect of the CREATE TABLE statement is to leave the rootpage
217 ** of the new table in register pParse->regRoot. This is important
218 ** because the OpenWrite opcode below will be needing it. */
219 sqlite3NestedParse(pParse
,
220 "CREATE TABLE %Q.%s(%s)", pDb
->zDbSName
, zTab
, aTable
[i
].zCols
222 aRoot
[i
] = pParse
->regRoot
;
223 aCreateTbl
[i
] = OPFLAG_P2ISREG
;
226 /* The table already exists. If zWhere is not NULL, delete all entries
227 ** associated with the table zWhere. If zWhere is NULL, delete the
228 ** entire contents of the table. */
229 aRoot
[i
] = pStat
->tnum
;
231 sqlite3TableLock(pParse
, iDb
, aRoot
[i
], 1, zTab
);
233 sqlite3NestedParse(pParse
,
234 "DELETE FROM %Q.%s WHERE %s=%Q",
235 pDb
->zDbSName
, zTab
, zWhereType
, zWhere
238 /* The sqlite_stat[134] table already exists. Delete all rows. */
239 sqlite3VdbeAddOp2(v
, OP_Clear
, aRoot
[i
], iDb
);
244 /* Open the sqlite_stat[134] tables for writing. */
245 for(i
=0; aTable
[i
].zCols
; i
++){
246 assert( i
<ArraySize(aTable
) );
247 sqlite3VdbeAddOp4Int(v
, OP_OpenWrite
, iStatCur
+i
, aRoot
[i
], iDb
, 3);
248 sqlite3VdbeChangeP5(v
, aCreateTbl
[i
]);
249 VdbeComment((v
, aTable
[i
].zName
));
254 ** Recommended number of samples for sqlite_stat4
256 #ifndef SQLITE_STAT4_SAMPLES
257 # define SQLITE_STAT4_SAMPLES 24
261 ** Three SQL functions - stat_init(), stat_push(), and stat_get() -
262 ** share an instance of the following structure to hold their state
265 typedef struct Stat4Accum Stat4Accum
;
266 typedef struct Stat4Sample Stat4Sample
;
268 tRowcnt
*anEq
; /* sqlite_stat4.nEq */
269 tRowcnt
*anDLt
; /* sqlite_stat4.nDLt */
270 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
271 tRowcnt
*anLt
; /* sqlite_stat4.nLt */
273 i64 iRowid
; /* Rowid in main table of the key */
274 u8
*aRowid
; /* Key for WITHOUT ROWID tables */
276 u32 nRowid
; /* Sizeof aRowid[] */
277 u8 isPSample
; /* True if a periodic sample */
278 int iCol
; /* If !isPSample, the reason for inclusion */
279 u32 iHash
; /* Tiebreaker hash */
283 tRowcnt nRow
; /* Number of rows in the entire table */
284 tRowcnt nPSample
; /* How often to do a periodic sample */
285 int nCol
; /* Number of columns in index + pk/rowid */
286 int nKeyCol
; /* Number of index columns w/o the pk/rowid */
287 int mxSample
; /* Maximum number of samples to accumulate */
288 Stat4Sample current
; /* Current row as a Stat4Sample */
289 u32 iPrn
; /* Pseudo-random number used for sampling */
290 Stat4Sample
*aBest
; /* Array of nCol best samples */
291 int iMin
; /* Index in a[] of entry with minimum score */
292 int nSample
; /* Current number of samples */
293 int nMaxEqZero
; /* Max leading 0 in anEq[] for any a[] entry */
294 int iGet
; /* Index of current sample accessed by stat_get() */
295 Stat4Sample
*a
; /* Array of mxSample Stat4Sample objects */
296 sqlite3
*db
; /* Database connection, for malloc() */
299 /* Reclaim memory used by a Stat4Sample
301 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
302 static void sampleClear(sqlite3
*db
, Stat4Sample
*p
){
305 sqlite3DbFree(db
, p
->u
.aRowid
);
311 /* Initialize the BLOB value of a ROWID
313 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
314 static void sampleSetRowid(sqlite3
*db
, Stat4Sample
*p
, int n
, const u8
*pData
){
316 if( p
->nRowid
) sqlite3DbFree(db
, p
->u
.aRowid
);
317 p
->u
.aRowid
= sqlite3DbMallocRawNN(db
, n
);
320 memcpy(p
->u
.aRowid
, pData
, n
);
327 /* Initialize the INTEGER value of a ROWID.
329 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
330 static void sampleSetRowidInt64(sqlite3
*db
, Stat4Sample
*p
, i64 iRowid
){
332 if( p
->nRowid
) sqlite3DbFree(db
, p
->u
.aRowid
);
334 p
->u
.iRowid
= iRowid
;
340 ** Copy the contents of object (*pFrom) into (*pTo).
342 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
343 static void sampleCopy(Stat4Accum
*p
, Stat4Sample
*pTo
, Stat4Sample
*pFrom
){
344 pTo
->isPSample
= pFrom
->isPSample
;
345 pTo
->iCol
= pFrom
->iCol
;
346 pTo
->iHash
= pFrom
->iHash
;
347 memcpy(pTo
->anEq
, pFrom
->anEq
, sizeof(tRowcnt
)*p
->nCol
);
348 memcpy(pTo
->anLt
, pFrom
->anLt
, sizeof(tRowcnt
)*p
->nCol
);
349 memcpy(pTo
->anDLt
, pFrom
->anDLt
, sizeof(tRowcnt
)*p
->nCol
);
351 sampleSetRowid(p
->db
, pTo
, pFrom
->nRowid
, pFrom
->u
.aRowid
);
353 sampleSetRowidInt64(p
->db
, pTo
, pFrom
->u
.iRowid
);
359 ** Reclaim all memory of a Stat4Accum structure.
361 static void stat4Destructor(void *pOld
){
362 Stat4Accum
*p
= (Stat4Accum
*)pOld
;
363 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
365 for(i
=0; i
<p
->nCol
; i
++) sampleClear(p
->db
, p
->aBest
+i
);
366 for(i
=0; i
<p
->mxSample
; i
++) sampleClear(p
->db
, p
->a
+i
);
367 sampleClear(p
->db
, &p
->current
);
369 sqlite3DbFree(p
->db
, p
);
373 ** Implementation of the stat_init(N,K,C) SQL function. The three parameters
375 ** N: The number of columns in the index including the rowid/pk (note 1)
376 ** K: The number of columns in the index excluding the rowid/pk.
377 ** C: The number of rows in the index (note 2)
379 ** Note 1: In the special case of the covering index that implements a
380 ** WITHOUT ROWID table, N is the number of PRIMARY KEY columns, not the
381 ** total number of columns in the table.
383 ** Note 2: C is only used for STAT3 and STAT4.
385 ** For indexes on ordinary rowid tables, N==K+1. But for indexes on
386 ** WITHOUT ROWID tables, N=K+P where P is the number of columns in the
387 ** PRIMARY KEY of the table. The covering index that implements the
388 ** original WITHOUT ROWID table as N==K as a special case.
390 ** This routine allocates the Stat4Accum object in heap memory. The return
391 ** value is a pointer to the Stat4Accum object. The datatype of the
392 ** return value is BLOB, but it is really just a pointer to the Stat4Accum
395 static void statInit(
396 sqlite3_context
*context
,
401 int nCol
; /* Number of columns in index being sampled */
402 int nKeyCol
; /* Number of key columns */
403 int nColUp
; /* nCol rounded up for alignment */
404 int n
; /* Bytes of space to allocate */
405 sqlite3
*db
; /* Database connection */
406 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
407 int mxSample
= SQLITE_STAT4_SAMPLES
;
410 /* Decode the three function arguments */
411 UNUSED_PARAMETER(argc
);
412 nCol
= sqlite3_value_int(argv
[0]);
414 nColUp
= sizeof(tRowcnt
)<8 ? (nCol
+1)&~1 : nCol
;
415 nKeyCol
= sqlite3_value_int(argv
[1]);
416 assert( nKeyCol
<=nCol
);
419 /* Allocate the space required for the Stat4Accum object */
421 + sizeof(tRowcnt
)*nColUp
/* Stat4Accum.anEq */
422 + sizeof(tRowcnt
)*nColUp
/* Stat4Accum.anDLt */
423 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
424 + sizeof(tRowcnt
)*nColUp
/* Stat4Accum.anLt */
425 + sizeof(Stat4Sample
)*(nCol
+mxSample
) /* Stat4Accum.aBest[], a[] */
426 + sizeof(tRowcnt
)*3*nColUp
*(nCol
+mxSample
)
429 db
= sqlite3_context_db_handle(context
);
430 p
= sqlite3DbMallocZero(db
, n
);
432 sqlite3_result_error_nomem(context
);
439 p
->nKeyCol
= nKeyCol
;
440 p
->current
.anDLt
= (tRowcnt
*)&p
[1];
441 p
->current
.anEq
= &p
->current
.anDLt
[nColUp
];
443 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
445 u8
*pSpace
; /* Allocated space not yet assigned */
446 int i
; /* Used to iterate through p->aSample[] */
449 p
->mxSample
= mxSample
;
450 p
->nPSample
= (tRowcnt
)(sqlite3_value_int64(argv
[2])/(mxSample
/3+1) + 1);
451 p
->current
.anLt
= &p
->current
.anEq
[nColUp
];
452 p
->iPrn
= 0x689e962d*(u32
)nCol
^ 0xd0944565*(u32
)sqlite3_value_int(argv
[2]);
454 /* Set up the Stat4Accum.a[] and aBest[] arrays */
455 p
->a
= (struct Stat4Sample
*)&p
->current
.anLt
[nColUp
];
456 p
->aBest
= &p
->a
[mxSample
];
457 pSpace
= (u8
*)(&p
->a
[mxSample
+nCol
]);
458 for(i
=0; i
<(mxSample
+nCol
); i
++){
459 p
->a
[i
].anEq
= (tRowcnt
*)pSpace
; pSpace
+= (sizeof(tRowcnt
) * nColUp
);
460 p
->a
[i
].anLt
= (tRowcnt
*)pSpace
; pSpace
+= (sizeof(tRowcnt
) * nColUp
);
461 p
->a
[i
].anDLt
= (tRowcnt
*)pSpace
; pSpace
+= (sizeof(tRowcnt
) * nColUp
);
463 assert( (pSpace
- (u8
*)p
)==n
);
465 for(i
=0; i
<nCol
; i
++){
466 p
->aBest
[i
].iCol
= i
;
471 /* Return a pointer to the allocated object to the caller. Note that
472 ** only the pointer (the 2nd parameter) matters. The size of the object
473 ** (given by the 3rd parameter) is never used and can be any positive
475 sqlite3_result_blob(context
, p
, sizeof(*p
), stat4Destructor
);
477 static const FuncDef statInitFuncdef
= {
478 2+IsStat34
, /* nArg */
479 SQLITE_UTF8
, /* funcFlags */
482 statInit
, /* xSFunc */
484 "stat_init", /* zName */
488 #ifdef SQLITE_ENABLE_STAT4
490 ** pNew and pOld are both candidate non-periodic samples selected for
491 ** the same column (pNew->iCol==pOld->iCol). Ignoring this column and
492 ** considering only any trailing columns and the sample hash value, this
493 ** function returns true if sample pNew is to be preferred over pOld.
494 ** In other words, if we assume that the cardinalities of the selected
495 ** column for pNew and pOld are equal, is pNew to be preferred over pOld.
497 ** This function assumes that for each argument sample, the contents of
498 ** the anEq[] array from pSample->anEq[pSample->iCol+1] onwards are valid.
500 static int sampleIsBetterPost(
505 int nCol
= pAccum
->nCol
;
507 assert( pNew
->iCol
==pOld
->iCol
);
508 for(i
=pNew
->iCol
+1; i
<nCol
; i
++){
509 if( pNew
->anEq
[i
]>pOld
->anEq
[i
] ) return 1;
510 if( pNew
->anEq
[i
]<pOld
->anEq
[i
] ) return 0;
512 if( pNew
->iHash
>pOld
->iHash
) return 1;
517 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
519 ** Return true if pNew is to be preferred over pOld.
521 ** This function assumes that for each argument sample, the contents of
522 ** the anEq[] array from pSample->anEq[pSample->iCol] onwards are valid.
524 static int sampleIsBetter(
529 tRowcnt nEqNew
= pNew
->anEq
[pNew
->iCol
];
530 tRowcnt nEqOld
= pOld
->anEq
[pOld
->iCol
];
532 assert( pOld
->isPSample
==0 && pNew
->isPSample
==0 );
533 assert( IsStat4
|| (pNew
->iCol
==0 && pOld
->iCol
==0) );
535 if( (nEqNew
>nEqOld
) ) return 1;
536 #ifdef SQLITE_ENABLE_STAT4
537 if( nEqNew
==nEqOld
){
538 if( pNew
->iCol
<pOld
->iCol
) return 1;
539 return (pNew
->iCol
==pOld
->iCol
&& sampleIsBetterPost(pAccum
, pNew
, pOld
));
543 return (nEqNew
==nEqOld
&& pNew
->iHash
>pOld
->iHash
);
548 ** Copy the contents of sample *pNew into the p->a[] array. If necessary,
549 ** remove the least desirable sample from p->a[] to make room.
551 static void sampleInsert(Stat4Accum
*p
, Stat4Sample
*pNew
, int nEqZero
){
552 Stat4Sample
*pSample
= 0;
555 assert( IsStat4
|| nEqZero
==0 );
557 #ifdef SQLITE_ENABLE_STAT4
558 /* Stat4Accum.nMaxEqZero is set to the maximum number of leading 0
559 ** values in the anEq[] array of any sample in Stat4Accum.a[]. In
560 ** other words, if nMaxEqZero is n, then it is guaranteed that there
561 ** are no samples with Stat4Sample.anEq[m]==0 for (m>=n). */
562 if( nEqZero
>p
->nMaxEqZero
){
563 p
->nMaxEqZero
= nEqZero
;
565 if( pNew
->isPSample
==0 ){
566 Stat4Sample
*pUpgrade
= 0;
567 assert( pNew
->anEq
[pNew
->iCol
]>0 );
569 /* This sample is being added because the prefix that ends in column
570 ** iCol occurs many times in the table. However, if we have already
571 ** added a sample that shares this prefix, there is no need to add
572 ** this one. Instead, upgrade the priority of the highest priority
573 ** existing sample that shares this prefix. */
574 for(i
=p
->nSample
-1; i
>=0; i
--){
575 Stat4Sample
*pOld
= &p
->a
[i
];
576 if( pOld
->anEq
[pNew
->iCol
]==0 ){
577 if( pOld
->isPSample
) return;
578 assert( pOld
->iCol
>pNew
->iCol
);
579 assert( sampleIsBetter(p
, pNew
, pOld
) );
580 if( pUpgrade
==0 || sampleIsBetter(p
, pOld
, pUpgrade
) ){
586 pUpgrade
->iCol
= pNew
->iCol
;
587 pUpgrade
->anEq
[pUpgrade
->iCol
] = pNew
->anEq
[pUpgrade
->iCol
];
593 /* If necessary, remove sample iMin to make room for the new sample. */
594 if( p
->nSample
>=p
->mxSample
){
595 Stat4Sample
*pMin
= &p
->a
[p
->iMin
];
596 tRowcnt
*anEq
= pMin
->anEq
;
597 tRowcnt
*anLt
= pMin
->anLt
;
598 tRowcnt
*anDLt
= pMin
->anDLt
;
599 sampleClear(p
->db
, pMin
);
600 memmove(pMin
, &pMin
[1], sizeof(p
->a
[0])*(p
->nSample
-p
->iMin
-1));
601 pSample
= &p
->a
[p
->nSample
-1];
603 pSample
->anEq
= anEq
;
604 pSample
->anDLt
= anDLt
;
605 pSample
->anLt
= anLt
;
606 p
->nSample
= p
->mxSample
-1;
609 /* The "rows less-than" for the rowid column must be greater than that
610 ** for the last sample in the p->a[] array. Otherwise, the samples would
611 ** be out of order. */
612 #ifdef SQLITE_ENABLE_STAT4
613 assert( p
->nSample
==0
614 || pNew
->anLt
[p
->nCol
-1] > p
->a
[p
->nSample
-1].anLt
[p
->nCol
-1] );
617 /* Insert the new sample */
618 pSample
= &p
->a
[p
->nSample
];
619 sampleCopy(p
, pSample
, pNew
);
622 /* Zero the first nEqZero entries in the anEq[] array. */
623 memset(pSample
->anEq
, 0, sizeof(tRowcnt
)*nEqZero
);
625 #ifdef SQLITE_ENABLE_STAT4
628 if( p
->nSample
>=p
->mxSample
){
630 for(i
=0; i
<p
->mxSample
; i
++){
631 if( p
->a
[i
].isPSample
) continue;
632 if( iMin
<0 || sampleIsBetter(p
, &p
->a
[iMin
], &p
->a
[i
]) ){
640 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
643 ** Field iChng of the index being scanned has changed. So at this point
644 ** p->current contains a sample that reflects the previous row of the
645 ** index. The value of anEq[iChng] and subsequent anEq[] elements are
646 ** correct at this point.
648 static void samplePushPrevious(Stat4Accum
*p
, int iChng
){
649 #ifdef SQLITE_ENABLE_STAT4
652 /* Check if any samples from the aBest[] array should be pushed
653 ** into IndexSample.a[] at this point. */
654 for(i
=(p
->nCol
-2); i
>=iChng
; i
--){
655 Stat4Sample
*pBest
= &p
->aBest
[i
];
656 pBest
->anEq
[i
] = p
->current
.anEq
[i
];
657 if( p
->nSample
<p
->mxSample
|| sampleIsBetter(p
, pBest
, &p
->a
[p
->iMin
]) ){
658 sampleInsert(p
, pBest
, i
);
662 /* Check that no sample contains an anEq[] entry with an index of
663 ** p->nMaxEqZero or greater set to zero. */
664 for(i
=p
->nSample
-1; i
>=0; i
--){
666 for(j
=p
->nMaxEqZero
; j
<p
->nCol
; j
++) assert( p
->a
[i
].anEq
[j
]>0 );
669 /* Update the anEq[] fields of any samples already collected. */
670 if( iChng
<p
->nMaxEqZero
){
671 for(i
=p
->nSample
-1; i
>=0; i
--){
673 for(j
=iChng
; j
<p
->nCol
; j
++){
674 if( p
->a
[i
].anEq
[j
]==0 ) p
->a
[i
].anEq
[j
] = p
->current
.anEq
[j
];
677 p
->nMaxEqZero
= iChng
;
681 #if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
683 tRowcnt nLt
= p
->current
.anLt
[0];
684 tRowcnt nEq
= p
->current
.anEq
[0];
686 /* Check if this is to be a periodic sample. If so, add it. */
687 if( (nLt
/p
->nPSample
)!=(nLt
+nEq
)/p
->nPSample
){
688 p
->current
.isPSample
= 1;
689 sampleInsert(p
, &p
->current
, 0);
690 p
->current
.isPSample
= 0;
693 /* Or if it is a non-periodic sample. Add it in this case too. */
694 if( p
->nSample
<p
->mxSample
695 || sampleIsBetter(p
, &p
->current
, &p
->a
[p
->iMin
])
697 sampleInsert(p
, &p
->current
, 0);
702 #ifndef SQLITE_ENABLE_STAT3_OR_STAT4
703 UNUSED_PARAMETER( p
);
704 UNUSED_PARAMETER( iChng
);
709 ** Implementation of the stat_push SQL function: stat_push(P,C,R)
712 ** P Pointer to the Stat4Accum object created by stat_init()
713 ** C Index of left-most column to differ from previous row
714 ** R Rowid for the current row. Might be a key record for
715 ** WITHOUT ROWID tables.
717 ** This SQL function always returns NULL. It's purpose it to accumulate
718 ** statistical data and/or samples in the Stat4Accum object about the
719 ** index being analyzed. The stat_get() SQL function will later be used to
720 ** extract relevant information for constructing the sqlite_statN tables.
722 ** The R parameter is only used for STAT3 and STAT4
724 static void statPush(
725 sqlite3_context
*context
,
731 /* The three function arguments */
732 Stat4Accum
*p
= (Stat4Accum
*)sqlite3_value_blob(argv
[0]);
733 int iChng
= sqlite3_value_int(argv
[1]);
735 UNUSED_PARAMETER( argc
);
736 UNUSED_PARAMETER( context
);
738 assert( iChng
<p
->nCol
);
741 /* This is the first call to this function. Do initialization. */
742 for(i
=0; i
<p
->nCol
; i
++) p
->current
.anEq
[i
] = 1;
744 /* Second and subsequent calls get processed here */
745 samplePushPrevious(p
, iChng
);
747 /* Update anDLt[], anLt[] and anEq[] to reflect the values that apply
748 ** to the current row of the index. */
749 for(i
=0; i
<iChng
; i
++){
750 p
->current
.anEq
[i
]++;
752 for(i
=iChng
; i
<p
->nCol
; i
++){
753 p
->current
.anDLt
[i
]++;
754 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
755 p
->current
.anLt
[i
] += p
->current
.anEq
[i
];
757 p
->current
.anEq
[i
] = 1;
761 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
762 if( sqlite3_value_type(argv
[2])==SQLITE_INTEGER
){
763 sampleSetRowidInt64(p
->db
, &p
->current
, sqlite3_value_int64(argv
[2]));
765 sampleSetRowid(p
->db
, &p
->current
, sqlite3_value_bytes(argv
[2]),
766 sqlite3_value_blob(argv
[2]));
768 p
->current
.iHash
= p
->iPrn
= p
->iPrn
*1103515245 + 12345;
771 #ifdef SQLITE_ENABLE_STAT4
773 tRowcnt nLt
= p
->current
.anLt
[p
->nCol
-1];
775 /* Check if this is to be a periodic sample. If so, add it. */
776 if( (nLt
/p
->nPSample
)!=(nLt
+1)/p
->nPSample
){
777 p
->current
.isPSample
= 1;
779 sampleInsert(p
, &p
->current
, p
->nCol
-1);
780 p
->current
.isPSample
= 0;
783 /* Update the aBest[] array. */
784 for(i
=0; i
<(p
->nCol
-1); i
++){
786 if( i
>=iChng
|| sampleIsBetterPost(p
, &p
->current
, &p
->aBest
[i
]) ){
787 sampleCopy(p
, &p
->aBest
[i
], &p
->current
);
793 static const FuncDef statPushFuncdef
= {
794 2+IsStat34
, /* nArg */
795 SQLITE_UTF8
, /* funcFlags */
798 statPush
, /* xSFunc */
800 "stat_push", /* zName */
804 #define STAT_GET_STAT1 0 /* "stat" column of stat1 table */
805 #define STAT_GET_ROWID 1 /* "rowid" column of stat[34] entry */
806 #define STAT_GET_NEQ 2 /* "neq" column of stat[34] entry */
807 #define STAT_GET_NLT 3 /* "nlt" column of stat[34] entry */
808 #define STAT_GET_NDLT 4 /* "ndlt" column of stat[34] entry */
811 ** Implementation of the stat_get(P,J) SQL function. This routine is
812 ** used to query statistical information that has been gathered into
813 ** the Stat4Accum object by prior calls to stat_push(). The P parameter
814 ** has type BLOB but it is really just a pointer to the Stat4Accum object.
815 ** The content to returned is determined by the parameter J
816 ** which is one of the STAT_GET_xxxx values defined above.
818 ** The stat_get(P,J) function is not available to generic SQL. It is
819 ** inserted as part of a manually constructed bytecode program. (See
820 ** the callStatGet() routine below.) It is guaranteed that the P
821 ** parameter will always be a poiner to a Stat4Accum object, never a
824 ** If neither STAT3 nor STAT4 are enabled, then J is always
825 ** STAT_GET_STAT1 and is hence omitted and this routine becomes
826 ** a one-parameter function, stat_get(P), that always returns the
827 ** stat1 table entry information.
830 sqlite3_context
*context
,
834 Stat4Accum
*p
= (Stat4Accum
*)sqlite3_value_blob(argv
[0]);
835 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
836 /* STAT3 and STAT4 have a parameter on this routine. */
837 int eCall
= sqlite3_value_int(argv
[1]);
839 assert( eCall
==STAT_GET_STAT1
|| eCall
==STAT_GET_NEQ
840 || eCall
==STAT_GET_ROWID
|| eCall
==STAT_GET_NLT
841 || eCall
==STAT_GET_NDLT
843 if( eCall
==STAT_GET_STAT1
)
848 /* Return the value to store in the "stat" column of the sqlite_stat1
849 ** table for this index.
851 ** The value is a string composed of a list of integers describing
852 ** the index. The first integer in the list is the total number of
853 ** entries in the index. There is one additional integer in the list
854 ** for each indexed column. This additional integer is an estimate of
855 ** the number of rows matched by a stabbing query on the index using
856 ** a key with the corresponding number of fields. In other words,
857 ** if the index is on columns (a,b) and the sqlite_stat1 value is
858 ** "100 10 2", then SQLite estimates that:
860 ** * the index contains 100 rows,
861 ** * "WHERE a=?" matches 10 rows, and
862 ** * "WHERE a=? AND b=?" matches 2 rows.
864 ** If D is the count of distinct values and K is the total number of
865 ** rows, then each estimate is computed as:
872 char *zRet
= sqlite3MallocZero( (p
->nKeyCol
+1)*25 );
874 sqlite3_result_error_nomem(context
);
878 sqlite3_snprintf(24, zRet
, "%llu", (u64
)p
->nRow
);
879 z
= zRet
+ sqlite3Strlen30(zRet
);
880 for(i
=0; i
<p
->nKeyCol
; i
++){
881 u64 nDistinct
= p
->current
.anDLt
[i
] + 1;
882 u64 iVal
= (p
->nRow
+ nDistinct
- 1) / nDistinct
;
883 sqlite3_snprintf(24, z
, " %llu", iVal
);
884 z
+= sqlite3Strlen30(z
);
885 assert( p
->current
.anEq
[i
] );
887 assert( z
[0]=='\0' && z
>zRet
);
889 sqlite3_result_text(context
, zRet
, -1, sqlite3_free
);
891 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
892 else if( eCall
==STAT_GET_ROWID
){
894 samplePushPrevious(p
, 0);
897 if( p
->iGet
<p
->nSample
){
898 Stat4Sample
*pS
= p
->a
+ p
->iGet
;
900 sqlite3_result_int64(context
, pS
->u
.iRowid
);
902 sqlite3_result_blob(context
, pS
->u
.aRowid
, pS
->nRowid
,
909 assert( p
->iGet
<p
->nSample
);
911 case STAT_GET_NEQ
: aCnt
= p
->a
[p
->iGet
].anEq
; break;
912 case STAT_GET_NLT
: aCnt
= p
->a
[p
->iGet
].anLt
; break;
914 aCnt
= p
->a
[p
->iGet
].anDLt
;
921 sqlite3_result_int64(context
, (i64
)aCnt
[0]);
923 char *zRet
= sqlite3MallocZero(p
->nCol
* 25);
925 sqlite3_result_error_nomem(context
);
929 for(i
=0; i
<p
->nCol
; i
++){
930 sqlite3_snprintf(24, z
, "%llu ", (u64
)aCnt
[i
]);
931 z
+= sqlite3Strlen30(z
);
933 assert( z
[0]=='\0' && z
>zRet
);
935 sqlite3_result_text(context
, zRet
, -1, sqlite3_free
);
939 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
941 UNUSED_PARAMETER( argc
);
944 static const FuncDef statGetFuncdef
= {
945 1+IsStat34
, /* nArg */
946 SQLITE_UTF8
, /* funcFlags */
949 statGet
, /* xSFunc */
951 "stat_get", /* zName */
955 static void callStatGet(Vdbe
*v
, int regStat4
, int iParam
, int regOut
){
956 assert( regOut
!=regStat4
&& regOut
!=regStat4
+1 );
957 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
958 sqlite3VdbeAddOp2(v
, OP_Integer
, iParam
, regStat4
+1);
960 assert( iParam
==STAT_GET_STAT1
);
962 UNUSED_PARAMETER( iParam
);
964 sqlite3VdbeAddOp4(v
, OP_Function0
, 0, regStat4
, regOut
,
965 (char*)&statGetFuncdef
, P4_FUNCDEF
);
966 sqlite3VdbeChangeP5(v
, 1 + IsStat34
);
970 ** Generate code to do an analysis of all indices associated with
973 static void analyzeOneTable(
974 Parse
*pParse
, /* Parser context */
975 Table
*pTab
, /* Table whose indices are to be analyzed */
976 Index
*pOnlyIdx
, /* If not NULL, only analyze this one index */
977 int iStatCur
, /* Index of VdbeCursor that writes the sqlite_stat1 table */
978 int iMem
, /* Available memory locations begin here */
979 int iTab
/* Next available cursor */
981 sqlite3
*db
= pParse
->db
; /* Database handle */
982 Index
*pIdx
; /* An index to being analyzed */
983 int iIdxCur
; /* Cursor open on index being analyzed */
984 int iTabCur
; /* Table cursor */
985 Vdbe
*v
; /* The virtual machine being built up */
986 int i
; /* Loop counter */
987 int jZeroRows
= -1; /* Jump from here if number of rows is zero */
988 int iDb
; /* Index of database containing pTab */
989 u8 needTableCnt
= 1; /* True to count the table */
990 int regNewRowid
= iMem
++; /* Rowid for the inserted record */
991 int regStat4
= iMem
++; /* Register to hold Stat4Accum object */
992 int regChng
= iMem
++; /* Index of changed index field */
993 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
994 int regRowid
= iMem
++; /* Rowid argument passed to stat_push() */
996 int regTemp
= iMem
++; /* Temporary use register */
997 int regTabname
= iMem
++; /* Register containing table name */
998 int regIdxname
= iMem
++; /* Register containing index name */
999 int regStat1
= iMem
++; /* Value for the stat column of sqlite_stat1 */
1000 int regPrev
= iMem
; /* MUST BE LAST (see below) */
1002 pParse
->nMem
= MAX(pParse
->nMem
, iMem
);
1003 v
= sqlite3GetVdbe(pParse
);
1004 if( v
==0 || NEVER(pTab
==0) ){
1007 if( pTab
->tnum
==0 ){
1008 /* Do not gather statistics on views or virtual tables */
1011 if( sqlite3_strlike("sqlite_%", pTab
->zName
, 0)==0 ){
1012 /* Do not gather statistics on system tables */
1015 assert( sqlite3BtreeHoldsAllMutexes(db
) );
1016 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
1018 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
1019 #ifndef SQLITE_OMIT_AUTHORIZATION
1020 if( sqlite3AuthCheck(pParse
, SQLITE_ANALYZE
, pTab
->zName
, 0,
1021 db
->aDb
[iDb
].zDbSName
) ){
1026 /* Establish a read-lock on the table at the shared-cache level.
1027 ** Open a read-only cursor on the table. Also allocate a cursor number
1028 ** to use for scanning indexes (iIdxCur). No index cursor is opened at
1029 ** this time though. */
1030 sqlite3TableLock(pParse
, iDb
, pTab
->tnum
, 0, pTab
->zName
);
1033 pParse
->nTab
= MAX(pParse
->nTab
, iTab
);
1034 sqlite3OpenTable(pParse
, iTabCur
, iDb
, pTab
, OP_OpenRead
);
1035 sqlite3VdbeLoadString(v
, regTabname
, pTab
->zName
);
1037 for(pIdx
=pTab
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
1038 int nCol
; /* Number of columns in pIdx. "N" */
1039 int addrRewind
; /* Address of "OP_Rewind iIdxCur" */
1040 int addrNextRow
; /* Address of "next_row:" */
1041 const char *zIdxName
; /* Name of the index */
1042 int nColTest
; /* Number of columns to test for changes */
1044 if( pOnlyIdx
&& pOnlyIdx
!=pIdx
) continue;
1045 if( pIdx
->pPartIdxWhere
==0 ) needTableCnt
= 0;
1046 if( !HasRowid(pTab
) && IsPrimaryKeyIndex(pIdx
) ){
1047 nCol
= pIdx
->nKeyCol
;
1048 zIdxName
= pTab
->zName
;
1049 nColTest
= nCol
- 1;
1051 nCol
= pIdx
->nColumn
;
1052 zIdxName
= pIdx
->zName
;
1053 nColTest
= pIdx
->uniqNotNull
? pIdx
->nKeyCol
-1 : nCol
-1;
1056 /* Populate the register containing the index name. */
1057 sqlite3VdbeLoadString(v
, regIdxname
, zIdxName
);
1058 VdbeComment((v
, "Analysis for %s.%s", pTab
->zName
, zIdxName
));
1061 ** Pseudo-code for loop that calls stat_push():
1064 ** if eof(csr) goto end_of_scan;
1066 ** goto chng_addr_0;
1070 ** if( idx(0) != regPrev(0) ) goto chng_addr_0
1072 ** if( idx(1) != regPrev(1) ) goto chng_addr_1
1078 ** regPrev(0) = idx(0)
1080 ** regPrev(1) = idx(1)
1084 ** regRowid = idx(rowid)
1085 ** stat_push(P, regChng, regRowid)
1087 ** if !eof(csr) goto next_row;
1092 /* Make sure there are enough memory cells allocated to accommodate
1093 ** the regPrev array and a trailing rowid (the rowid slot is required
1094 ** when building a record to insert into the sample column of
1095 ** the sqlite_stat4 table. */
1096 pParse
->nMem
= MAX(pParse
->nMem
, regPrev
+nColTest
);
1098 /* Open a read-only cursor on the index being analyzed. */
1099 assert( iDb
==sqlite3SchemaToIndex(db
, pIdx
->pSchema
) );
1100 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iIdxCur
, pIdx
->tnum
, iDb
);
1101 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
1102 VdbeComment((v
, "%s", pIdx
->zName
));
1104 /* Invoke the stat_init() function. The arguments are:
1106 ** (1) the number of columns in the index including the rowid
1107 ** (or for a WITHOUT ROWID table, the number of PK columns),
1108 ** (2) the number of columns in the key without the rowid/pk
1109 ** (3) the number of rows in the index,
1112 ** The third argument is only used for STAT3 and STAT4
1114 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1115 sqlite3VdbeAddOp2(v
, OP_Count
, iIdxCur
, regStat4
+3);
1117 sqlite3VdbeAddOp2(v
, OP_Integer
, nCol
, regStat4
+1);
1118 sqlite3VdbeAddOp2(v
, OP_Integer
, pIdx
->nKeyCol
, regStat4
+2);
1119 sqlite3VdbeAddOp4(v
, OP_Function0
, 0, regStat4
+1, regStat4
,
1120 (char*)&statInitFuncdef
, P4_FUNCDEF
);
1121 sqlite3VdbeChangeP5(v
, 2+IsStat34
);
1123 /* Implementation of the following:
1126 ** if eof(csr) goto end_of_scan;
1128 ** goto next_push_0;
1131 addrRewind
= sqlite3VdbeAddOp1(v
, OP_Rewind
, iIdxCur
);
1133 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regChng
);
1134 addrNextRow
= sqlite3VdbeCurrentAddr(v
);
1137 int endDistinctTest
= sqlite3VdbeMakeLabel(v
);
1138 int *aGotoChng
; /* Array of jump instruction addresses */
1139 aGotoChng
= sqlite3DbMallocRawNN(db
, sizeof(int)*nColTest
);
1140 if( aGotoChng
==0 ) continue;
1145 ** if( idx(0) != regPrev(0) ) goto chng_addr_0
1147 ** if( idx(1) != regPrev(1) ) goto chng_addr_1
1150 ** goto endDistinctTest
1152 sqlite3VdbeAddOp0(v
, OP_Goto
);
1153 addrNextRow
= sqlite3VdbeCurrentAddr(v
);
1154 if( nColTest
==1 && pIdx
->nKeyCol
==1 && IsUniqueIndex(pIdx
) ){
1155 /* For a single-column UNIQUE index, once we have found a non-NULL
1156 ** row, we know that all the rest will be distinct, so skip
1157 ** subsequent distinctness tests. */
1158 sqlite3VdbeAddOp2(v
, OP_NotNull
, regPrev
, endDistinctTest
);
1161 for(i
=0; i
<nColTest
; i
++){
1162 char *pColl
= (char*)sqlite3LocateCollSeq(pParse
, pIdx
->azColl
[i
]);
1163 sqlite3VdbeAddOp2(v
, OP_Integer
, i
, regChng
);
1164 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, i
, regTemp
);
1166 sqlite3VdbeAddOp4(v
, OP_Ne
, regTemp
, 0, regPrev
+i
, pColl
, P4_COLLSEQ
);
1167 sqlite3VdbeChangeP5(v
, SQLITE_NULLEQ
);
1170 sqlite3VdbeAddOp2(v
, OP_Integer
, nColTest
, regChng
);
1171 sqlite3VdbeGoto(v
, endDistinctTest
);
1176 ** regPrev(0) = idx(0)
1178 ** regPrev(1) = idx(1)
1181 sqlite3VdbeJumpHere(v
, addrNextRow
-1);
1182 for(i
=0; i
<nColTest
; i
++){
1183 sqlite3VdbeJumpHere(v
, aGotoChng
[i
]);
1184 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, i
, regPrev
+i
);
1186 sqlite3VdbeResolveLabel(v
, endDistinctTest
);
1187 sqlite3DbFree(db
, aGotoChng
);
1192 ** regRowid = idx(rowid) // STAT34 only
1193 ** stat_push(P, regChng, regRowid) // 3rd parameter STAT34 only
1195 ** if !eof(csr) goto next_row;
1197 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1198 assert( regRowid
==(regStat4
+2) );
1199 if( HasRowid(pTab
) ){
1200 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iIdxCur
, regRowid
);
1202 Index
*pPk
= sqlite3PrimaryKeyIndex(pIdx
->pTable
);
1204 regKey
= sqlite3GetTempRange(pParse
, pPk
->nKeyCol
);
1205 for(j
=0; j
<pPk
->nKeyCol
; j
++){
1206 k
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[j
]);
1207 assert( k
>=0 && k
<pIdx
->nColumn
);
1208 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, k
, regKey
+j
);
1209 VdbeComment((v
, "%s", pTab
->aCol
[pPk
->aiColumn
[j
]].zName
));
1211 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regKey
, pPk
->nKeyCol
, regRowid
);
1212 sqlite3ReleaseTempRange(pParse
, regKey
, pPk
->nKeyCol
);
1215 assert( regChng
==(regStat4
+1) );
1216 sqlite3VdbeAddOp4(v
, OP_Function0
, 1, regStat4
, regTemp
,
1217 (char*)&statPushFuncdef
, P4_FUNCDEF
);
1218 sqlite3VdbeChangeP5(v
, 2+IsStat34
);
1219 sqlite3VdbeAddOp2(v
, OP_Next
, iIdxCur
, addrNextRow
); VdbeCoverage(v
);
1221 /* Add the entry to the stat1 table. */
1222 callStatGet(v
, regStat4
, STAT_GET_STAT1
, regStat1
);
1223 assert( "BBB"[0]==SQLITE_AFF_TEXT
);
1224 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regTabname
, 3, regTemp
, "BBB", 0);
1225 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iStatCur
, regNewRowid
);
1226 sqlite3VdbeAddOp3(v
, OP_Insert
, iStatCur
, regTemp
, regNewRowid
);
1227 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
1229 /* Add the entries to the stat3 or stat4 table. */
1230 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1232 int regEq
= regStat1
;
1233 int regLt
= regStat1
+1;
1234 int regDLt
= regStat1
+2;
1235 int regSample
= regStat1
+3;
1236 int regCol
= regStat1
+4;
1237 int regSampleRowid
= regCol
+ nCol
;
1240 u8 seekOp
= HasRowid(pTab
) ? OP_NotExists
: OP_NotFound
;
1242 pParse
->nMem
= MAX(pParse
->nMem
, regCol
+nCol
);
1244 addrNext
= sqlite3VdbeCurrentAddr(v
);
1245 callStatGet(v
, regStat4
, STAT_GET_ROWID
, regSampleRowid
);
1246 addrIsNull
= sqlite3VdbeAddOp1(v
, OP_IsNull
, regSampleRowid
);
1248 callStatGet(v
, regStat4
, STAT_GET_NEQ
, regEq
);
1249 callStatGet(v
, regStat4
, STAT_GET_NLT
, regLt
);
1250 callStatGet(v
, regStat4
, STAT_GET_NDLT
, regDLt
);
1251 sqlite3VdbeAddOp4Int(v
, seekOp
, iTabCur
, addrNext
, regSampleRowid
, 0);
1252 /* We know that the regSampleRowid row exists because it was read by
1253 ** the previous loop. Thus the not-found jump of seekOp will never
1255 VdbeCoverageNeverTaken(v
);
1256 #ifdef SQLITE_ENABLE_STAT3
1257 sqlite3ExprCodeLoadIndexColumn(pParse
, pIdx
, iTabCur
, 0, regSample
);
1259 for(i
=0; i
<nCol
; i
++){
1260 sqlite3ExprCodeLoadIndexColumn(pParse
, pIdx
, iTabCur
, i
, regCol
+i
);
1262 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regCol
, nCol
, regSample
);
1264 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, regTabname
, 6, regTemp
);
1265 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iStatCur
+1, regNewRowid
);
1266 sqlite3VdbeAddOp3(v
, OP_Insert
, iStatCur
+1, regTemp
, regNewRowid
);
1267 sqlite3VdbeAddOp2(v
, OP_Goto
, 1, addrNext
); /* P1==1 for end-of-loop */
1268 sqlite3VdbeJumpHere(v
, addrIsNull
);
1270 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
1272 /* End of analysis */
1273 sqlite3VdbeJumpHere(v
, addrRewind
);
1277 /* Create a single sqlite_stat1 entry containing NULL as the index
1278 ** name and the row count as the content.
1280 if( pOnlyIdx
==0 && needTableCnt
){
1281 VdbeComment((v
, "%s", pTab
->zName
));
1282 sqlite3VdbeAddOp2(v
, OP_Count
, iTabCur
, regStat1
);
1283 jZeroRows
= sqlite3VdbeAddOp1(v
, OP_IfNot
, regStat1
); VdbeCoverage(v
);
1284 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regIdxname
);
1285 assert( "BBB"[0]==SQLITE_AFF_TEXT
);
1286 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regTabname
, 3, regTemp
, "BBB", 0);
1287 sqlite3VdbeAddOp2(v
, OP_NewRowid
, iStatCur
, regNewRowid
);
1288 sqlite3VdbeAddOp3(v
, OP_Insert
, iStatCur
, regTemp
, regNewRowid
);
1289 sqlite3VdbeChangeP5(v
, OPFLAG_APPEND
);
1290 sqlite3VdbeJumpHere(v
, jZeroRows
);
1296 ** Generate code that will cause the most recent index analysis to
1297 ** be loaded into internal hash tables where is can be used.
1299 static void loadAnalysis(Parse
*pParse
, int iDb
){
1300 Vdbe
*v
= sqlite3GetVdbe(pParse
);
1302 sqlite3VdbeAddOp1(v
, OP_LoadAnalysis
, iDb
);
1307 ** Generate code that will do an analysis of an entire database
1309 static void analyzeDatabase(Parse
*pParse
, int iDb
){
1310 sqlite3
*db
= pParse
->db
;
1311 Schema
*pSchema
= db
->aDb
[iDb
].pSchema
; /* Schema of database iDb */
1317 sqlite3BeginWriteOperation(pParse
, 0, iDb
);
1318 iStatCur
= pParse
->nTab
;
1320 openStatTable(pParse
, iDb
, iStatCur
, 0, 0);
1321 iMem
= pParse
->nMem
+1;
1322 iTab
= pParse
->nTab
;
1323 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
1324 for(k
=sqliteHashFirst(&pSchema
->tblHash
); k
; k
=sqliteHashNext(k
)){
1325 Table
*pTab
= (Table
*)sqliteHashData(k
);
1326 analyzeOneTable(pParse
, pTab
, 0, iStatCur
, iMem
, iTab
);
1328 loadAnalysis(pParse
, iDb
);
1332 ** Generate code that will do an analysis of a single table in
1333 ** a database. If pOnlyIdx is not NULL then it is a single index
1334 ** in pTab that should be analyzed.
1336 static void analyzeTable(Parse
*pParse
, Table
*pTab
, Index
*pOnlyIdx
){
1341 assert( sqlite3BtreeHoldsAllMutexes(pParse
->db
) );
1342 iDb
= sqlite3SchemaToIndex(pParse
->db
, pTab
->pSchema
);
1343 sqlite3BeginWriteOperation(pParse
, 0, iDb
);
1344 iStatCur
= pParse
->nTab
;
1347 openStatTable(pParse
, iDb
, iStatCur
, pOnlyIdx
->zName
, "idx");
1349 openStatTable(pParse
, iDb
, iStatCur
, pTab
->zName
, "tbl");
1351 analyzeOneTable(pParse
, pTab
, pOnlyIdx
, iStatCur
,pParse
->nMem
+1,pParse
->nTab
);
1352 loadAnalysis(pParse
, iDb
);
1356 ** Generate code for the ANALYZE command. The parser calls this routine
1357 ** when it recognizes an ANALYZE command.
1360 ** ANALYZE <database> -- 2
1361 ** ANALYZE ?<database>.?<tablename> -- 3
1363 ** Form 1 causes all indices in all attached databases to be analyzed.
1364 ** Form 2 analyzes all indices the single database named.
1365 ** Form 3 analyzes all indices associated with the named table.
1367 void sqlite3Analyze(Parse
*pParse
, Token
*pName1
, Token
*pName2
){
1368 sqlite3
*db
= pParse
->db
;
1377 /* Read the database schema. If an error occurs, leave an error message
1378 ** and code in pParse and return NULL. */
1379 assert( sqlite3BtreeHoldsAllMutexes(pParse
->db
) );
1380 if( SQLITE_OK
!=sqlite3ReadSchema(pParse
) ){
1384 assert( pName2
!=0 || pName1
==0 );
1386 /* Form 1: Analyze everything */
1387 for(i
=0; i
<db
->nDb
; i
++){
1388 if( i
==1 ) continue; /* Do not analyze the TEMP database */
1389 analyzeDatabase(pParse
, i
);
1391 }else if( pName2
->n
==0 && (iDb
= sqlite3FindDb(db
, pName1
))>=0 ){
1392 /* Analyze the schema named as the argument */
1393 analyzeDatabase(pParse
, iDb
);
1395 /* Form 3: Analyze the table or index named as an argument */
1396 iDb
= sqlite3TwoPartName(pParse
, pName1
, pName2
, &pTableName
);
1398 zDb
= pName2
->n
? db
->aDb
[iDb
].zDbSName
: 0;
1399 z
= sqlite3NameFromToken(db
, pTableName
);
1401 if( (pIdx
= sqlite3FindIndex(db
, z
, zDb
))!=0 ){
1402 analyzeTable(pParse
, pIdx
->pTable
, pIdx
);
1403 }else if( (pTab
= sqlite3LocateTable(pParse
, 0, z
, zDb
))!=0 ){
1404 analyzeTable(pParse
, pTab
, 0);
1406 sqlite3DbFree(db
, z
);
1410 if( db
->nSqlExec
==0 && (v
= sqlite3GetVdbe(pParse
))!=0 ){
1411 sqlite3VdbeAddOp0(v
, OP_Expire
);
1416 ** Used to pass information from the analyzer reader through to the
1417 ** callback routine.
1419 typedef struct analysisInfo analysisInfo
;
1420 struct analysisInfo
{
1422 const char *zDatabase
;
1426 ** The first argument points to a nul-terminated string containing a
1427 ** list of space separated integers. Read the first nOut of these into
1428 ** the array aOut[].
1430 static void decodeIntArray(
1431 char *zIntArray
, /* String containing int array to decode */
1432 int nOut
, /* Number of slots in aOut[] */
1433 tRowcnt
*aOut
, /* Store integers here */
1434 LogEst
*aLog
, /* Or, if aOut==0, here */
1435 Index
*pIndex
/* Handle extra flags for this index, if not NULL */
1437 char *z
= zIntArray
;
1442 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1447 for(i
=0; *z
&& i
<nOut
; i
++){
1449 while( (c
=z
[0])>='0' && c
<='9' ){
1453 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1454 if( aOut
) aOut
[i
] = v
;
1455 if( aLog
) aLog
[i
] = sqlite3LogEst(v
);
1458 UNUSED_PARAMETER(aOut
);
1460 aLog
[i
] = sqlite3LogEst(v
);
1464 #ifndef SQLITE_ENABLE_STAT3_OR_STAT4
1465 assert( pIndex
!=0 ); {
1469 pIndex
->bUnordered
= 0;
1470 pIndex
->noSkipScan
= 0;
1472 if( sqlite3_strglob("unordered*", z
)==0 ){
1473 pIndex
->bUnordered
= 1;
1474 }else if( sqlite3_strglob("sz=[0-9]*", z
)==0 ){
1475 pIndex
->szIdxRow
= sqlite3LogEst(sqlite3Atoi(z
+3));
1476 }else if( sqlite3_strglob("noskipscan*", z
)==0 ){
1477 pIndex
->noSkipScan
= 1;
1479 #ifdef SQLITE_ENABLE_COSTMULT
1480 else if( sqlite3_strglob("costmult=[0-9]*",z
)==0 ){
1481 pIndex
->pTable
->costMult
= sqlite3LogEst(sqlite3Atoi(z
+9));
1484 while( z
[0]!=0 && z
[0]!=' ' ) z
++;
1485 while( z
[0]==' ' ) z
++;
1491 ** This callback is invoked once for each index when reading the
1492 ** sqlite_stat1 table.
1494 ** argv[0] = name of the table
1495 ** argv[1] = name of the index (might be NULL)
1496 ** argv[2] = results of analysis - on integer for each column
1498 ** Entries for which argv[1]==NULL simply record the number of rows in
1501 static int analysisLoader(void *pData
, int argc
, char **argv
, char **NotUsed
){
1502 analysisInfo
*pInfo
= (analysisInfo
*)pData
;
1508 UNUSED_PARAMETER2(NotUsed
, argc
);
1510 if( argv
==0 || argv
[0]==0 || argv
[2]==0 ){
1513 pTable
= sqlite3FindTable(pInfo
->db
, argv
[0], pInfo
->zDatabase
);
1519 }else if( sqlite3_stricmp(argv
[0],argv
[1])==0 ){
1520 pIndex
= sqlite3PrimaryKeyIndex(pTable
);
1522 pIndex
= sqlite3FindIndex(pInfo
->db
, argv
[1], pInfo
->zDatabase
);
1527 tRowcnt
*aiRowEst
= 0;
1528 int nCol
= pIndex
->nKeyCol
+1;
1529 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1530 /* Index.aiRowEst may already be set here if there are duplicate
1531 ** sqlite_stat1 entries for this index. In that case just clobber
1532 ** the old data with the new instead of allocating a new array. */
1533 if( pIndex
->aiRowEst
==0 ){
1534 pIndex
->aiRowEst
= (tRowcnt
*)sqlite3MallocZero(sizeof(tRowcnt
) * nCol
);
1535 if( pIndex
->aiRowEst
==0 ) sqlite3OomFault(pInfo
->db
);
1537 aiRowEst
= pIndex
->aiRowEst
;
1539 pIndex
->bUnordered
= 0;
1540 decodeIntArray((char*)z
, nCol
, aiRowEst
, pIndex
->aiRowLogEst
, pIndex
);
1541 pIndex
->hasStat1
= 1;
1542 if( pIndex
->pPartIdxWhere
==0 ){
1543 pTable
->nRowLogEst
= pIndex
->aiRowLogEst
[0];
1544 pTable
->tabFlags
|= TF_HasStat1
;
1548 fakeIdx
.szIdxRow
= pTable
->szTabRow
;
1549 #ifdef SQLITE_ENABLE_COSTMULT
1550 fakeIdx
.pTable
= pTable
;
1552 decodeIntArray((char*)z
, 1, 0, &pTable
->nRowLogEst
, &fakeIdx
);
1553 pTable
->szTabRow
= fakeIdx
.szIdxRow
;
1554 pTable
->tabFlags
|= TF_HasStat1
;
1561 ** If the Index.aSample variable is not NULL, delete the aSample[] array
1562 ** and its contents.
1564 void sqlite3DeleteIndexSamples(sqlite3
*db
, Index
*pIdx
){
1565 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1566 if( pIdx
->aSample
){
1568 for(j
=0; j
<pIdx
->nSample
; j
++){
1569 IndexSample
*p
= &pIdx
->aSample
[j
];
1570 sqlite3DbFree(db
, p
->p
);
1572 sqlite3DbFree(db
, pIdx
->aSample
);
1574 if( db
&& db
->pnBytesFreed
==0 ){
1579 UNUSED_PARAMETER(db
);
1580 UNUSED_PARAMETER(pIdx
);
1581 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
1584 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1586 ** Populate the pIdx->aAvgEq[] array based on the samples currently
1587 ** stored in pIdx->aSample[].
1589 static void initAvgEq(Index
*pIdx
){
1591 IndexSample
*aSample
= pIdx
->aSample
;
1592 IndexSample
*pFinal
= &aSample
[pIdx
->nSample
-1];
1595 if( pIdx
->nSampleCol
>1 ){
1596 /* If this is stat4 data, then calculate aAvgEq[] values for all
1597 ** sample columns except the last. The last is always set to 1, as
1598 ** once the trailing PK fields are considered all index keys are
1600 nCol
= pIdx
->nSampleCol
-1;
1601 pIdx
->aAvgEq
[nCol
] = 1;
1603 for(iCol
=0; iCol
<nCol
; iCol
++){
1604 int nSample
= pIdx
->nSample
;
1605 int i
; /* Used to iterate through samples */
1606 tRowcnt sumEq
= 0; /* Sum of the nEq values */
1608 tRowcnt nRow
; /* Number of rows in index */
1609 i64 nSum100
= 0; /* Number of terms contributing to sumEq */
1610 i64 nDist100
; /* Number of distinct values in index */
1612 if( !pIdx
->aiRowEst
|| iCol
>=pIdx
->nKeyCol
|| pIdx
->aiRowEst
[iCol
+1]==0 ){
1613 nRow
= pFinal
->anLt
[iCol
];
1614 nDist100
= (i64
)100 * pFinal
->anDLt
[iCol
];
1617 nRow
= pIdx
->aiRowEst
[0];
1618 nDist100
= ((i64
)100 * pIdx
->aiRowEst
[0]) / pIdx
->aiRowEst
[iCol
+1];
1620 pIdx
->nRowEst0
= nRow
;
1622 /* Set nSum to the number of distinct (iCol+1) field prefixes that
1623 ** occur in the stat4 table for this index. Set sumEq to the sum of
1624 ** the nEq values for column iCol for the same set (adding the value
1625 ** only once where there exist duplicate prefixes). */
1626 for(i
=0; i
<nSample
; i
++){
1627 if( i
==(pIdx
->nSample
-1)
1628 || aSample
[i
].anDLt
[iCol
]!=aSample
[i
+1].anDLt
[iCol
]
1630 sumEq
+= aSample
[i
].anEq
[iCol
];
1635 if( nDist100
>nSum100
&& sumEq
<nRow
){
1636 avgEq
= ((i64
)100 * (nRow
- sumEq
))/(nDist100
- nSum100
);
1638 if( avgEq
==0 ) avgEq
= 1;
1639 pIdx
->aAvgEq
[iCol
] = avgEq
;
1645 ** Look up an index by name. Or, if the name of a WITHOUT ROWID table
1646 ** is supplied instead, find the PRIMARY KEY index for that table.
1648 static Index
*findIndexOrPrimaryKey(
1653 Index
*pIdx
= sqlite3FindIndex(db
, zName
, zDb
);
1655 Table
*pTab
= sqlite3FindTable(db
, zName
, zDb
);
1656 if( pTab
&& !HasRowid(pTab
) ) pIdx
= sqlite3PrimaryKeyIndex(pTab
);
1662 ** Load the content from either the sqlite_stat4 or sqlite_stat3 table
1663 ** into the relevant Index.aSample[] arrays.
1665 ** Arguments zSql1 and zSql2 must point to SQL statements that return
1666 ** data equivalent to the following (statements are different for stat3,
1667 ** see the caller of this function for details):
1669 ** zSql1: SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx
1670 ** zSql2: SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4
1672 ** where %Q is replaced with the database name before the SQL is executed.
1674 static int loadStatTbl(
1675 sqlite3
*db
, /* Database handle */
1676 int bStat3
, /* Assume single column records only */
1677 const char *zSql1
, /* SQL statement 1 (see above) */
1678 const char *zSql2
, /* SQL statement 2 (see above) */
1679 const char *zDb
/* Database name (e.g. "main") */
1681 int rc
; /* Result codes from subroutines */
1682 sqlite3_stmt
*pStmt
= 0; /* An SQL statement being run */
1683 char *zSql
; /* Text of the SQL statement */
1684 Index
*pPrevIdx
= 0; /* Previous index in the loop */
1685 IndexSample
*pSample
; /* A slot in pIdx->aSample[] */
1687 assert( db
->lookaside
.bDisable
);
1688 zSql
= sqlite3MPrintf(db
, zSql1
, zDb
);
1690 return SQLITE_NOMEM_BKPT
;
1692 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
1693 sqlite3DbFree(db
, zSql
);
1696 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
1697 int nIdxCol
= 1; /* Number of columns in stat4 records */
1699 char *zIndex
; /* Index name */
1700 Index
*pIdx
; /* Pointer to the index object */
1701 int nSample
; /* Number of samples */
1702 int nByte
; /* Bytes of space required */
1703 int i
; /* Bytes of space required */
1706 zIndex
= (char *)sqlite3_column_text(pStmt
, 0);
1707 if( zIndex
==0 ) continue;
1708 nSample
= sqlite3_column_int(pStmt
, 1);
1709 pIdx
= findIndexOrPrimaryKey(db
, zIndex
, zDb
);
1710 assert( pIdx
==0 || bStat3
|| pIdx
->nSample
==0 );
1711 /* Index.nSample is non-zero at this point if data has already been
1712 ** loaded from the stat4 table. In this case ignore stat3 data. */
1713 if( pIdx
==0 || pIdx
->nSample
) continue;
1715 assert( !HasRowid(pIdx
->pTable
) || pIdx
->nColumn
==pIdx
->nKeyCol
+1 );
1716 if( !HasRowid(pIdx
->pTable
) && IsPrimaryKeyIndex(pIdx
) ){
1717 nIdxCol
= pIdx
->nKeyCol
;
1719 nIdxCol
= pIdx
->nColumn
;
1722 pIdx
->nSampleCol
= nIdxCol
;
1723 nByte
= sizeof(IndexSample
) * nSample
;
1724 nByte
+= sizeof(tRowcnt
) * nIdxCol
* 3 * nSample
;
1725 nByte
+= nIdxCol
* sizeof(tRowcnt
); /* Space for Index.aAvgEq[] */
1727 pIdx
->aSample
= sqlite3DbMallocZero(db
, nByte
);
1728 if( pIdx
->aSample
==0 ){
1729 sqlite3_finalize(pStmt
);
1730 return SQLITE_NOMEM_BKPT
;
1732 pSpace
= (tRowcnt
*)&pIdx
->aSample
[nSample
];
1733 pIdx
->aAvgEq
= pSpace
; pSpace
+= nIdxCol
;
1734 for(i
=0; i
<nSample
; i
++){
1735 pIdx
->aSample
[i
].anEq
= pSpace
; pSpace
+= nIdxCol
;
1736 pIdx
->aSample
[i
].anLt
= pSpace
; pSpace
+= nIdxCol
;
1737 pIdx
->aSample
[i
].anDLt
= pSpace
; pSpace
+= nIdxCol
;
1739 assert( ((u8
*)pSpace
)-nByte
==(u8
*)(pIdx
->aSample
) );
1741 rc
= sqlite3_finalize(pStmt
);
1744 zSql
= sqlite3MPrintf(db
, zSql2
, zDb
);
1746 return SQLITE_NOMEM_BKPT
;
1748 rc
= sqlite3_prepare(db
, zSql
, -1, &pStmt
, 0);
1749 sqlite3DbFree(db
, zSql
);
1752 while( sqlite3_step(pStmt
)==SQLITE_ROW
){
1753 char *zIndex
; /* Index name */
1754 Index
*pIdx
; /* Pointer to the index object */
1755 int nCol
= 1; /* Number of columns in index */
1757 zIndex
= (char *)sqlite3_column_text(pStmt
, 0);
1758 if( zIndex
==0 ) continue;
1759 pIdx
= findIndexOrPrimaryKey(db
, zIndex
, zDb
);
1760 if( pIdx
==0 ) continue;
1761 /* This next condition is true if data has already been loaded from
1762 ** the sqlite_stat4 table. In this case ignore stat3 data. */
1763 nCol
= pIdx
->nSampleCol
;
1764 if( bStat3
&& nCol
>1 ) continue;
1765 if( pIdx
!=pPrevIdx
){
1766 initAvgEq(pPrevIdx
);
1769 pSample
= &pIdx
->aSample
[pIdx
->nSample
];
1770 decodeIntArray((char*)sqlite3_column_text(pStmt
,1),nCol
,pSample
->anEq
,0,0);
1771 decodeIntArray((char*)sqlite3_column_text(pStmt
,2),nCol
,pSample
->anLt
,0,0);
1772 decodeIntArray((char*)sqlite3_column_text(pStmt
,3),nCol
,pSample
->anDLt
,0,0);
1774 /* Take a copy of the sample. Add two 0x00 bytes the end of the buffer.
1775 ** This is in case the sample record is corrupted. In that case, the
1776 ** sqlite3VdbeRecordCompare() may read up to two varints past the
1777 ** end of the allocated buffer before it realizes it is dealing with
1778 ** a corrupt record. Adding the two 0x00 bytes prevents this from causing
1779 ** a buffer overread. */
1780 pSample
->n
= sqlite3_column_bytes(pStmt
, 4);
1781 pSample
->p
= sqlite3DbMallocZero(db
, pSample
->n
+ 2);
1782 if( pSample
->p
==0 ){
1783 sqlite3_finalize(pStmt
);
1784 return SQLITE_NOMEM_BKPT
;
1787 memcpy(pSample
->p
, sqlite3_column_blob(pStmt
, 4), pSample
->n
);
1791 rc
= sqlite3_finalize(pStmt
);
1792 if( rc
==SQLITE_OK
) initAvgEq(pPrevIdx
);
1797 ** Load content from the sqlite_stat4 and sqlite_stat3 tables into
1798 ** the Index.aSample[] arrays of all indices.
1800 static int loadStat4(sqlite3
*db
, const char *zDb
){
1801 int rc
= SQLITE_OK
; /* Result codes from subroutines */
1803 assert( db
->lookaside
.bDisable
);
1804 if( sqlite3FindTable(db
, "sqlite_stat4", zDb
) ){
1805 rc
= loadStatTbl(db
, 0,
1806 "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx",
1807 "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4",
1812 if( rc
==SQLITE_OK
&& sqlite3FindTable(db
, "sqlite_stat3", zDb
) ){
1813 rc
= loadStatTbl(db
, 1,
1814 "SELECT idx,count(*) FROM %Q.sqlite_stat3 GROUP BY idx",
1815 "SELECT idx,neq,nlt,ndlt,sqlite_record(sample) FROM %Q.sqlite_stat3",
1822 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
1825 ** Load the content of the sqlite_stat1 and sqlite_stat3/4 tables. The
1826 ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[]
1827 ** arrays. The contents of sqlite_stat3/4 are used to populate the
1828 ** Index.aSample[] arrays.
1830 ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR
1831 ** is returned. In this case, even if SQLITE_ENABLE_STAT3/4 was defined
1832 ** during compilation and the sqlite_stat3/4 table is present, no data is
1835 ** If SQLITE_ENABLE_STAT3/4 was defined during compilation and the
1836 ** sqlite_stat4 table is not present in the database, SQLITE_ERROR is
1837 ** returned. However, in this case, data is read from the sqlite_stat1
1838 ** table (if it is present) before returning.
1840 ** If an OOM error occurs, this function always sets db->mallocFailed.
1841 ** This means if the caller does not care about other errors, the return
1842 ** code may be ignored.
1844 int sqlite3AnalysisLoad(sqlite3
*db
, int iDb
){
1849 Schema
*pSchema
= db
->aDb
[iDb
].pSchema
;
1851 assert( iDb
>=0 && iDb
<db
->nDb
);
1852 assert( db
->aDb
[iDb
].pBt
!=0 );
1854 /* Clear any prior statistics */
1855 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
1856 for(i
=sqliteHashFirst(&pSchema
->tblHash
); i
; i
=sqliteHashNext(i
)){
1857 Table
*pTab
= sqliteHashData(i
);
1858 pTab
->tabFlags
&= ~TF_HasStat1
;
1860 for(i
=sqliteHashFirst(&pSchema
->idxHash
); i
; i
=sqliteHashNext(i
)){
1861 Index
*pIdx
= sqliteHashData(i
);
1863 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1864 sqlite3DeleteIndexSamples(db
, pIdx
);
1869 /* Load new statistics out of the sqlite_stat1 table */
1871 sInfo
.zDatabase
= db
->aDb
[iDb
].zDbSName
;
1872 if( sqlite3FindTable(db
, "sqlite_stat1", sInfo
.zDatabase
)!=0 ){
1873 zSql
= sqlite3MPrintf(db
,
1874 "SELECT tbl,idx,stat FROM %Q.sqlite_stat1", sInfo
.zDatabase
);
1876 rc
= SQLITE_NOMEM_BKPT
;
1878 rc
= sqlite3_exec(db
, zSql
, analysisLoader
, &sInfo
, 0);
1879 sqlite3DbFree(db
, zSql
);
1883 /* Set appropriate defaults on all indexes not in the sqlite_stat1 table */
1884 assert( sqlite3SchemaMutexHeld(db
, iDb
, 0) );
1885 for(i
=sqliteHashFirst(&pSchema
->idxHash
); i
; i
=sqliteHashNext(i
)){
1886 Index
*pIdx
= sqliteHashData(i
);
1887 if( !pIdx
->hasStat1
) sqlite3DefaultRowEst(pIdx
);
1890 /* Load the statistics from the sqlite_stat4 table. */
1891 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
1892 if( rc
==SQLITE_OK
&& OptimizationEnabled(db
, SQLITE_Stat34
) ){
1893 db
->lookaside
.bDisable
++;
1894 rc
= loadStat4(db
, sInfo
.zDatabase
);
1895 db
->lookaside
.bDisable
--;
1897 for(i
=sqliteHashFirst(&pSchema
->idxHash
); i
; i
=sqliteHashNext(i
)){
1898 Index
*pIdx
= sqliteHashData(i
);
1899 sqlite3_free(pIdx
->aiRowEst
);
1904 if( rc
==SQLITE_NOMEM
){
1905 sqlite3OomFault(db
);
1911 #endif /* SQLITE_OMIT_ANALYZE */