Annotation of embedaddon/sqlite3/src/analyze.c, revision 1.1
1.1 ! misho 1: /*
! 2: ** 2005 July 8
! 3: **
! 4: ** The author disclaims copyright to this source code. In place of
! 5: ** a legal notice, here is a blessing:
! 6: **
! 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.
! 10: **
! 11: *************************************************************************
! 12: ** This file contains code associated with the ANALYZE command.
! 13: **
! 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.
! 17: **
! 18: ** The following system tables are or have been supported:
! 19: **
! 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: **
! 24: ** Additional tables might be added in future releases of SQLite.
! 25: ** The sqlite_stat2 table is not created or used unless the SQLite version
! 26: ** is between 3.6.18 and 3.7.8, inclusive, and unless SQLite is compiled
! 27: ** with SQLITE_ENABLE_STAT2. The sqlite_stat2 table is deprecated.
! 28: ** The sqlite_stat2 table is superceded by sqlite_stat3, which is only
! 29: ** created and used by SQLite versions 3.7.9 and later and with
! 30: ** SQLITE_ENABLE_STAT3 defined. The fucntionality of sqlite_stat3
! 31: ** is a superset of sqlite_stat2.
! 32: **
! 33: ** Format of sqlite_stat1:
! 34: **
! 35: ** There is normally one row per index, with the index identified by the
! 36: ** name in the idx column. The tbl column is the name of the table to
! 37: ** which the index belongs. In each such row, the stat column will be
! 38: ** a string consisting of a list of integers. The first integer in this
! 39: ** list is the number of rows in the index and in the table. The second
! 40: ** integer is the average number of rows in the index that have the same
! 41: ** value in the first column of the index. The third integer is the average
! 42: ** number of rows in the index that have the same value for the first two
! 43: ** columns. The N-th integer (for N>1) is the average number of rows in
! 44: ** the index which have the same value for the first N-1 columns. For
! 45: ** a K-column index, there will be K+1 integers in the stat column. If
! 46: ** the index is unique, then the last integer will be 1.
! 47: **
! 48: ** The list of integers in the stat column can optionally be followed
! 49: ** by the keyword "unordered". The "unordered" keyword, if it is present,
! 50: ** must be separated from the last integer by a single space. If the
! 51: ** "unordered" keyword is present, then the query planner assumes that
! 52: ** the index is unordered and will not use the index for a range query.
! 53: **
! 54: ** If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
! 55: ** column contains a single integer which is the (estimated) number of
! 56: ** rows in the table identified by sqlite_stat1.tbl.
! 57: **
! 58: ** Format of sqlite_stat2:
! 59: **
! 60: ** The sqlite_stat2 is only created and is only used if SQLite is compiled
! 61: ** with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
! 62: ** 3.6.18 and 3.7.8. The "stat2" table contains additional information
! 63: ** about the distribution of keys within an index. The index is identified by
! 64: ** the "idx" column and the "tbl" column is the name of the table to which
! 65: ** the index belongs. There are usually 10 rows in the sqlite_stat2
! 66: ** table for each index.
! 67: **
! 68: ** The sqlite_stat2 entries for an index that have sampleno between 0 and 9
! 69: ** inclusive are samples of the left-most key value in the index taken at
! 70: ** evenly spaced points along the index. Let the number of samples be S
! 71: ** (10 in the standard build) and let C be the number of rows in the index.
! 72: ** Then the sampled rows are given by:
! 73: **
! 74: ** rownumber = (i*C*2 + C)/(S*2)
! 75: **
! 76: ** For i between 0 and S-1. Conceptually, the index space is divided into
! 77: ** S uniform buckets and the samples are the middle row from each bucket.
! 78: **
! 79: ** The format for sqlite_stat2 is recorded here for legacy reference. This
! 80: ** version of SQLite does not support sqlite_stat2. It neither reads nor
! 81: ** writes the sqlite_stat2 table. This version of SQLite only supports
! 82: ** sqlite_stat3.
! 83: **
! 84: ** Format for sqlite_stat3:
! 85: **
! 86: ** The sqlite_stat3 is an enhancement to sqlite_stat2. A new name is
! 87: ** used to avoid compatibility problems.
! 88: **
! 89: ** The format of the sqlite_stat3 table is similar to the format of
! 90: ** the sqlite_stat2 table. There are multiple entries for each index.
! 91: ** The idx column names the index and the tbl column is the table of the
! 92: ** index. If the idx and tbl columns are the same, then the sample is
! 93: ** of the INTEGER PRIMARY KEY. The sample column is a value taken from
! 94: ** the left-most column of the index. The nEq column is the approximate
! 95: ** number of entires in the index whose left-most column exactly matches
! 96: ** the sample. nLt is the approximate number of entires whose left-most
! 97: ** column is less than the sample. The nDLt column is the approximate
! 98: ** number of distinct left-most entries in the index that are less than
! 99: ** the sample.
! 100: **
! 101: ** Future versions of SQLite might change to store a string containing
! 102: ** multiple integers values in the nDLt column of sqlite_stat3. The first
! 103: ** integer will be the number of prior index entires that are distinct in
! 104: ** the left-most column. The second integer will be the number of prior index
! 105: ** entries that are distinct in the first two columns. The third integer
! 106: ** will be the number of prior index entries that are distinct in the first
! 107: ** three columns. And so forth. With that extension, the nDLt field is
! 108: ** similar in function to the sqlite_stat1.stat field.
! 109: **
! 110: ** There can be an arbitrary number of sqlite_stat3 entries per index.
! 111: ** The ANALYZE command will typically generate sqlite_stat3 tables
! 112: ** that contain between 10 and 40 samples which are distributed across
! 113: ** the key space, though not uniformly, and which include samples with
! 114: ** largest possible nEq values.
! 115: */
! 116: #ifndef SQLITE_OMIT_ANALYZE
! 117: #include "sqliteInt.h"
! 118:
! 119: /*
! 120: ** This routine generates code that opens the sqlite_stat1 table for
! 121: ** writing with cursor iStatCur. If the library was built with the
! 122: ** SQLITE_ENABLE_STAT3 macro defined, then the sqlite_stat3 table is
! 123: ** opened for writing using cursor (iStatCur+1)
! 124: **
! 125: ** If the sqlite_stat1 tables does not previously exist, it is created.
! 126: ** Similarly, if the sqlite_stat3 table does not exist and the library
! 127: ** is compiled with SQLITE_ENABLE_STAT3 defined, it is created.
! 128: **
! 129: ** Argument zWhere may be a pointer to a buffer containing a table name,
! 130: ** or it may be a NULL pointer. If it is not NULL, then all entries in
! 131: ** the sqlite_stat1 and (if applicable) sqlite_stat3 tables associated
! 132: ** with the named table are deleted. If zWhere==0, then code is generated
! 133: ** to delete all stat table entries.
! 134: */
! 135: static void openStatTable(
! 136: Parse *pParse, /* Parsing context */
! 137: int iDb, /* The database we are looking in */
! 138: int iStatCur, /* Open the sqlite_stat1 table on this cursor */
! 139: const char *zWhere, /* Delete entries for this table or index */
! 140: const char *zWhereType /* Either "tbl" or "idx" */
! 141: ){
! 142: static const struct {
! 143: const char *zName;
! 144: const char *zCols;
! 145: } aTable[] = {
! 146: { "sqlite_stat1", "tbl,idx,stat" },
! 147: #ifdef SQLITE_ENABLE_STAT3
! 148: { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
! 149: #endif
! 150: };
! 151:
! 152: int aRoot[] = {0, 0};
! 153: u8 aCreateTbl[] = {0, 0};
! 154:
! 155: int i;
! 156: sqlite3 *db = pParse->db;
! 157: Db *pDb;
! 158: Vdbe *v = sqlite3GetVdbe(pParse);
! 159: if( v==0 ) return;
! 160: assert( sqlite3BtreeHoldsAllMutexes(db) );
! 161: assert( sqlite3VdbeDb(v)==db );
! 162: pDb = &db->aDb[iDb];
! 163:
! 164: /* Create new statistic tables if they do not exist, or clear them
! 165: ** if they do already exist.
! 166: */
! 167: for(i=0; i<ArraySize(aTable); i++){
! 168: const char *zTab = aTable[i].zName;
! 169: Table *pStat;
! 170: if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){
! 171: /* The sqlite_stat[12] table does not exist. Create it. Note that a
! 172: ** side-effect of the CREATE TABLE statement is to leave the rootpage
! 173: ** of the new table in register pParse->regRoot. This is important
! 174: ** because the OpenWrite opcode below will be needing it. */
! 175: sqlite3NestedParse(pParse,
! 176: "CREATE TABLE %Q.%s(%s)", pDb->zName, zTab, aTable[i].zCols
! 177: );
! 178: aRoot[i] = pParse->regRoot;
! 179: aCreateTbl[i] = 1;
! 180: }else{
! 181: /* The table already exists. If zWhere is not NULL, delete all entries
! 182: ** associated with the table zWhere. If zWhere is NULL, delete the
! 183: ** entire contents of the table. */
! 184: aRoot[i] = pStat->tnum;
! 185: sqlite3TableLock(pParse, iDb, aRoot[i], 1, zTab);
! 186: if( zWhere ){
! 187: sqlite3NestedParse(pParse,
! 188: "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere
! 189: );
! 190: }else{
! 191: /* The sqlite_stat[12] table already exists. Delete all rows. */
! 192: sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
! 193: }
! 194: }
! 195: }
! 196:
! 197: /* Open the sqlite_stat[13] tables for writing. */
! 198: for(i=0; i<ArraySize(aTable); i++){
! 199: sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
! 200: sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
! 201: sqlite3VdbeChangeP5(v, aCreateTbl[i]);
! 202: }
! 203: }
! 204:
! 205: /*
! 206: ** Recommended number of samples for sqlite_stat3
! 207: */
! 208: #ifndef SQLITE_STAT3_SAMPLES
! 209: # define SQLITE_STAT3_SAMPLES 24
! 210: #endif
! 211:
! 212: /*
! 213: ** Three SQL functions - stat3_init(), stat3_push(), and stat3_pop() -
! 214: ** share an instance of the following structure to hold their state
! 215: ** information.
! 216: */
! 217: typedef struct Stat3Accum Stat3Accum;
! 218: struct Stat3Accum {
! 219: tRowcnt nRow; /* Number of rows in the entire table */
! 220: tRowcnt nPSample; /* How often to do a periodic sample */
! 221: int iMin; /* Index of entry with minimum nEq and hash */
! 222: int mxSample; /* Maximum number of samples to accumulate */
! 223: int nSample; /* Current number of samples */
! 224: u32 iPrn; /* Pseudo-random number used for sampling */
! 225: struct Stat3Sample {
! 226: i64 iRowid; /* Rowid in main table of the key */
! 227: tRowcnt nEq; /* sqlite_stat3.nEq */
! 228: tRowcnt nLt; /* sqlite_stat3.nLt */
! 229: tRowcnt nDLt; /* sqlite_stat3.nDLt */
! 230: u8 isPSample; /* True if a periodic sample */
! 231: u32 iHash; /* Tiebreaker hash */
! 232: } *a; /* An array of samples */
! 233: };
! 234:
! 235: #ifdef SQLITE_ENABLE_STAT3
! 236: /*
! 237: ** Implementation of the stat3_init(C,S) SQL function. The two parameters
! 238: ** are the number of rows in the table or index (C) and the number of samples
! 239: ** to accumulate (S).
! 240: **
! 241: ** This routine allocates the Stat3Accum object.
! 242: **
! 243: ** The return value is the Stat3Accum object (P).
! 244: */
! 245: static void stat3Init(
! 246: sqlite3_context *context,
! 247: int argc,
! 248: sqlite3_value **argv
! 249: ){
! 250: Stat3Accum *p;
! 251: tRowcnt nRow;
! 252: int mxSample;
! 253: int n;
! 254:
! 255: UNUSED_PARAMETER(argc);
! 256: nRow = (tRowcnt)sqlite3_value_int64(argv[0]);
! 257: mxSample = sqlite3_value_int(argv[1]);
! 258: n = sizeof(*p) + sizeof(p->a[0])*mxSample;
! 259: p = sqlite3_malloc( n );
! 260: if( p==0 ){
! 261: sqlite3_result_error_nomem(context);
! 262: return;
! 263: }
! 264: memset(p, 0, n);
! 265: p->a = (struct Stat3Sample*)&p[1];
! 266: p->nRow = nRow;
! 267: p->mxSample = mxSample;
! 268: p->nPSample = p->nRow/(mxSample/3+1) + 1;
! 269: sqlite3_randomness(sizeof(p->iPrn), &p->iPrn);
! 270: sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
! 271: }
! 272: static const FuncDef stat3InitFuncdef = {
! 273: 2, /* nArg */
! 274: SQLITE_UTF8, /* iPrefEnc */
! 275: 0, /* flags */
! 276: 0, /* pUserData */
! 277: 0, /* pNext */
! 278: stat3Init, /* xFunc */
! 279: 0, /* xStep */
! 280: 0, /* xFinalize */
! 281: "stat3_init", /* zName */
! 282: 0, /* pHash */
! 283: 0 /* pDestructor */
! 284: };
! 285:
! 286:
! 287: /*
! 288: ** Implementation of the stat3_push(nEq,nLt,nDLt,rowid,P) SQL function. The
! 289: ** arguments describe a single key instance. This routine makes the
! 290: ** decision about whether or not to retain this key for the sqlite_stat3
! 291: ** table.
! 292: **
! 293: ** The return value is NULL.
! 294: */
! 295: static void stat3Push(
! 296: sqlite3_context *context,
! 297: int argc,
! 298: sqlite3_value **argv
! 299: ){
! 300: Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[4]);
! 301: tRowcnt nEq = sqlite3_value_int64(argv[0]);
! 302: tRowcnt nLt = sqlite3_value_int64(argv[1]);
! 303: tRowcnt nDLt = sqlite3_value_int64(argv[2]);
! 304: i64 rowid = sqlite3_value_int64(argv[3]);
! 305: u8 isPSample = 0;
! 306: u8 doInsert = 0;
! 307: int iMin = p->iMin;
! 308: struct Stat3Sample *pSample;
! 309: int i;
! 310: u32 h;
! 311:
! 312: UNUSED_PARAMETER(context);
! 313: UNUSED_PARAMETER(argc);
! 314: if( nEq==0 ) return;
! 315: h = p->iPrn = p->iPrn*1103515245 + 12345;
! 316: if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){
! 317: doInsert = isPSample = 1;
! 318: }else if( p->nSample<p->mxSample ){
! 319: doInsert = 1;
! 320: }else{
! 321: if( nEq>p->a[iMin].nEq || (nEq==p->a[iMin].nEq && h>p->a[iMin].iHash) ){
! 322: doInsert = 1;
! 323: }
! 324: }
! 325: if( !doInsert ) return;
! 326: if( p->nSample==p->mxSample ){
! 327: assert( p->nSample - iMin - 1 >= 0 );
! 328: memmove(&p->a[iMin], &p->a[iMin+1], sizeof(p->a[0])*(p->nSample-iMin-1));
! 329: pSample = &p->a[p->nSample-1];
! 330: }else{
! 331: pSample = &p->a[p->nSample++];
! 332: }
! 333: pSample->iRowid = rowid;
! 334: pSample->nEq = nEq;
! 335: pSample->nLt = nLt;
! 336: pSample->nDLt = nDLt;
! 337: pSample->iHash = h;
! 338: pSample->isPSample = isPSample;
! 339:
! 340: /* Find the new minimum */
! 341: if( p->nSample==p->mxSample ){
! 342: pSample = p->a;
! 343: i = 0;
! 344: while( pSample->isPSample ){
! 345: i++;
! 346: pSample++;
! 347: assert( i<p->nSample );
! 348: }
! 349: nEq = pSample->nEq;
! 350: h = pSample->iHash;
! 351: iMin = i;
! 352: for(i++, pSample++; i<p->nSample; i++, pSample++){
! 353: if( pSample->isPSample ) continue;
! 354: if( pSample->nEq<nEq
! 355: || (pSample->nEq==nEq && pSample->iHash<h)
! 356: ){
! 357: iMin = i;
! 358: nEq = pSample->nEq;
! 359: h = pSample->iHash;
! 360: }
! 361: }
! 362: p->iMin = iMin;
! 363: }
! 364: }
! 365: static const FuncDef stat3PushFuncdef = {
! 366: 5, /* nArg */
! 367: SQLITE_UTF8, /* iPrefEnc */
! 368: 0, /* flags */
! 369: 0, /* pUserData */
! 370: 0, /* pNext */
! 371: stat3Push, /* xFunc */
! 372: 0, /* xStep */
! 373: 0, /* xFinalize */
! 374: "stat3_push", /* zName */
! 375: 0, /* pHash */
! 376: 0 /* pDestructor */
! 377: };
! 378:
! 379: /*
! 380: ** Implementation of the stat3_get(P,N,...) SQL function. This routine is
! 381: ** used to query the results. Content is returned for the Nth sqlite_stat3
! 382: ** row where N is between 0 and S-1 and S is the number of samples. The
! 383: ** value returned depends on the number of arguments.
! 384: **
! 385: ** argc==2 result: rowid
! 386: ** argc==3 result: nEq
! 387: ** argc==4 result: nLt
! 388: ** argc==5 result: nDLt
! 389: */
! 390: static void stat3Get(
! 391: sqlite3_context *context,
! 392: int argc,
! 393: sqlite3_value **argv
! 394: ){
! 395: int n = sqlite3_value_int(argv[1]);
! 396: Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[0]);
! 397:
! 398: assert( p!=0 );
! 399: if( p->nSample<=n ) return;
! 400: switch( argc ){
! 401: case 2: sqlite3_result_int64(context, p->a[n].iRowid); break;
! 402: case 3: sqlite3_result_int64(context, p->a[n].nEq); break;
! 403: case 4: sqlite3_result_int64(context, p->a[n].nLt); break;
! 404: default: sqlite3_result_int64(context, p->a[n].nDLt); break;
! 405: }
! 406: }
! 407: static const FuncDef stat3GetFuncdef = {
! 408: -1, /* nArg */
! 409: SQLITE_UTF8, /* iPrefEnc */
! 410: 0, /* flags */
! 411: 0, /* pUserData */
! 412: 0, /* pNext */
! 413: stat3Get, /* xFunc */
! 414: 0, /* xStep */
! 415: 0, /* xFinalize */
! 416: "stat3_get", /* zName */
! 417: 0, /* pHash */
! 418: 0 /* pDestructor */
! 419: };
! 420: #endif /* SQLITE_ENABLE_STAT3 */
! 421:
! 422:
! 423:
! 424:
! 425: /*
! 426: ** Generate code to do an analysis of all indices associated with
! 427: ** a single table.
! 428: */
! 429: static void analyzeOneTable(
! 430: Parse *pParse, /* Parser context */
! 431: Table *pTab, /* Table whose indices are to be analyzed */
! 432: Index *pOnlyIdx, /* If not NULL, only analyze this one index */
! 433: int iStatCur, /* Index of VdbeCursor that writes the sqlite_stat1 table */
! 434: int iMem /* Available memory locations begin here */
! 435: ){
! 436: sqlite3 *db = pParse->db; /* Database handle */
! 437: Index *pIdx; /* An index to being analyzed */
! 438: int iIdxCur; /* Cursor open on index being analyzed */
! 439: Vdbe *v; /* The virtual machine being built up */
! 440: int i; /* Loop counter */
! 441: int topOfLoop; /* The top of the loop */
! 442: int endOfLoop; /* The end of the loop */
! 443: int jZeroRows = -1; /* Jump from here if number of rows is zero */
! 444: int iDb; /* Index of database containing pTab */
! 445: int regTabname = iMem++; /* Register containing table name */
! 446: int regIdxname = iMem++; /* Register containing index name */
! 447: int regStat1 = iMem++; /* The stat column of sqlite_stat1 */
! 448: #ifdef SQLITE_ENABLE_STAT3
! 449: int regNumEq = regStat1; /* Number of instances. Same as regStat1 */
! 450: int regNumLt = iMem++; /* Number of keys less than regSample */
! 451: int regNumDLt = iMem++; /* Number of distinct keys less than regSample */
! 452: int regSample = iMem++; /* The next sample value */
! 453: int regRowid = regSample; /* Rowid of a sample */
! 454: int regAccum = iMem++; /* Register to hold Stat3Accum object */
! 455: int regLoop = iMem++; /* Loop counter */
! 456: int regCount = iMem++; /* Number of rows in the table or index */
! 457: int regTemp1 = iMem++; /* Intermediate register */
! 458: int regTemp2 = iMem++; /* Intermediate register */
! 459: int once = 1; /* One-time initialization */
! 460: int shortJump = 0; /* Instruction address */
! 461: int iTabCur = pParse->nTab++; /* Table cursor */
! 462: #endif
! 463: int regCol = iMem++; /* Content of a column in analyzed table */
! 464: int regRec = iMem++; /* Register holding completed record */
! 465: int regTemp = iMem++; /* Temporary use register */
! 466: int regNewRowid = iMem++; /* Rowid for the inserted record */
! 467:
! 468:
! 469: v = sqlite3GetVdbe(pParse);
! 470: if( v==0 || NEVER(pTab==0) ){
! 471: return;
! 472: }
! 473: if( pTab->tnum==0 ){
! 474: /* Do not gather statistics on views or virtual tables */
! 475: return;
! 476: }
! 477: if( memcmp(pTab->zName, "sqlite_", 7)==0 ){
! 478: /* Do not gather statistics on system tables */
! 479: return;
! 480: }
! 481: assert( sqlite3BtreeHoldsAllMutexes(db) );
! 482: iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
! 483: assert( iDb>=0 );
! 484: assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
! 485: #ifndef SQLITE_OMIT_AUTHORIZATION
! 486: if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0,
! 487: db->aDb[iDb].zName ) ){
! 488: return;
! 489: }
! 490: #endif
! 491:
! 492: /* Establish a read-lock on the table at the shared-cache level. */
! 493: sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
! 494:
! 495: iIdxCur = pParse->nTab++;
! 496: sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
! 497: for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
! 498: int nCol;
! 499: KeyInfo *pKey;
! 500: int addrIfNot = 0; /* address of OP_IfNot */
! 501: int *aChngAddr; /* Array of jump instruction addresses */
! 502:
! 503: if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
! 504: VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
! 505: nCol = pIdx->nColumn;
! 506: aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol);
! 507: if( aChngAddr==0 ) continue;
! 508: pKey = sqlite3IndexKeyinfo(pParse, pIdx);
! 509: if( iMem+1+(nCol*2)>pParse->nMem ){
! 510: pParse->nMem = iMem+1+(nCol*2);
! 511: }
! 512:
! 513: /* Open a cursor to the index to be analyzed. */
! 514: assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
! 515: sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
! 516: (char *)pKey, P4_KEYINFO_HANDOFF);
! 517: VdbeComment((v, "%s", pIdx->zName));
! 518:
! 519: /* Populate the register containing the index name. */
! 520: sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);
! 521:
! 522: #ifdef SQLITE_ENABLE_STAT3
! 523: if( once ){
! 524: once = 0;
! 525: sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);
! 526: }
! 527: sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
! 528: sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT3_SAMPLES, regTemp1);
! 529: sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumEq);
! 530: sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumLt);
! 531: sqlite3VdbeAddOp2(v, OP_Integer, -1, regNumDLt);
! 532: sqlite3VdbeAddOp3(v, OP_Null, 0, regSample, regAccum);
! 533: sqlite3VdbeAddOp4(v, OP_Function, 1, regCount, regAccum,
! 534: (char*)&stat3InitFuncdef, P4_FUNCDEF);
! 535: sqlite3VdbeChangeP5(v, 2);
! 536: #endif /* SQLITE_ENABLE_STAT3 */
! 537:
! 538: /* The block of memory cells initialized here is used as follows.
! 539: **
! 540: ** iMem:
! 541: ** The total number of rows in the table.
! 542: **
! 543: ** iMem+1 .. iMem+nCol:
! 544: ** Number of distinct entries in index considering the
! 545: ** left-most N columns only, where N is between 1 and nCol,
! 546: ** inclusive.
! 547: **
! 548: ** iMem+nCol+1 .. Mem+2*nCol:
! 549: ** Previous value of indexed columns, from left to right.
! 550: **
! 551: ** Cells iMem through iMem+nCol are initialized to 0. The others are
! 552: ** initialized to contain an SQL NULL.
! 553: */
! 554: for(i=0; i<=nCol; i++){
! 555: sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem+i);
! 556: }
! 557: for(i=0; i<nCol; i++){
! 558: sqlite3VdbeAddOp2(v, OP_Null, 0, iMem+nCol+i+1);
! 559: }
! 560:
! 561: /* Start the analysis loop. This loop runs through all the entries in
! 562: ** the index b-tree. */
! 563: endOfLoop = sqlite3VdbeMakeLabel(v);
! 564: sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
! 565: topOfLoop = sqlite3VdbeCurrentAddr(v);
! 566: sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); /* Increment row counter */
! 567:
! 568: for(i=0; i<nCol; i++){
! 569: CollSeq *pColl;
! 570: sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
! 571: if( i==0 ){
! 572: /* Always record the very first row */
! 573: addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
! 574: }
! 575: assert( pIdx->azColl!=0 );
! 576: assert( pIdx->azColl[i]!=0 );
! 577: pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
! 578: aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
! 579: (char*)pColl, P4_COLLSEQ);
! 580: sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
! 581: VdbeComment((v, "jump if column %d changed", i));
! 582: #ifdef SQLITE_ENABLE_STAT3
! 583: if( i==0 ){
! 584: sqlite3VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
! 585: VdbeComment((v, "incr repeat count"));
! 586: }
! 587: #endif
! 588: }
! 589: sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
! 590: for(i=0; i<nCol; i++){
! 591: sqlite3VdbeJumpHere(v, aChngAddr[i]); /* Set jump dest for the OP_Ne */
! 592: if( i==0 ){
! 593: sqlite3VdbeJumpHere(v, addrIfNot); /* Jump dest for OP_IfNot */
! 594: #ifdef SQLITE_ENABLE_STAT3
! 595: sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
! 596: (char*)&stat3PushFuncdef, P4_FUNCDEF);
! 597: sqlite3VdbeChangeP5(v, 5);
! 598: sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, pIdx->nColumn, regRowid);
! 599: sqlite3VdbeAddOp3(v, OP_Add, regNumEq, regNumLt, regNumLt);
! 600: sqlite3VdbeAddOp2(v, OP_AddImm, regNumDLt, 1);
! 601: sqlite3VdbeAddOp2(v, OP_Integer, 1, regNumEq);
! 602: #endif
! 603: }
! 604: sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
! 605: sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
! 606: }
! 607: sqlite3DbFree(db, aChngAddr);
! 608:
! 609: /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
! 610: sqlite3VdbeResolveLabel(v, endOfLoop);
! 611:
! 612: sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
! 613: sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
! 614: #ifdef SQLITE_ENABLE_STAT3
! 615: sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
! 616: (char*)&stat3PushFuncdef, P4_FUNCDEF);
! 617: sqlite3VdbeChangeP5(v, 5);
! 618: sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop);
! 619: shortJump =
! 620: sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1);
! 621: sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regTemp1,
! 622: (char*)&stat3GetFuncdef, P4_FUNCDEF);
! 623: sqlite3VdbeChangeP5(v, 2);
! 624: sqlite3VdbeAddOp1(v, OP_IsNull, regTemp1);
! 625: sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regTemp1);
! 626: sqlite3VdbeAddOp3(v, OP_Column, iTabCur, pIdx->aiColumn[0], regSample);
! 627: sqlite3ColumnDefault(v, pTab, pIdx->aiColumn[0], regSample);
! 628: sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumEq,
! 629: (char*)&stat3GetFuncdef, P4_FUNCDEF);
! 630: sqlite3VdbeChangeP5(v, 3);
! 631: sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumLt,
! 632: (char*)&stat3GetFuncdef, P4_FUNCDEF);
! 633: sqlite3VdbeChangeP5(v, 4);
! 634: sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumDLt,
! 635: (char*)&stat3GetFuncdef, P4_FUNCDEF);
! 636: sqlite3VdbeChangeP5(v, 5);
! 637: sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 6, regRec, "bbbbbb", 0);
! 638: sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
! 639: sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regNewRowid);
! 640: sqlite3VdbeAddOp2(v, OP_Goto, 0, shortJump);
! 641: sqlite3VdbeJumpHere(v, shortJump+2);
! 642: #endif
! 643:
! 644: /* Store the results in sqlite_stat1.
! 645: **
! 646: ** The result is a single row of the sqlite_stat1 table. The first
! 647: ** two columns are the names of the table and index. The third column
! 648: ** is a string composed of a list of integer statistics about the
! 649: ** index. The first integer in the list is the total number of entries
! 650: ** in the index. There is one additional integer in the list for each
! 651: ** column of the table. This additional integer is a guess of how many
! 652: ** rows of the table the index will select. If D is the count of distinct
! 653: ** values and K is the total number of rows, then the integer is computed
! 654: ** as:
! 655: **
! 656: ** I = (K+D-1)/D
! 657: **
! 658: ** If K==0 then no entry is made into the sqlite_stat1 table.
! 659: ** If K>0 then it is always the case the D>0 so division by zero
! 660: ** is never possible.
! 661: */
! 662: sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1);
! 663: if( jZeroRows<0 ){
! 664: jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
! 665: }
! 666: for(i=0; i<nCol; i++){
! 667: sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0);
! 668: sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
! 669: sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp);
! 670: sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
! 671: sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp);
! 672: sqlite3VdbeAddOp1(v, OP_ToInt, regTemp);
! 673: sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
! 674: }
! 675: sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
! 676: sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
! 677: sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
! 678: sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
! 679: }
! 680:
! 681: /* If the table has no indices, create a single sqlite_stat1 entry
! 682: ** containing NULL as the index name and the row count as the content.
! 683: */
! 684: if( pTab->pIndex==0 ){
! 685: sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
! 686: VdbeComment((v, "%s", pTab->zName));
! 687: sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat1);
! 688: sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
! 689: jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1);
! 690: }else{
! 691: sqlite3VdbeJumpHere(v, jZeroRows);
! 692: jZeroRows = sqlite3VdbeAddOp0(v, OP_Goto);
! 693: }
! 694: sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
! 695: sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
! 696: sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
! 697: sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
! 698: sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
! 699: if( pParse->nMem<regRec ) pParse->nMem = regRec;
! 700: sqlite3VdbeJumpHere(v, jZeroRows);
! 701: }
! 702:
! 703:
! 704: /*
! 705: ** Generate code that will cause the most recent index analysis to
! 706: ** be loaded into internal hash tables where is can be used.
! 707: */
! 708: static void loadAnalysis(Parse *pParse, int iDb){
! 709: Vdbe *v = sqlite3GetVdbe(pParse);
! 710: if( v ){
! 711: sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb);
! 712: }
! 713: }
! 714:
! 715: /*
! 716: ** Generate code that will do an analysis of an entire database
! 717: */
! 718: static void analyzeDatabase(Parse *pParse, int iDb){
! 719: sqlite3 *db = pParse->db;
! 720: Schema *pSchema = db->aDb[iDb].pSchema; /* Schema of database iDb */
! 721: HashElem *k;
! 722: int iStatCur;
! 723: int iMem;
! 724:
! 725: sqlite3BeginWriteOperation(pParse, 0, iDb);
! 726: iStatCur = pParse->nTab;
! 727: pParse->nTab += 3;
! 728: openStatTable(pParse, iDb, iStatCur, 0, 0);
! 729: iMem = pParse->nMem+1;
! 730: assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
! 731: for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
! 732: Table *pTab = (Table*)sqliteHashData(k);
! 733: analyzeOneTable(pParse, pTab, 0, iStatCur, iMem);
! 734: }
! 735: loadAnalysis(pParse, iDb);
! 736: }
! 737:
! 738: /*
! 739: ** Generate code that will do an analysis of a single table in
! 740: ** a database. If pOnlyIdx is not NULL then it is a single index
! 741: ** in pTab that should be analyzed.
! 742: */
! 743: static void analyzeTable(Parse *pParse, Table *pTab, Index *pOnlyIdx){
! 744: int iDb;
! 745: int iStatCur;
! 746:
! 747: assert( pTab!=0 );
! 748: assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
! 749: iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
! 750: sqlite3BeginWriteOperation(pParse, 0, iDb);
! 751: iStatCur = pParse->nTab;
! 752: pParse->nTab += 3;
! 753: if( pOnlyIdx ){
! 754: openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx");
! 755: }else{
! 756: openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl");
! 757: }
! 758: analyzeOneTable(pParse, pTab, pOnlyIdx, iStatCur, pParse->nMem+1);
! 759: loadAnalysis(pParse, iDb);
! 760: }
! 761:
! 762: /*
! 763: ** Generate code for the ANALYZE command. The parser calls this routine
! 764: ** when it recognizes an ANALYZE command.
! 765: **
! 766: ** ANALYZE -- 1
! 767: ** ANALYZE <database> -- 2
! 768: ** ANALYZE ?<database>.?<tablename> -- 3
! 769: **
! 770: ** Form 1 causes all indices in all attached databases to be analyzed.
! 771: ** Form 2 analyzes all indices the single database named.
! 772: ** Form 3 analyzes all indices associated with the named table.
! 773: */
! 774: void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2){
! 775: sqlite3 *db = pParse->db;
! 776: int iDb;
! 777: int i;
! 778: char *z, *zDb;
! 779: Table *pTab;
! 780: Index *pIdx;
! 781: Token *pTableName;
! 782:
! 783: /* Read the database schema. If an error occurs, leave an error message
! 784: ** and code in pParse and return NULL. */
! 785: assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
! 786: if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
! 787: return;
! 788: }
! 789:
! 790: assert( pName2!=0 || pName1==0 );
! 791: if( pName1==0 ){
! 792: /* Form 1: Analyze everything */
! 793: for(i=0; i<db->nDb; i++){
! 794: if( i==1 ) continue; /* Do not analyze the TEMP database */
! 795: analyzeDatabase(pParse, i);
! 796: }
! 797: }else if( pName2->n==0 ){
! 798: /* Form 2: Analyze the database or table named */
! 799: iDb = sqlite3FindDb(db, pName1);
! 800: if( iDb>=0 ){
! 801: analyzeDatabase(pParse, iDb);
! 802: }else{
! 803: z = sqlite3NameFromToken(db, pName1);
! 804: if( z ){
! 805: if( (pIdx = sqlite3FindIndex(db, z, 0))!=0 ){
! 806: analyzeTable(pParse, pIdx->pTable, pIdx);
! 807: }else if( (pTab = sqlite3LocateTable(pParse, 0, z, 0))!=0 ){
! 808: analyzeTable(pParse, pTab, 0);
! 809: }
! 810: sqlite3DbFree(db, z);
! 811: }
! 812: }
! 813: }else{
! 814: /* Form 3: Analyze the fully qualified table name */
! 815: iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pTableName);
! 816: if( iDb>=0 ){
! 817: zDb = db->aDb[iDb].zName;
! 818: z = sqlite3NameFromToken(db, pTableName);
! 819: if( z ){
! 820: if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){
! 821: analyzeTable(pParse, pIdx->pTable, pIdx);
! 822: }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){
! 823: analyzeTable(pParse, pTab, 0);
! 824: }
! 825: sqlite3DbFree(db, z);
! 826: }
! 827: }
! 828: }
! 829: }
! 830:
! 831: /*
! 832: ** Used to pass information from the analyzer reader through to the
! 833: ** callback routine.
! 834: */
! 835: typedef struct analysisInfo analysisInfo;
! 836: struct analysisInfo {
! 837: sqlite3 *db;
! 838: const char *zDatabase;
! 839: };
! 840:
! 841: /*
! 842: ** This callback is invoked once for each index when reading the
! 843: ** sqlite_stat1 table.
! 844: **
! 845: ** argv[0] = name of the table
! 846: ** argv[1] = name of the index (might be NULL)
! 847: ** argv[2] = results of analysis - on integer for each column
! 848: **
! 849: ** Entries for which argv[1]==NULL simply record the number of rows in
! 850: ** the table.
! 851: */
! 852: static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){
! 853: analysisInfo *pInfo = (analysisInfo*)pData;
! 854: Index *pIndex;
! 855: Table *pTable;
! 856: int i, c, n;
! 857: tRowcnt v;
! 858: const char *z;
! 859:
! 860: assert( argc==3 );
! 861: UNUSED_PARAMETER2(NotUsed, argc);
! 862:
! 863: if( argv==0 || argv[0]==0 || argv[2]==0 ){
! 864: return 0;
! 865: }
! 866: pTable = sqlite3FindTable(pInfo->db, argv[0], pInfo->zDatabase);
! 867: if( pTable==0 ){
! 868: return 0;
! 869: }
! 870: if( argv[1] ){
! 871: pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
! 872: }else{
! 873: pIndex = 0;
! 874: }
! 875: n = pIndex ? pIndex->nColumn : 0;
! 876: z = argv[2];
! 877: for(i=0; *z && i<=n; i++){
! 878: v = 0;
! 879: while( (c=z[0])>='0' && c<='9' ){
! 880: v = v*10 + c - '0';
! 881: z++;
! 882: }
! 883: if( i==0 ) pTable->nRowEst = v;
! 884: if( pIndex==0 ) break;
! 885: pIndex->aiRowEst[i] = v;
! 886: if( *z==' ' ) z++;
! 887: if( memcmp(z, "unordered", 10)==0 ){
! 888: pIndex->bUnordered = 1;
! 889: break;
! 890: }
! 891: }
! 892: return 0;
! 893: }
! 894:
! 895: /*
! 896: ** If the Index.aSample variable is not NULL, delete the aSample[] array
! 897: ** and its contents.
! 898: */
! 899: void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
! 900: #ifdef SQLITE_ENABLE_STAT3
! 901: if( pIdx->aSample ){
! 902: int j;
! 903: for(j=0; j<pIdx->nSample; j++){
! 904: IndexSample *p = &pIdx->aSample[j];
! 905: if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){
! 906: sqlite3DbFree(db, p->u.z);
! 907: }
! 908: }
! 909: sqlite3DbFree(db, pIdx->aSample);
! 910: }
! 911: if( db && db->pnBytesFreed==0 ){
! 912: pIdx->nSample = 0;
! 913: pIdx->aSample = 0;
! 914: }
! 915: #else
! 916: UNUSED_PARAMETER(db);
! 917: UNUSED_PARAMETER(pIdx);
! 918: #endif
! 919: }
! 920:
! 921: #ifdef SQLITE_ENABLE_STAT3
! 922: /*
! 923: ** Load content from the sqlite_stat3 table into the Index.aSample[]
! 924: ** arrays of all indices.
! 925: */
! 926: static int loadStat3(sqlite3 *db, const char *zDb){
! 927: int rc; /* Result codes from subroutines */
! 928: sqlite3_stmt *pStmt = 0; /* An SQL statement being run */
! 929: char *zSql; /* Text of the SQL statement */
! 930: Index *pPrevIdx = 0; /* Previous index in the loop */
! 931: int idx = 0; /* slot in pIdx->aSample[] for next sample */
! 932: int eType; /* Datatype of a sample */
! 933: IndexSample *pSample; /* A slot in pIdx->aSample[] */
! 934:
! 935: if( !sqlite3FindTable(db, "sqlite_stat3", zDb) ){
! 936: return SQLITE_OK;
! 937: }
! 938:
! 939: zSql = sqlite3MPrintf(db,
! 940: "SELECT idx,count(*) FROM %Q.sqlite_stat3"
! 941: " GROUP BY idx", zDb);
! 942: if( !zSql ){
! 943: return SQLITE_NOMEM;
! 944: }
! 945: rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
! 946: sqlite3DbFree(db, zSql);
! 947: if( rc ) return rc;
! 948:
! 949: while( sqlite3_step(pStmt)==SQLITE_ROW ){
! 950: char *zIndex; /* Index name */
! 951: Index *pIdx; /* Pointer to the index object */
! 952: int nSample; /* Number of samples */
! 953:
! 954: zIndex = (char *)sqlite3_column_text(pStmt, 0);
! 955: if( zIndex==0 ) continue;
! 956: nSample = sqlite3_column_int(pStmt, 1);
! 957: pIdx = sqlite3FindIndex(db, zIndex, zDb);
! 958: if( pIdx==0 ) continue;
! 959: assert( pIdx->nSample==0 );
! 960: pIdx->nSample = nSample;
! 961: pIdx->aSample = sqlite3MallocZero( nSample*sizeof(IndexSample) );
! 962: pIdx->avgEq = pIdx->aiRowEst[1];
! 963: if( pIdx->aSample==0 ){
! 964: db->mallocFailed = 1;
! 965: sqlite3_finalize(pStmt);
! 966: return SQLITE_NOMEM;
! 967: }
! 968: }
! 969: rc = sqlite3_finalize(pStmt);
! 970: if( rc ) return rc;
! 971:
! 972: zSql = sqlite3MPrintf(db,
! 973: "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat3", zDb);
! 974: if( !zSql ){
! 975: return SQLITE_NOMEM;
! 976: }
! 977: rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
! 978: sqlite3DbFree(db, zSql);
! 979: if( rc ) return rc;
! 980:
! 981: while( sqlite3_step(pStmt)==SQLITE_ROW ){
! 982: char *zIndex; /* Index name */
! 983: Index *pIdx; /* Pointer to the index object */
! 984: int i; /* Loop counter */
! 985: tRowcnt sumEq; /* Sum of the nEq values */
! 986:
! 987: zIndex = (char *)sqlite3_column_text(pStmt, 0);
! 988: if( zIndex==0 ) continue;
! 989: pIdx = sqlite3FindIndex(db, zIndex, zDb);
! 990: if( pIdx==0 ) continue;
! 991: if( pIdx==pPrevIdx ){
! 992: idx++;
! 993: }else{
! 994: pPrevIdx = pIdx;
! 995: idx = 0;
! 996: }
! 997: assert( idx<pIdx->nSample );
! 998: pSample = &pIdx->aSample[idx];
! 999: pSample->nEq = (tRowcnt)sqlite3_column_int64(pStmt, 1);
! 1000: pSample->nLt = (tRowcnt)sqlite3_column_int64(pStmt, 2);
! 1001: pSample->nDLt = (tRowcnt)sqlite3_column_int64(pStmt, 3);
! 1002: if( idx==pIdx->nSample-1 ){
! 1003: if( pSample->nDLt>0 ){
! 1004: for(i=0, sumEq=0; i<=idx-1; i++) sumEq += pIdx->aSample[i].nEq;
! 1005: pIdx->avgEq = (pSample->nLt - sumEq)/pSample->nDLt;
! 1006: }
! 1007: if( pIdx->avgEq<=0 ) pIdx->avgEq = 1;
! 1008: }
! 1009: eType = sqlite3_column_type(pStmt, 4);
! 1010: pSample->eType = (u8)eType;
! 1011: switch( eType ){
! 1012: case SQLITE_INTEGER: {
! 1013: pSample->u.i = sqlite3_column_int64(pStmt, 4);
! 1014: break;
! 1015: }
! 1016: case SQLITE_FLOAT: {
! 1017: pSample->u.r = sqlite3_column_double(pStmt, 4);
! 1018: break;
! 1019: }
! 1020: case SQLITE_NULL: {
! 1021: break;
! 1022: }
! 1023: default: assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB ); {
! 1024: const char *z = (const char *)(
! 1025: (eType==SQLITE_BLOB) ?
! 1026: sqlite3_column_blob(pStmt, 4):
! 1027: sqlite3_column_text(pStmt, 4)
! 1028: );
! 1029: int n = z ? sqlite3_column_bytes(pStmt, 4) : 0;
! 1030: pSample->nByte = n;
! 1031: if( n < 1){
! 1032: pSample->u.z = 0;
! 1033: }else{
! 1034: pSample->u.z = sqlite3Malloc(n);
! 1035: if( pSample->u.z==0 ){
! 1036: db->mallocFailed = 1;
! 1037: sqlite3_finalize(pStmt);
! 1038: return SQLITE_NOMEM;
! 1039: }
! 1040: memcpy(pSample->u.z, z, n);
! 1041: }
! 1042: }
! 1043: }
! 1044: }
! 1045: return sqlite3_finalize(pStmt);
! 1046: }
! 1047: #endif /* SQLITE_ENABLE_STAT3 */
! 1048:
! 1049: /*
! 1050: ** Load the content of the sqlite_stat1 and sqlite_stat3 tables. The
! 1051: ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[]
! 1052: ** arrays. The contents of sqlite_stat3 are used to populate the
! 1053: ** Index.aSample[] arrays.
! 1054: **
! 1055: ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR
! 1056: ** is returned. In this case, even if SQLITE_ENABLE_STAT3 was defined
! 1057: ** during compilation and the sqlite_stat3 table is present, no data is
! 1058: ** read from it.
! 1059: **
! 1060: ** If SQLITE_ENABLE_STAT3 was defined during compilation and the
! 1061: ** sqlite_stat3 table is not present in the database, SQLITE_ERROR is
! 1062: ** returned. However, in this case, data is read from the sqlite_stat1
! 1063: ** table (if it is present) before returning.
! 1064: **
! 1065: ** If an OOM error occurs, this function always sets db->mallocFailed.
! 1066: ** This means if the caller does not care about other errors, the return
! 1067: ** code may be ignored.
! 1068: */
! 1069: int sqlite3AnalysisLoad(sqlite3 *db, int iDb){
! 1070: analysisInfo sInfo;
! 1071: HashElem *i;
! 1072: char *zSql;
! 1073: int rc;
! 1074:
! 1075: assert( iDb>=0 && iDb<db->nDb );
! 1076: assert( db->aDb[iDb].pBt!=0 );
! 1077:
! 1078: /* Clear any prior statistics */
! 1079: assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
! 1080: for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
! 1081: Index *pIdx = sqliteHashData(i);
! 1082: sqlite3DefaultRowEst(pIdx);
! 1083: #ifdef SQLITE_ENABLE_STAT3
! 1084: sqlite3DeleteIndexSamples(db, pIdx);
! 1085: pIdx->aSample = 0;
! 1086: #endif
! 1087: }
! 1088:
! 1089: /* Check to make sure the sqlite_stat1 table exists */
! 1090: sInfo.db = db;
! 1091: sInfo.zDatabase = db->aDb[iDb].zName;
! 1092: if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){
! 1093: return SQLITE_ERROR;
! 1094: }
! 1095:
! 1096: /* Load new statistics out of the sqlite_stat1 table */
! 1097: zSql = sqlite3MPrintf(db,
! 1098: "SELECT tbl,idx,stat FROM %Q.sqlite_stat1", sInfo.zDatabase);
! 1099: if( zSql==0 ){
! 1100: rc = SQLITE_NOMEM;
! 1101: }else{
! 1102: rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
! 1103: sqlite3DbFree(db, zSql);
! 1104: }
! 1105:
! 1106:
! 1107: /* Load the statistics from the sqlite_stat3 table. */
! 1108: #ifdef SQLITE_ENABLE_STAT3
! 1109: if( rc==SQLITE_OK ){
! 1110: rc = loadStat3(db, sInfo.zDatabase);
! 1111: }
! 1112: #endif
! 1113:
! 1114: if( rc==SQLITE_NOMEM ){
! 1115: db->mallocFailed = 1;
! 1116: }
! 1117: return rc;
! 1118: }
! 1119:
! 1120:
! 1121: #endif /* SQLITE_OMIT_ANALYZE */
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to analyze.c CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / src