Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Change the sqlite_stat2 schema to be more flexible. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
ded9dec6459baf21e01f63250db5ace5 |
| User & Date: | dan 2009-08-18 16:24:59 |
Context
|
2009-08-19
| ||
| 08:18 | Add the SQLITE_ENABLE_STAT2 macro. If this is not defined at build-time, the stat2 table is not created, populated, or used. check-in: 362665e8 user: dan tags: trunk | |
|
2009-08-18
| ||
| 16:24 | Change the sqlite_stat2 schema to be more flexible. check-in: ded9dec6 user: dan tags: trunk | |
|
2009-08-17
| ||
| 17:06 | First version of sqlite_stat2 (schema forces exactly 10 samples). check-in: dd96bda2 user: dan tags: trunk | |
Changes
Changes to src/analyze.c.
28 28 static void openStatTable( 29 29 Parse *pParse, /* Parsing context */ 30 30 int iDb, /* The database we are looking in */ 31 31 int iStatCur, /* Open the sqlite_stat1 table on this cursor */ 32 32 const char *zWhere /* Delete entries associated with this table */ 33 33 ){ 34 34 const char *aName[] = { "sqlite_stat1", "sqlite_stat2" }; 35 - const char *aCols[] = { "tbl,idx,stat", "tbl,idx," SQLITE_INDEX_SAMPLE_COLS }; 35 + const char *aCols[] = { "tbl,idx,stat", "tbl,idx,sampleno,sample" }; 36 36 int aRoot[] = {0, 0}; 37 37 int aCreateTbl[] = {0, 0}; 38 38 39 39 int i; 40 40 sqlite3 *db = pParse->db; 41 41 Db *pDb; 42 42 Vdbe *v = sqlite3GetVdbe(pParse); ................................................................................ 90 90 Parse *pParse, /* Parser context */ 91 91 Table *pTab, /* Table whose indices are to be analyzed */ 92 92 int iStatCur, /* Index of VdbeCursor that writes the sqlite_stat1 table */ 93 93 int iMem /* Available memory locations begin here */ 94 94 ){ 95 95 Index *pIdx; /* An index to being analyzed */ 96 96 int iIdxCur; /* Index of VdbeCursor for index being analyzed */ 97 - int nCol; /* Number of columns in the index */ 98 97 Vdbe *v; /* The virtual machine being built up */ 99 98 int i; /* Loop counter */ 100 99 int topOfLoop; /* The top of the loop */ 101 100 int endOfLoop; /* The end of the loop */ 102 101 int addr; /* The address of an instruction */ 103 102 int iDb; /* Index of database containing pTab */ 104 103 104 + 105 + /* Assign the required registers. */ 106 + int regTabname = iMem++; /* Register containing table name */ 107 + int regIdxname = iMem++; /* Register containing index name */ 108 + int regSampleno = iMem++; /* Register containing next sample number */ 109 + int regCol = iMem++; /* Content of a column analyzed table */ 110 + 111 + int regSamplerecno = iMem++; /* Next sample index record number */ 112 + int regRecno = iMem++; /* Register next index record number */ 113 + int regRec = iMem++; /* Register holding completed record */ 114 + int regTemp = iMem++; /* Temporary use register */ 115 + int regTemp2 = iMem++; /* Temporary use register */ 116 + int regRowid = iMem++; /* Rowid for the inserted record */ 117 + int regCount = iMem++; /* Total number of records in table */ 118 + 105 119 v = sqlite3GetVdbe(pParse); 106 120 if( v==0 || NEVER(pTab==0) || pTab->pIndex==0 ){ 107 121 /* Do no analysis for tables that have no indices */ 108 122 return; 109 123 } 110 124 assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); 111 125 iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); ................................................................................ 116 130 return; 117 131 } 118 132 #endif 119 133 120 134 /* Establish a read-lock on the table at the shared-cache level. */ 121 135 sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); 122 136 123 - iMem += 3; 124 137 iIdxCur = pParse->nTab++; 125 138 for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ 139 + int nCol = pIdx->nColumn; 126 140 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); 127 - int regFields; /* Register block for building records */ 128 - int regRec; /* Register holding completed record */ 129 - int regTemp; /* Temporary use register */ 130 - int regCol; /* Content of a column from the table being analyzed */ 131 - int regRowid; /* Rowid for the inserted record */ 132 - int regF2; 133 - int regStat2; 134 141 135 - /* Open a cursor to the index to be analyzed 136 - */ 142 + if( iMem+1+(nCol*2)>pParse->nMem ){ 143 + pParse->nMem = iMem+1+(nCol*2); 144 + } 145 + 146 + /* Open a cursor to the index to be analyzed. */ 137 147 assert( iDb==sqlite3SchemaToIndex(pParse->db, pIdx->pSchema) ); 138 - nCol = pIdx->nColumn; 139 148 sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb, 140 149 (char *)pKey, P4_KEYINFO_HANDOFF); 141 150 VdbeComment((v, "%s", pIdx->zName)); 142 - regStat2 = iMem+nCol*2+1; 143 - regFields = regStat2+2+SQLITE_INDEX_SAMPLES; 144 - regTemp = regRowid = regCol = regFields+3; 145 - regRec = regCol+1; 146 - if( regRec>pParse->nMem ){ 147 - pParse->nMem = regRec; 151 + 152 + /* If this iteration of the loop is generating code to analyze the 153 + ** first index in the pTab->pIndex list, then register regCount has 154 + ** not been populated. In this case populate it now. */ 155 + if( pTab->pIndex==pIdx ){ 156 + sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount); 157 + sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0); 148 158 } 159 + sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0); 149 160 150 - /* Fill in the register with the total number of rows. */ 151 - if( pTab->pIndex==pIdx ){ 152 - sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, iMem-3); 153 - } 154 - sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem-2); 155 - sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem-1); 161 + /* Zero the regSampleno and regRecno registers. */ 162 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno); 163 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno); 164 + 165 + /* If there are less than INDEX_SAMPLES records in the index, then 166 + ** set the contents of regSampleRecno to integer value INDEX_SAMPLES. 167 + ** Otherwise, set it to zero. This is to ensure that if there are 168 + ** less than the said number of entries in the index, no samples at 169 + ** all are collected. */ 170 + sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno); 171 + sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, sqlite3VdbeCurrentAddr(v)+2, 172 + regCount); 173 + sqlite3VdbeAddOp2(v, OP_Integer, 0, regSamplerecno); 156 174 157 175 /* Memory cells are used as follows. All memory cell addresses are 158 176 ** offset by iMem. That is, cell 0 below is actually cell iMem, cell 159 177 ** 1 is cell 1+iMem, etc. 160 178 ** 161 179 ** 0: The total number of rows in the table. 162 180 ** ................................................................................ 163 181 ** 1..nCol: Number of distinct entries in index considering the 164 182 ** left-most N columns, where N is the same as the 165 183 ** memory cell number. 166 184 ** 167 185 ** nCol+1..2*nCol: Previous value of indexed columns, from left to 168 186 ** right. 169 187 ** 170 - ** 2*nCol+1..2*nCol+10: 10 evenly spaced samples. 171 - ** 172 188 ** Cells iMem through iMem+nCol are initialized to 0. The others 173 189 ** are initialized to NULL. 174 190 */ 175 191 for(i=0; i<=nCol; i++){ 176 192 sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem+i); 177 193 } 178 194 for(i=0; i<nCol; i++){ 179 195 sqlite3VdbeAddOp2(v, OP_Null, 0, iMem+nCol+i+1); 180 196 } 181 197 182 - /* Start the analysis loop. This loop runs through all the entries inof 198 + /* Start the analysis loop. This loop runs through all the entries in 183 199 ** the index b-tree. */ 184 200 endOfLoop = sqlite3VdbeMakeLabel(v); 185 201 sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop); 186 202 topOfLoop = sqlite3VdbeCurrentAddr(v); 187 203 sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1); 188 204 189 205 for(i=0; i<nCol; i++){ 190 206 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol); 191 207 if( i==0 ){ 192 - sqlite3VdbeAddOp3(v, OP_Sample, iMem-3, regCol, regStat2+2); 208 + 209 + /* Check if the record that cursor iIdxCur points to contains a 210 + ** value that should be stored in the sqlite_stat2 table. If so, 211 + ** store it. */ 212 + int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno); 213 + assert( regTabname+1==regIdxname 214 + && regTabname+2==regSampleno 215 + && regTabname+3==regCol 216 + ); 217 + sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0); 218 + sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid); 219 + sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid); 220 + 221 + /* Calculate new values for regSamplerecno and regSampleno. 222 + ** 223 + ** sampleno = sampleno + 1 224 + ** samplerecno = samplerecno+(remaining records)/(remaining samples) 225 + */ 226 + sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1); 227 + sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regCount, regTemp); 228 + sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); 229 + sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2); 230 + sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2); 231 + sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp); 232 + sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno); 233 + 234 + sqlite3VdbeJumpHere(v, ne); 235 + sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1); 193 236 } 237 + 238 + assert( sqlite3VdbeCurrentAddr(v)==(topOfLoop+14+2*i) ); 194 239 sqlite3VdbeAddOp3(v, OP_Ne, regCol, 0, iMem+nCol+i+1); 240 + 195 241 /**** TODO: add collating sequence *****/ 196 242 sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL); 197 243 } 198 244 sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop); 199 245 for(i=0; i<nCol; i++){ 200 - sqlite3VdbeJumpHere(v, topOfLoop + 1 + 2*(i + 1)); 246 + sqlite3VdbeJumpHere(v, topOfLoop+14+2*i); 201 247 sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1); 202 248 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1); 203 249 } 204 250 205 251 /* End of the analysis loop. */ 206 252 sqlite3VdbeResolveLabel(v, endOfLoop); 207 253 sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop); ................................................................................ 222 268 ** I = (K+D-1)/D 223 269 ** 224 270 ** If K==0 then no entry is made into the sqlite_stat1 table. 225 271 ** If K>0 then it is always the case the D>0 so division by zero 226 272 ** is never possible. 227 273 */ 228 274 addr = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); 229 - sqlite3VdbeAddOp4(v, OP_String8, 0, regFields, 0, pTab->zName, 0); 230 - sqlite3VdbeAddOp4(v, OP_String8, 0, regFields+1, 0, pIdx->zName, 0); 231 - regF2 = regFields+2; 232 - sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regF2); 275 + sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno); 233 276 for(i=0; i<nCol; i++){ 234 277 sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); 235 - sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regF2, regF2); 278 + sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); 236 279 sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp); 237 280 sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); 238 281 sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp); 239 282 sqlite3VdbeAddOp1(v, OP_ToInt, regTemp); 240 - sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regF2, regF2); 283 + sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno); 241 284 } 242 - sqlite3VdbeAddOp4(v, OP_MakeRecord, regFields, 3, regRec, "aaa", 0); 285 + sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); 243 286 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid); 244 287 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid); 245 288 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 246 - 247 - /* Store the results in sqlite_stat2. */ 248 - sqlite3VdbeAddOp4(v, OP_String8, 0, regStat2, 0, pTab->zName, 0); 249 - sqlite3VdbeAddOp4(v, OP_String8, 0, regStat2+1, 0, pIdx->zName, 0); 250 - sqlite3VdbeAddOp4(v, OP_MakeRecord, regStat2, SQLITE_INDEX_SAMPLES+2, 251 - regRec, "aabbbbbbbbbb", 0 252 - ); 253 - sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid); 254 - sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid); 255 - 256 289 sqlite3VdbeJumpHere(v, addr); 257 290 } 258 291 } 259 292 260 293 /* 261 294 ** Generate code that will cause the most recent index analysis to 262 295 ** be laoded into internal hash tables where is can be used. ................................................................................ 457 490 }else{ 458 491 (void)sqlite3SafetyOff(db); 459 492 rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0); 460 493 (void)sqlite3SafetyOn(db); 461 494 sqlite3DbFree(db, zSql); 462 495 } 463 496 464 - /* Load the statistics from the sqlite_stat2 table */ 497 + /* Load the statistics from the sqlite_stat2 table. */ 465 498 if( rc==SQLITE_OK ){ 499 + sqlite3_stmt *pStmt = 0; 500 + 466 501 zSql = sqlite3MPrintf(db, 467 - "SELECT idx," SQLITE_INDEX_SAMPLE_COLS " FROM %Q.sqlite_stat2", 468 - sInfo.zDatabase 502 + "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase 469 503 ); 470 - if( zSql ){ 471 - sqlite3_stmt *pStmt = 0; 472 - (void)sqlite3SafetyOff(db); 473 - rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 474 - if( rc==SQLITE_OK ){ 475 - while( SQLITE_ROW==sqlite3_step(pStmt) ){ 476 - char *zIndex = (char *)sqlite3_column_text(pStmt, 0); 477 - Index *pIdx; 478 - pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase); 479 - if( pIdx ){ 480 - char *pSpace; 481 - IndexSample *pSample; 482 - int iCol; 483 - int nAlloc = SQLITE_INDEX_SAMPLES * sizeof(IndexSample); 484 - for(iCol=1; iCol<=SQLITE_INDEX_SAMPLES; iCol++){ 485 - int eType = sqlite3_column_type(pStmt, iCol); 486 - if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){ 487 - nAlloc += sqlite3_column_bytes(pStmt, iCol); 504 + if( !zSql ){ 505 + return SQLITE_NOMEM; 506 + } 507 + 508 + (void)sqlite3SafetyOff(db); 509 + rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); 510 + assert( rc!=SQLITE_MISUSE ); 511 + (void)sqlite3SafetyOn(db); 512 + sqlite3DbFree(db, zSql); 513 + (void)sqlite3SafetyOff(db); 514 + 515 + if( rc==SQLITE_OK ){ 516 + while( sqlite3_step(pStmt)==SQLITE_ROW ){ 517 + char *zIndex = (char *)sqlite3_column_text(pStmt, 0); 518 + Index *pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase); 519 + if( pIdx ){ 520 + int iSample = sqlite3_column_int(pStmt, 1); 521 + if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){ 522 + int eType = sqlite3_column_type(pStmt, 2); 523 + 524 + if( pIdx->aSample==0 ){ 525 + pIdx->aSample = (IndexSample *)sqlite3DbMallocZero(db, 526 + sizeof(IndexSample)*SQLITE_INDEX_SAMPLES 527 + ); 528 + if( pIdx->aSample==0 ){ 529 + break; 488 530 } 489 - } 490 - pSample = sqlite3DbMallocRaw(db, nAlloc); 491 - if( !pSample ){ 492 - rc = SQLITE_NOMEM; 493 - break; 494 - } 495 - sqlite3DbFree(db, pIdx->aSample); 496 - pIdx->aSample = pSample; 497 - pSpace = (char *)&pSample[SQLITE_INDEX_SAMPLES]; 498 - for(iCol=1; iCol<=SQLITE_INDEX_SAMPLES; iCol++){ 499 - int eType = sqlite3_column_type(pStmt, iCol); 500 - pSample[iCol-1].eType = eType; 501 - switch( eType ){ 502 - case SQLITE_BLOB: 503 - case SQLITE_TEXT: { 504 - const char *z = (const char *)( 505 - (eType==SQLITE_BLOB) ? 506 - sqlite3_column_blob(pStmt, iCol): 507 - sqlite3_column_text(pStmt, iCol) 508 - ); 509 - int n = sqlite3_column_bytes(pStmt, iCol); 510 - if( n>24 ){ 511 - n = 24; 512 - } 513 - pSample[iCol-1].nByte = n; 514 - pSample[iCol-1].u.z = pSpace; 515 - memcpy(pSpace, z, n); 516 - pSpace += n; 517 - break; 531 + } 532 + 533 + if( pIdx->aSample ){ 534 + IndexSample *pSample = &pIdx->aSample[iSample]; 535 + if( pSample->eType==SQLITE_TEXT || pSample->eType==SQLITE_BLOB ){ 536 + sqlite3DbFree(db, pSample->u.z); 537 + } 538 + pSample->eType = eType; 539 + if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ 540 + pSample->u.r = sqlite3_column_double(pStmt, 2); 541 + }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){ 542 + const char *z = (const char *)( 543 + (eType==SQLITE_BLOB) ? 544 + sqlite3_column_blob(pStmt, 2): 545 + sqlite3_column_text(pStmt, 2) 546 + ); 547 + int n = sqlite3_column_bytes(pStmt, 2); 548 + if( n>24 ){ 549 + n = 24; 518 550 } 519 - case SQLITE_INTEGER: 520 - case SQLITE_FLOAT: 521 - pSample[iCol-1].u.r = sqlite3_column_double(pStmt, iCol); 522 - break; 523 - case SQLITE_NULL: 551 + pSample->nByte = n; 552 + pSample->u.z = sqlite3DbMallocRaw(db, n); 553 + if( pSample->u.z ){ 554 + memcpy(pSample->u.z, z, n); 555 + }else{ 524 556 break; 525 - } 526 - } 527 - } 528 - } 529 - if( rc==SQLITE_NOMEM ){ 530 - sqlite3_finalize(pStmt); 531 - }else{ 532 - rc = sqlite3_finalize(pStmt); 533 - } 557 + } 558 + } 559 + } 560 + } 561 + } 534 562 } 535 - (void)sqlite3SafetyOn(db); 536 - sqlite3DbFree(db, zSql); 537 - }else{ 538 - rc = SQLITE_NOMEM; 563 + rc = sqlite3_finalize(pStmt); 539 564 } 565 + (void)sqlite3SafetyOn(db); 540 566 } 541 567 542 - if( rc==SQLITE_NOMEM ) db->mallocFailed = 1; 568 + if( rc==SQLITE_NOMEM ){ 569 + db->mallocFailed = 1; 570 + } 543 571 return rc; 544 572 } 545 573 546 574 547 575 #endif /* SQLITE_OMIT_ANALYZE */
Changes to src/build.c.
339 339 340 340 /* 341 341 ** Reclaim the memory used by an index 342 342 */ 343 343 static void freeIndex(Index *p){ 344 344 sqlite3 *db = p->pTable->dbMem; 345 345 /* testcase( db==0 ); */ 346 + if( p->aSample ){ 347 + int i; 348 + for(i=0; i<SQLITE_INDEX_SAMPLES; i++){ 349 + int e = p->aSample[i].eType; 350 + if( e==SQLITE_BLOB || e==SQLITE_TEXT ){ 351 + sqlite3DbFree(db, p->aSample[i].u.z); 352 + } 353 + } 354 + } 346 355 sqlite3DbFree(db, p->aSample); 347 356 sqlite3DbFree(db, p->zColAff); 348 357 sqlite3DbFree(db, p); 349 358 } 350 359 351 360 /* 352 361 ** Remove the given index from the index hash table, and free
Changes to src/sqliteInt.h.
74 74 #include <stdint.h> 75 75 #endif 76 76 #ifdef HAVE_INTTYPES_H 77 77 #include <inttypes.h> 78 78 #endif 79 79 80 80 #define SQLITE_INDEX_SAMPLES 10 81 -#define SQLITE_INDEX_SAMPLE_COLS "s1,s2,s3,s4,s5,s6,s7,s8,s9,s10" 82 81 83 82 /* 84 83 ** This macro is used to "hide" some ugliness in casting an int 85 84 ** value to a ptr value under the MSVC 64-bit compiler. Casting 86 85 ** non 64-bit values to ptr types results in a "hard" error with 87 86 ** the MSVC 64-bit compiler which this attempts to avoid. 88 87 **
Changes to src/vdbe.c.
1201 1201 ** Subtract the value in register P1 from the value in register P2 1202 1202 ** and store the result in register P3. 1203 1203 ** If either input is NULL, the result is NULL. 1204 1204 */ 1205 1205 /* Opcode: Divide P1 P2 P3 * * 1206 1206 ** 1207 1207 ** Divide the value in register P1 by the value in register P2 1208 -** and store the result in register P3. If the value in register P2 1209 -** is zero, then the result is NULL. 1210 -** If either input is NULL, the result is NULL. 1208 +** and store the result in register P3 (P3=P2/P1). If the value in 1209 +** register P1 is zero, then the result is NULL. If either input is 1210 +** NULL, the result is NULL. 1211 1211 */ 1212 1212 /* Opcode: Remainder P1 P2 P3 * * 1213 1213 ** 1214 1214 ** Compute the remainder after integer division of the value in 1215 1215 ** register P1 by the value in register P2 and store the result in P3. 1216 1216 ** If the value in register P2 is zero the result is NULL. 1217 1217 ** If either operand is NULL, the result is NULL. ................................................................................ 4967 4967 */ 4968 4968 case OP_Expire: { 4969 4969 if( !pOp->p1 ){ 4970 4970 sqlite3ExpirePreparedStatements(db); 4971 4971 }else{ 4972 4972 p->expired = 1; 4973 4973 } 4974 - break; 4975 -} 4976 - 4977 - 4978 -/* Opcode: Sample P1 P2 P3 * * 4979 -** 4980 -** Register P1 contains the total number of rows in the index being 4981 -** analyzed. Register P1+1 contains an integer between 0 and 9, the 4982 -** index of the next sample required. Register P1+2 contains an index 4983 -** between 1 and *P1, the number of the next sample required. Register 4984 -** P1+3 contains the current row index. 4985 -** 4986 -** If the integer in register P1+3 is the same as the integer in register 4987 -** P1+1, then the following takes place: 4988 -** 4989 -** (a) the contents of register P1+1 is incremented. 4990 -** 4991 -** (b) the contents of the register identified by parameter P2 is 4992 -** copied to register number (P3 + X), where X is the newly 4993 -** incremented value of register P1+1. 4994 -** 4995 -** (c) register P1+2 is set to the index of the next sample required. 4996 -*/ 4997 -case OP_Sample: { 4998 - int p1 = pOp->p1; 4999 - i64 iReq = p->aMem[p1+2].u.i; 5000 - i64 iRow = p->aMem[p1+3].u.i; 5001 - 5002 - while( iReq==iRow ){ 5003 - i64 nRow = p->aMem[p1].u.i; 5004 - int iSample = ++p->aMem[p1+1].u.i; 5005 - Mem *pReg = &p->aMem[pOp->p3 + iSample - 1]; 5006 - 5007 - assert( pReg<&p->aMem[p->nMem] ); 5008 - sqlite3VdbeMemShallowCopy(pReg, &p->aMem[pOp->p2], MEM_Ephem); 5009 - Deephemeralize(pReg); 5010 - if( iSample==SQLITE_INDEX_SAMPLES ){ 5011 - iReq = 0; 5012 - }else{ 5013 - iReq = iRow + (nRow-iRow)/(SQLITE_INDEX_SAMPLES - iSample); 5014 - p->aMem[p1+2].u.i = iReq; 5015 - } 5016 - } 5017 4974 break; 5018 4975 } 5019 4976 5020 4977 #ifndef SQLITE_OMIT_SHARED_CACHE 5021 4978 /* Opcode: TableLock P1 P2 P3 P4 * 5022 4979 ** 5023 4980 ** Obtain a lock on a particular table. This instruction is only used when
Changes to src/where.c.
1925 1925 sqlite3 *db = pParse->db; 1926 1926 CollSeq *pColl; 1927 1927 const u8 *z; 1928 1928 int n; 1929 1929 if( eType==SQLITE_BLOB ){ 1930 1930 z = (const u8 *)sqlite3_value_blob(pVal); 1931 1931 pColl = db->pDfltColl; 1932 - assert( pColl->enc==SQLITE_UTF8 ); 1932 + assert( pColl->enc==SQLITE_UTF8 ); 1933 1933 }else{ 1934 - pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, *pIdx->azColl, 0); 1935 - if( sqlite3CheckCollSeq(pParse, pColl) ){ 1936 - return SQLITE_ERROR; 1937 - } 1934 + pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, *pIdx->azColl, 0); 1935 + if( sqlite3CheckCollSeq(pParse, pColl) ){ 1936 + return SQLITE_ERROR; 1937 + } 1938 1938 z = (const u8 *)sqlite3ValueText(pVal, pColl->enc); 1939 - if( !z ){ 1940 - return SQLITE_NOMEM; 1941 - } 1939 + if( !z ){ 1940 + return SQLITE_NOMEM; 1941 + } 1942 1942 assert( z && pColl && pColl->xCmp ); 1943 1943 } 1944 1944 n = sqlite3ValueBytes(pVal, pColl->enc); 1945 1945 1946 1946 for(i=0; i<SQLITE_INDEX_SAMPLES; i++){ 1947 - int r; 1947 + int r; 1948 1948 int eSampletype = aSample[i].eType; 1949 1949 if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue; 1950 1950 if( (eSampletype!=eType) ) break; 1951 1951 if( pColl->enc==SQLITE_UTF8 ){ 1952 - r = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z); 1952 + r = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z); 1953 1953 }else{ 1954 - int nSample; 1955 - char *zSample = sqlite3Utf8to16( 1954 + int nSample; 1955 + char *zSample = sqlite3Utf8to16( 1956 1956 db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample 1957 1957 ); 1958 - if( !zSample ){ 1959 - assert( db->mallocFailed ); 1960 - return SQLITE_NOMEM; 1961 - } 1962 - r = pColl->xCmp(pColl->pUser, nSample, zSample, n, z); 1963 - sqlite3DbFree(db, zSample); 1958 + if( !zSample ){ 1959 + assert( db->mallocFailed ); 1960 + return SQLITE_NOMEM; 1961 + } 1962 + r = pColl->xCmp(pColl->pUser, nSample, zSample, n, z); 1963 + sqlite3DbFree(db, zSample); 1964 1964 } 1965 - if( r>0 ) break; 1965 + if( r>0 ) break; 1966 1966 } 1967 1967 } 1968 1968 1969 1969 *piRegion = i; 1970 1970 } 1971 1971 return SQLITE_OK; 1972 1972 } ................................................................................ 2242 2242 2243 2243 /* Determine the value of nBound. */ 2244 2244 if( nEq<pProbe->nColumn ){ 2245 2245 int j = pProbe->aiColumn[nEq]; 2246 2246 if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){ 2247 2247 WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx); 2248 2248 WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx); 2249 - whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound); 2249 + whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound); 2250 2250 if( pTop ){ 2251 2251 wsFlags |= WHERE_TOP_LIMIT; 2252 2252 used |= pTop->prereqRight; 2253 2253 } 2254 2254 if( pBtm ){ 2255 2255 wsFlags |= WHERE_BTM_LIMIT; 2256 2256 used |= pBtm->prereqRight;
Added test/analyze2.test.
1 +# 2009 August 06 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 +# $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $ 13 + 14 +set testdir [file dirname $argv0] 15 +source $testdir/tester.tcl 16 + 17 +sqlite3_db_config_lookaside db 0 0 0 18 + 19 +do_test analyze2-0.1 { 20 + execsql { CREATE TABLE t1(x PRIMARY KEY) } 21 + for {set i 0} {$i < 1000} {incr i} { 22 + execsql { INSERT INTO t1 VALUES($i) } 23 + } 24 + execsql { 25 + ANALYZE; 26 + SELECT * FROM sqlite_stat2; 27 + } 28 +} [list t1 sqlite_autoindex_t1_1 0 0 \ 29 + t1 sqlite_autoindex_t1_1 1 111 \ 30 + t1 sqlite_autoindex_t1_1 2 222 \ 31 + t1 sqlite_autoindex_t1_1 3 333 \ 32 + t1 sqlite_autoindex_t1_1 4 444 \ 33 + t1 sqlite_autoindex_t1_1 5 555 \ 34 + t1 sqlite_autoindex_t1_1 6 666 \ 35 + t1 sqlite_autoindex_t1_1 7 777 \ 36 + t1 sqlite_autoindex_t1_1 8 888 \ 37 + t1 sqlite_autoindex_t1_1 9 999 \ 38 +] 39 + 40 +do_test analyze2-0.2 { 41 + execsql { 42 + DELETE FROM t1 WHERe x>9; 43 + ANALYZE; 44 + SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2; 45 + } 46 +} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}} 47 + 48 +do_test analyze2-0.3 { 49 + execsql { 50 + DELETE FROM t1 WHERE x>5; 51 + ANALYZE; 52 + SELECT * FROM sqlite_stat2; 53 + } 54 +} {} 55 + 56 +do_test analyze2-0.4 { 57 + execsql { 58 + DELETE FROM t1; 59 + ANALYZE; 60 + SELECT * FROM sqlite_stat2; 61 + } 62 +} {} 63 + 64 +proc eqp sql { 65 + uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] 66 +} 67 + 68 +do_test analyze2-1.1 { 69 + execsql { 70 + DROP TABLE t1; 71 + CREATE TABLE t1(x, y); 72 + CREATE INDEX t1_x ON t1(x); 73 + CREATE INDEX t1_y ON t1(y); 74 + } 75 + 76 + for {set i 0} {$i < 1000} {incr i} { 77 + execsql { INSERT INTO t1 VALUES($i, $i) } 78 + } 79 + execsql ANALYZE 80 +} {} 81 +do_test analyze2-1.2 { 82 + execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE x>500 AND y>700 } 83 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 84 + 85 +do_test analyze2-1.3 { 86 + execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE x>700 AND y>500 } 87 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 88 + 89 +do_test analyze2-1.3 { 90 + execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE y>700 AND x>500 } 91 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 92 +do_test analyze2-1.4 { 93 + execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE y>500 AND x>700 } 94 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 95 + 96 +do_test analyze2-2.1 { 97 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700" 98 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 99 +do_test analyze2-2.2 { 100 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700" 101 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 102 +do_test analyze2-2.3 { 103 + eqp "SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300" 104 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 105 +do_test analyze2-2.4 { 106 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300" 107 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 108 + 109 +do_test analyze2-3.1 { 110 + eqp "SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300" 111 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 112 +do_test analyze2-3.2 { 113 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100" 114 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 115 + 116 +do_test analyze2-4.1 { 117 + set alphabet [list a b c d e f g h i j] 118 + for {set i 0} {$i < 1000} {incr i} { 119 + set str [lindex $alphabet [expr ($i/100)%10]] 120 + append str [lindex $alphabet [expr ($i/ 10)%10]] 121 + append str [lindex $alphabet [expr ($i/ 1)%10]] 122 + execsql { INSERT INTO t1 VALUES($str, $str) } 123 + } 124 + execsql ANALYZE 125 + execsql { 126 + SELECT tbl,idx,group_concat(sample,' ') 127 + FROM sqlite_stat2 128 + WHERE idx = 't1_x' 129 + GROUP BY tbl,idx 130 + } 131 +} {t1 t1_x {0 222 444 666 888 bba ddc ffe hhg jjj}} 132 +do_test analyze2-4.2 { 133 + execsql { 134 + SELECT tbl,idx,group_concat(sample,' ') 135 + FROM sqlite_stat2 136 + WHERE idx = 't1_y' 137 + GROUP BY tbl,idx 138 + } 139 +} {t1 t1_y {0 222 444 666 888 bba ddc ffe hhg jjj}} 140 + 141 +do_test analyze2-4.3 { 142 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'" 143 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 144 +do_test analyze2-4.4 { 145 + eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'" 146 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 147 +do_test analyze2-4.5 { 148 + eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'" 149 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 150 +do_test analyze2-4.6 { 151 + eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'" 152 +} {0 0 {TABLE t1 WITH INDEX t1_y}} 153 +do_test analyze2-4.7 { 154 + eqp "SELECT * FROM t1 WHERE x<221 AND y>'h'" 155 +} {0 0 {TABLE t1 WITH INDEX t1_x}} 156 + 157 +do_test analyze2-5.1 { 158 + execsql { CREATE TABLE t3(a COLLATE nocase, b) } 159 + execsql { CREATE INDEX t3a ON t3(a) } 160 + execsql { CREATE INDEX t3b ON t3(b) } 161 + set alphabet [list A b C d E f G h I j] 162 + for {set i 0} {$i < 1000} {incr i} { 163 + set str [lindex $alphabet [expr ($i/100)%10]] 164 + append str [lindex $alphabet [expr ($i/ 10)%10]] 165 + append str [lindex $alphabet [expr ($i/ 1)%10]] 166 + execsql { INSERT INTO t3 VALUES($str, $str) } 167 + } 168 + execsql ANALYZE 169 +} {} 170 +do_test analyze2-5.2 { 171 + execsql { 172 + SELECT tbl,idx,group_concat(sample,' ') 173 + FROM sqlite_stat2 174 + WHERE idx = 't3a' 175 + GROUP BY tbl,idx 176 + } 177 +} {t3 t3a {AAA bbb CCC ddd EEE fff GGG hhh III jjj}} 178 +do_test analyze2-5.3 { 179 + execsql { 180 + SELECT tbl,idx,group_concat(sample,' ') 181 + FROM sqlite_stat2 182 + WHERE idx = 't3b' 183 + GROUP BY tbl,idx 184 + } 185 +} {t3 t3b {AAA CCC EEE GGG III bbb ddd fff hhh jjj}} 186 + 187 +do_test analyze2-5.4 { 188 + eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'" 189 +} {0 0 {TABLE t3 WITH INDEX t3b}} 190 +do_test analyze2-5.5 { 191 + eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'" 192 +} {0 0 {TABLE t3 WITH INDEX t3a}} 193 + 194 +proc test_collate {enc lhs rhs} { 195 + # puts $enc 196 + return [string compare $lhs $rhs] 197 +} 198 + 199 +do_test analyze2-6.1 { 200 + add_test_collate db 0 0 1 201 + execsql { CREATE TABLE t4(x COLLATE test_collate) } 202 + execsql { CREATE INDEX t4x ON t4(x) } 203 + set alphabet [list a b c d e f g h i j] 204 + for {set i 0} {$i < 1000} {incr i} { 205 + set str [lindex $alphabet [expr ($i/100)%10]] 206 + append str [lindex $alphabet [expr ($i/ 10)%10]] 207 + append str [lindex $alphabet [expr ($i/ 1)%10]] 208 + execsql { INSERT INTO t4 VALUES($str) } 209 + } 210 + execsql ANALYZE 211 +} {} 212 +do_test analyze2-6.2 { 213 + execsql { 214 + SELECT tbl,idx,group_concat(sample,' ') 215 + FROM sqlite_stat2 216 + WHERE tbl = 't4' 217 + GROUP BY tbl,idx 218 + } 219 +} {t4 t4x {aaa bbb ccc ddd eee fff ggg hhh iii jjj}} 220 +do_test analyze2-6.3 { 221 + eqp "SELECT * FROM t4 WHERE x>'ccc'" 222 +} {0 0 {TABLE t4 WITH INDEX t4x}} 223 +do_test analyze2-6.4 { 224 + eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'" 225 +} {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}} 226 +do_test analyze2-6.5 { 227 + eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'" 228 +} {0 0 {TABLE t4 AS t41 WITH INDEX t4x} 1 1 {TABLE t4 AS t42 WITH INDEX t4x}} 229 + 230 +ifcapable memdebug { 231 + execsql { DELETE FROM t4 } 232 + db close 233 + source $testdir/malloc_common.tcl 234 + file copy -force test.db bak.db 235 + 236 + do_malloc_test analyze2-oom -tclprep { 237 + db close 238 + file copy -force bak.db test.db 239 + sqlite3 db test.db 240 + sqlite3_db_config_lookaside db 0 0 0 241 + add_test_collate db 0 0 1 242 + } -sqlbody { 243 + SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc' 244 + } 245 +} 246 + 247 +finish_test