Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Store the total number of rows as part of the ANALYZE statistics. (CVS 2563) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
868279c78e056d27b2d1bea81127fe68 |
User & Date: | drh 2005-07-23 14:52:12.000 |
Context
2005-07-23
| ||
22:59 | A new optimizer that breaks a lot of tests. But none of them critically, I think. Nevertheless, there is a lot of work ahead to stabilize the code. (CVS 2564) (check-in: 86ce56ccea user: drh tags: trunk) | |
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) | |
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.5 2005/07/23 14:52:12 drh Exp $ */ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" /* ** This routine generates code that opens the sqlite_stat1 table on cursor ** iStatCur. |
︙ | ︙ | |||
159 160 161 162 163 164 165 | sqlite3VdbeAddOp(v, OP_Close, iIdxCur, 0); /* Store the results. ** ** The result is a single row of the sqlite_stmt1 table. The first ** two columns are the names of the table and index. The third column ** is a string composed of a list of integer statistics about the | > | | | | > > > < | < < < < | < | 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 | sqlite3VdbeAddOp(v, OP_Close, iIdxCur, 0); /* Store the results. ** ** The result is a single row of the sqlite_stmt1 table. The first ** two columns are the names of the table and index. The third column ** is a string composed of a list of integer statistics about the ** index. The first integer in the list is the total number of entires ** in the index. There is one additional integer in the list for each ** column of the table. This additional integer is a guess of how many ** rows of the table the index will select. If D is the count of distinct ** values and K is the total number of rows, then the integer is computed ** as: ** ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0); addr = sqlite3VdbeAddOp(v, OP_IfNot, 0, 0); sqlite3VdbeAddOp(v, OP_NewRowid, iStatCur, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, pIdx->zName, 0); sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0); sqlite3VdbeOp3(v, OP_String8, 0, 0, " ", 0); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0); sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0); sqlite3VdbeAddOp(v, OP_Add, 0, 0); sqlite3VdbeAddOp(v, OP_AddImm, -1, 0); sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0); sqlite3VdbeAddOp(v, OP_Divide, 0, 0); if( i==nCol-1 ){ sqlite3VdbeAddOp(v, OP_Concat, nCol*2-1, 0); }else{ sqlite3VdbeAddOp(v, OP_Dup, 1, 0); } } sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0); sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0); sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v)); } } |
︙ | ︙ | |||
339 340 341 342 343 344 345 | return 0; } pIndex = sqlite3FindIndex(pInfo->db, argv[0], pInfo->zDatabase); if( pIndex==0 ){ return 0; } z = argv[1]; | | | 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 | 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++; |
︙ | ︙ | |||
363 364 365 366 367 368 369 | 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; | | | | 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 | 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=0; j<=pIdx->nColumn; j++){ pIdx->aiRowEst[j] = j<100 ? 1000*(100-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 ){ |
︙ | ︙ |
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.335 2005/07/23 14:52:12 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. |
︙ | ︙ | |||
2175 2176 2177 2178 2179 2180 2181 | pList = sqlite3ExprListAppend(0, 0, &nullId); if( pList==0 ) goto exit_create_index; } /* ** Allocate the index structure. */ | | | | 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 | pList = sqlite3ExprListAppend(0, 0, &nullId); if( pList==0 ) goto exit_create_index; } /* ** Allocate the index structure. */ pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 + sizeof(int) + (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+1]; strcpy(pIndex->zName, zName); pIndex->pTable = pTab; pIndex->nColumn = pList->nExpr; pIndex->onError = onError; pIndex->autoIndex = pName==0; pIndex->iDb = iDb; |
︙ | ︙ |
Changes to test/analyze.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2005 July 22 # # 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 implements regression tests for SQLite library. # This file implements tests for 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 22 # # 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 implements regression tests for SQLite library. # This file implements tests for the ANALYZE command. # # $Id: analyze.test,v 1.2 2005/07/23 14:52:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # There is nothing to test if ANALYZE is disable for this build. # ifcapable {!analyze} { |
︙ | ︙ | |||
123 124 125 126 127 128 129 | do_test analyze-3.1 { execsql { INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(1,3); ANALYZE main.t1; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } | | | | | | | | | | 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | do_test analyze-3.1 { execsql { INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(1,3); ANALYZE main.t1; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {2 2} t1i2 {2 1} t1i3 {2 2 1}} do_test analyze-3.2 { execsql { INSERT INTO t1 VALUES(1,4); INSERT INTO t1 VALUES(1,5); ANALYZE t1; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {4 4} t1i2 {4 1} t1i3 {4 4 1}} do_test analyze-3.3 { execsql { INSERT INTO t1 VALUES(2,5); ANALYZE main; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}} do_test analyze-3.4 { execsql { CREATE TABLE t2 AS SELECT * FROM t1; CREATE INDEX t2i1 ON t2(a); CREATE INDEX t2i2 ON t2(b); CREATE INDEX t2i3 ON t2(a,b); ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}} do_test analyze-3.5 { execsql { DROP INDEX t2i3; ANALYZE t1; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}} do_test analyze-3.6 { execsql { ANALYZE t2; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}} do_test analyze-3.7 { execsql { DROP INDEX t2i2; ANALYZE t2; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}} do_test analyze-3.8 { execsql { CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1; CREATE INDEX t3i1 ON t3(a); CREATE INDEX t3i2 ON t3(a,b,c,d); CREATE INDEX t3i3 ON t3(d,b,c,a); DROP TABLE t1; DROP TABLE t2; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; } } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}} finish_test |