/ Check-in [794fde6f]
Login

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: 794fde6f918b405ebe47068dea76a2d3f7d97733
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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