Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | The results of ANALYZE are now loaded into internal data structures where they can be used. But they are not actually used yet. (CVS 2562) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1996bacfb97180965304e2a6d6784b6e |
User & Date: | drh 2005-07-23 03:18:40.000 |
Context
2005-07-23
| ||
14:52 | Store the total number of rows as part of the ANALYZE statistics. (CVS 2563) (check-in: 868279c78e user: drh tags: trunk) | |
03:18 | The results of ANALYZE are now loaded into internal data structures where they can be used. But they are not actually used yet. (CVS 2562) (check-in: 1996bacfb9 user: drh tags: trunk) | |
02:17 | Full-coverage testing and documentation for the ANALYZE command. The results of analysis are still not loaded or used, however. (CVS 2561) (check-in: bd7583a5d6 user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2005 July 8 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code associated with the ANALYZE command. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2005 July 8 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code associated with the ANALYZE command. ** ** @(#) $Id: analyze.c,v 1.4 2005/07/23 03:18:40 drh Exp $ */ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" /* ** This routine generates code that opens the sqlite_stat1 table on cursor ** iStatCur. |
︙ | ︙ | |||
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | } } sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0); sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0); sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v)); } } /* ** Generate code that will do an analysis of an entire database */ static void analyzeDatabase(Parse *pParse, int iDb){ sqlite3 *db = pParse->db; HashElem *k; int iStatCur; int iMem; sqlite3BeginWriteOperation(pParse, 0, iDb); iStatCur = pParse->nTab++; openStatTable(pParse, iDb, iStatCur, 0); iMem = pParse->nMem; for(k=sqliteHashFirst(&db->aDb[iDb].tblHash); k; k=sqliteHashNext(k)){ Table *pTab = (Table*)sqliteHashData(k); analyzeOneTable(pParse, pTab, iStatCur, iMem); } } /* ** Generate code that will do an analysis of a single table in ** a database. */ static void analyzeTable(Parse *pParse, Table *pTab){ int iDb; int iStatCur; assert( pTab!=0 ); iDb = pTab->iDb; sqlite3BeginWriteOperation(pParse, 0, iDb); iStatCur = pParse->nTab++; openStatTable(pParse, iDb, iStatCur, pTab->zName); analyzeOneTable(pParse, pTab, iStatCur, pParse->nMem); } /* ** Generate code for the ANALYZE command. The parser calls this routine ** when it recognizes an ANALYZE command. ** ** ANALYZE -- 1 | > > > > > > > > > > > | 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 | } } sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0); sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0); sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v)); } } /* ** Generate code that will cause the most recent index analysis to ** be laoded into internal hash tables where is can be used. */ static void loadAnalysis(Parse *pParse, int iDb){ Vdbe *v = sqlite3GetVdbe(pParse); sqlite3VdbeAddOp(v, OP_LoadAnalysis, iDb, 0); } /* ** Generate code that will do an analysis of an entire database */ static void analyzeDatabase(Parse *pParse, int iDb){ sqlite3 *db = pParse->db; HashElem *k; int iStatCur; int iMem; sqlite3BeginWriteOperation(pParse, 0, iDb); iStatCur = pParse->nTab++; openStatTable(pParse, iDb, iStatCur, 0); iMem = pParse->nMem; for(k=sqliteHashFirst(&db->aDb[iDb].tblHash); k; k=sqliteHashNext(k)){ Table *pTab = (Table*)sqliteHashData(k); analyzeOneTable(pParse, pTab, iStatCur, iMem); } loadAnalysis(pParse, iDb); } /* ** Generate code that will do an analysis of a single table in ** a database. */ static void analyzeTable(Parse *pParse, Table *pTab){ int iDb; int iStatCur; assert( pTab!=0 ); iDb = pTab->iDb; sqlite3BeginWriteOperation(pParse, 0, iDb); iStatCur = pParse->nTab++; openStatTable(pParse, iDb, iStatCur, pTab->zName); analyzeOneTable(pParse, pTab, iStatCur, pParse->nMem); loadAnalysis(pParse, iDb); } /* ** Generate code for the ANALYZE command. The parser calls this routine ** when it recognizes an ANALYZE command. ** ** ANALYZE -- 1 |
︙ | ︙ | |||
295 296 297 298 299 300 301 302 303 304 | if( pTab ){ analyzeTable(pParse, pTab); } } } } #endif /* SQLITE_OMIT_ANALYZE */ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 | if( pTab ){ analyzeTable(pParse, pTab); } } } } /* ** Used to pass information from the analyzer reader through to the ** callback routine. */ typedef struct analysisInfo analysisInfo; struct analysisInfo { sqlite3 *db; const char *zDatabase; }; /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** ** argv[0] = name of the index ** argv[1] = results of analysis - on integer for each column */ static int analysisLoader(void *pData, int argc, char **argv, char **azNotUsed){ analysisInfo *pInfo = (analysisInfo*)pData; Index *pIndex; int i, c; unsigned int v; const char *z; assert( argc==2 ); if( argv[0]==0 || argv[1]==0 ){ return 0; } pIndex = sqlite3FindIndex(pInfo->db, argv[0], pInfo->zDatabase); if( pIndex==0 ){ return 0; } z = argv[1]; for(i=0; *z && i<pIndex->nColumn; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } pIndex->aiRowEst[i] = v; if( *z==' ' ) z++; } return 0; } /* ** Load the content of the sqlite_stat1 table into the index hash tables. */ void sqlite3AnalysisLoad(sqlite3 *db, int iDb){ analysisInfo sInfo; HashElem *i; char *zSql; /* Clear any prior statistics */ for(i=sqliteHashFirst(&db->aDb[iDb].idxHash); i; i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); int j; for(j=pIdx->nColumn-1; j>=0; j--){ pIdx->aiRowEst[j] = 100; } } /* Check to make sure the sqlite_stat1 table existss */ sInfo.db = db; sInfo.zDatabase = db->aDb[iDb].zName; if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){ return; } /* Load new statistics out of the sqlite_stat1 table */ zSql = sqlite3MPrintf("SELECT idx, stat FROM %Q.sqlite_stat1", sInfo.zDatabase); sqlite3SafetyOff(db); sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); sqlite3SafetyOn(db); sqliteFree(zSql); } #endif /* SQLITE_OMIT_ANALYZE */ |
Changes to src/build.c.
︙ | ︙ | |||
18 19 20 21 22 23 24 | ** CREATE INDEX ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** | | | 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | ** CREATE INDEX ** DROP INDEX ** creating ID lists ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** ** $Id: build.c,v 1.334 2005/07/23 03:18:40 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Initialize the pParse structure as needed. |
︙ | ︙ | |||
2176 2177 2178 2179 2180 2181 2182 | if( pList==0 ) goto exit_create_index; } /* ** Allocate the index structure. */ pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 + | | > | | 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 | if( pList==0 ) goto exit_create_index; } /* ** Allocate the index structure. */ pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 + (sizeof(int)*2 + sizeof(CollSeq*))*pList->nExpr ); if( sqlite3_malloc_failed ) goto exit_create_index; pIndex->aiColumn = (int*)&pIndex->keyInfo.aColl[pList->nExpr]; pIndex->aiRowEst = &pIndex->aiColumn[pList->nExpr]; pIndex->zName = (char*)&pIndex->aiRowEst[pList->nExpr]; strcpy(pIndex->zName, zName); pIndex->pTable = pTab; pIndex->nColumn = pList->nExpr; pIndex->onError = onError; pIndex->autoIndex = pName==0; pIndex->iDb = iDb; |
︙ | ︙ | |||
2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 | } if( j>=pTab->nCol ){ sqlite3ErrorMsg(pParse, "table %s has no column named %s", pTab->zName, pList->a[i].zName); goto exit_create_index; } pIndex->aiColumn[i] = j; if( pList->a[i].pExpr ){ assert( pList->a[i].pExpr->pColl ); pIndex->keyInfo.aColl[i] = pList->a[i].pExpr->pColl; }else{ pIndex->keyInfo.aColl[i] = pTab->aCol[j].pColl; } assert( pIndex->keyInfo.aColl[i] ); | > | 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 | } if( j>=pTab->nCol ){ sqlite3ErrorMsg(pParse, "table %s has no column named %s", pTab->zName, pList->a[i].zName); goto exit_create_index; } pIndex->aiColumn[i] = j; pIndex->aiRowEst[i] = 100; if( pList->a[i].pExpr ){ assert( pList->a[i].pExpr->pColl ); pIndex->keyInfo.aColl[i] = pList->a[i].pExpr->pColl; }else{ pIndex->keyInfo.aColl[i] = pTab->aCol[j].pColl; } assert( pIndex->keyInfo.aColl[i] ); |
︙ | ︙ |
Changes to src/prepare.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains the implementation of the sqlite3_prepare() ** interface, and routines that contribute to loading the database schema ** from disk. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains the implementation of the sqlite3_prepare() ** interface, and routines that contribute to loading the database schema ** from disk. ** ** $Id: prepare.c,v 1.2 2005/07/23 03:18:40 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> /* ** Fill the InitData structure with an error message that indicates |
︙ | ︙ | |||
290 291 292 293 294 295 296 297 298 299 300 301 302 303 | zSql = sqlite3MPrintf( "SELECT name, rootpage, sql, '%s' FROM '%q'.%s", zDbNum, db->aDb[iDb].zName, zMasterName); sqlite3SafetyOff(db); rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0); sqlite3SafetyOn(db); sqliteFree(zSql); sqlite3BtreeCloseCursor(curMain); } if( sqlite3_malloc_failed ){ sqlite3SetString(pzErrMsg, "out of memory", (char*)0); rc = SQLITE_NOMEM; sqlite3ResetInternalSchema(db, 0); } | > > > > > | 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 | zSql = sqlite3MPrintf( "SELECT name, rootpage, sql, '%s' FROM '%q'.%s", zDbNum, db->aDb[iDb].zName, zMasterName); sqlite3SafetyOff(db); rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0); sqlite3SafetyOn(db); sqliteFree(zSql); #ifndef SQLITE_OMIT_ANALYZE if( rc==SQLITE_OK ){ sqlite3AnalysisLoad(db, iDb); } #endif sqlite3BtreeCloseCursor(curMain); } if( sqlite3_malloc_failed ){ sqlite3SetString(pzErrMsg, "out of memory", (char*)0); rc = SQLITE_NOMEM; sqlite3ResetInternalSchema(db, 0); } |
︙ | ︙ | |||
522 523 524 525 526 527 528 | int chars_parsed = sqlite3utf8CharLen(zSql8, zTail8-zSql8); *pzTail = (u8 *)zSql + sqlite3utf16ByteLen(zSql, chars_parsed); } return rc; } #endif /* SQLITE_OMIT_UTF16 */ | < | 527 528 529 530 531 532 533 | int chars_parsed = sqlite3utf8CharLen(zSql8, zTail8-zSql8); *pzTail = (u8 *)zSql + sqlite3utf16ByteLen(zSql, chars_parsed); } return rc; } #endif /* SQLITE_OMIT_UTF16 */ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.399 2005/07/23 03:18:40 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** These #defines should enable >2GB file support on Posix if the ** underlying operating system supports it. If the OS lacks |
︙ | ︙ | |||
741 742 743 744 745 746 747 748 749 750 751 752 753 754 | ** algorithm to employ whenever an attempt is made to insert a non-unique ** element. */ struct Index { char *zName; /* Name of this index */ int nColumn; /* Number of columns in the table used by this index */ int *aiColumn; /* Which columns are used by this index. 1st is 0 */ Table *pTable; /* The SQL table being indexed */ int tnum; /* Page containing root of this index in database file */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ u8 autoIndex; /* True if is automatically created (ex: by UNIQUE) */ u8 iDb; /* Index in sqlite.aDb[] of where this index is stored */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ | > | 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 | ** algorithm to employ whenever an attempt is made to insert a non-unique ** element. */ struct Index { char *zName; /* Name of this index */ int nColumn; /* Number of columns in the table used by this index */ int *aiColumn; /* Which columns are used by this index. 1st is 0 */ int *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */ Table *pTable; /* The SQL table being indexed */ int tnum; /* Page containing root of this index in database file */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ u8 autoIndex; /* True if is automatically created (ex: by UNIQUE) */ u8 iDb; /* Index in sqlite.aDb[] of where this index is stored */ char *zColAff; /* String defining the affinity of each column */ Index *pNext; /* The next index associated with the same table */ |
︙ | ︙ | |||
1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 | void sqlite3AlterBeginAddColumn(Parse *, SrcList *); const char *sqlite3TestErrorName(int); CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int); char sqlite3AffinityType(const Token*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); #ifdef SQLITE_SSE #include "sseInt.h" #endif #endif | > | 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 | void sqlite3AlterBeginAddColumn(Parse *, SrcList *); const char *sqlite3TestErrorName(int); CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int); char sqlite3AffinityType(const Token*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); void sqlite3AnalysisLoad(sqlite3*,int iDB); #ifdef SQLITE_SSE #include "sseInt.h" #endif #endif |
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.477 2005/07/23 03:18:40 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 | rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0); db->init.busy = 0; sqlite3SafetyOn(db); sqliteFree(zSql); break; } /* Opcode: DropTable P1 * P3 ** ** Remove the internal (in-memory) data structures that describe ** the table named P3 in database P1. This is called after a table ** is dropped in order to keep the internal representation of the ** schema consistent with what is on disk. */ | > > > > > > > > > > > > > > > | 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 | rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0); db->init.busy = 0; sqlite3SafetyOn(db); sqliteFree(zSql); break; } #ifndef SQLITE_OMIT_ANALYZE /* Opcode: LoadAnalysis P1 * * ** ** Read the sqlite_stat1 table for database P1 and load the content ** of that table into the internal index hash table. This will cause ** the analysis to be used when preparing all subsequent queries. */ case OP_LoadAnalysis: { /* no-push */ int iDb = pOp->p1; assert( iDb>=0 && iDb<db->nDb ); sqlite3AnalysisLoad(db, iDb); break; } #endif /* SQLITE_OMIT_ANALYZE */ /* Opcode: DropTable P1 * P3 ** ** Remove the internal (in-memory) data structures that describe ** the table named P3 in database P1. This is called after a table ** is dropped in order to keep the internal representation of the ** schema consistent with what is on disk. */ |
︙ | ︙ |