Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | The ANALYZE command adds the sqlite_stat2.cnt column if it does not already exist. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | query-planner-tweaks |
| Files: | files | file ages | folders |
| SHA1: |
794fde6f918b405ebe47068dea76a2d3 |
| User & Date: | drh 2011-08-07 00:21:17 |
Context
|
2011-08-08
| ||
| 17:18 | Add code to actually use the sqlite_stat2.cnt field in the query planner. This changes some plans resulting in a few failures in analyze5.test. (Closed-Leaf check-in: d1248165 user: drh tags: query-planner-tweaks) | |
|
2011-08-07
| ||
| 00:21 | The ANALYZE command adds the sqlite_stat2.cnt column if it does not already exist. (check-in: 794fde6f user: drh tags: query-planner-tweaks) | |
|
2011-08-06
| ||
| 19:48 | The sqlite_stat2.cnt field is parsed if it is present. But it is not yet used. A large comment added to analyze.c to explain the format of the ANALYZE system tables. (check-in: 6d1e2372 user: drh tags: query-planner-tweaks) | |
Changes
Changes to src/analyze.c.
175 175 sqlite3NestedParse(pParse, 176 176 "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere 177 177 ); 178 178 }else{ 179 179 /* The sqlite_stat[12] table already exists. Delete all rows. */ 180 180 sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb); 181 181 } 182 +#ifdef SQLITE_ENABLE_STAT2 183 + if( i==1 && iDb!=1 && pStat->nCol==4 ){ 184 + sqlite3NestedParse(pParse, 185 + "UPDATE %Q.sqlite_master SET sql='CREATE TABLE sqlite_stat2(%s)'" 186 + " WHERE name='sqlite_stat2'", pDb->zName, aTable[i].zCols 187 + ); 188 + sqlite3ChangeCookie(pParse, iDb); 189 + } 190 +#endif 182 191 } 183 192 } 184 193 185 194 /* Open the sqlite_stat[12] tables for writing. */ 186 195 for(i=0; i<ArraySize(aTable); i++){ 187 196 sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb); 188 197 sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32); ................................................................................ 603 612 if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){ 604 613 analyzeTable(pParse, pIdx->pTable, pIdx); 605 614 }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ 606 615 analyzeTable(pParse, pTab, 0); 607 616 } 608 617 sqlite3DbFree(db, z); 609 618 } 610 - } 619 + } 611 620 } 612 621 } 613 622 614 623 /* 615 624 ** Used to pass information from the analyzer reader through to the 616 625 ** callback routine. 617 626 */
Changes to test/analyze8.test.
157 157 execsql { 158 158 UPDATE t1 SET a=b; 159 159 UPDATE t1 SET a=20 WHERE b>2; 160 160 ANALYZE; 161 161 SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno; 162 162 } 163 163 } {2 2 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38} 164 + 165 + 166 + 167 +# Verify that the 5th "cnt" column is added to the sqlite_stat2 table 168 +# on a full ANALYZE if the column is not already present. 169 +# 170 +do_test analyze8-4.0 { 171 + execsql { 172 + UPDATE t1 SET a=b; 173 + ANALYZE; 174 + PRAGMA writable_schema=ON; 175 + UPDATE sqlite_master 176 + SET sql='CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample)' 177 + WHERE name='sqlite_stat2'; 178 + } 179 + db close 180 + sqlite3 db test.db 181 + execsql { 182 + SELECT sample FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno 183 + } 184 +} {2 4 6 8 10 12 14 16 18 20} 185 +do_test analyze8-4.1 { 186 + catchsql {SELECT sample, cnt FROM sqlite_stat2} 187 +} {1 {no such column: cnt}} 188 +do_test analyze8-4.2 { 189 + execsql { 190 + ANALYZE; 191 + } 192 + db close; 193 + sqlite3 db test.db 194 + execsql { 195 + SELECT sample, +cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno; 196 + } 197 +} {2 2 4 2 6 2 8 2 10 2 12 2 14 2 16 2 18 2 20 2} 198 + 199 + 164 200 165 201 finish_test