/ Check-in [794fde6f]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

175
176
177
178
179
180
181









182
183
184
185
186
187
188
...
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
        sqlite3NestedParse(pParse,
           "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere
        );
      }else{
        /* The sqlite_stat[12] table already exists.  Delete all rows. */
        sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
      }









    }
  }

  /* Open the sqlite_stat[12] tables for writing. */
  for(i=0; i<ArraySize(aTable); i++){
    sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
................................................................................
        if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){
          analyzeTable(pParse, pIdx->pTable, pIdx);
        }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){
          analyzeTable(pParse, pTab, 0);
        }
        sqlite3DbFree(db, z);
      }
    }   
  }
}

/*
** Used to pass information from the analyzer reader through to the
** callback routine.
*/







>
>
>
>
>
>
>
>
>







 







|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
        sqlite3NestedParse(pParse,
           "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere
        );
      }else{
        /* The sqlite_stat[12] table already exists.  Delete all rows. */
        sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
      }
#ifdef SQLITE_ENABLE_STAT2
      if( i==1 && iDb!=1 && pStat->nCol==4 ){
        sqlite3NestedParse(pParse,
           "UPDATE %Q.sqlite_master SET sql='CREATE TABLE sqlite_stat2(%s)'"
           " WHERE name='sqlite_stat2'", pDb->zName, aTable[i].zCols
        );
        sqlite3ChangeCookie(pParse, iDb);
      }
#endif
    }
  }

  /* Open the sqlite_stat[12] tables for writing. */
  for(i=0; i<ArraySize(aTable); i++){
    sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
................................................................................
        if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){
          analyzeTable(pParse, pIdx->pTable, pIdx);
        }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){
          analyzeTable(pParse, pTab, 0);
        }
        sqlite3DbFree(db, z);
      }
    }
  }
}

/*
** Used to pass information from the analyzer reader through to the
** callback routine.
*/

Changes to test/analyze8.test.

157
158
159
160
161
162
163
164




































165
  execsql {
    UPDATE t1 SET a=b;
    UPDATE t1 SET a=20 WHERE b>2;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 2 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38}





































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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
191
192
193
194
195
196
197
198
199
200
201
  execsql {
    UPDATE t1 SET a=b;
    UPDATE t1 SET a=20 WHERE b>2;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 2 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38}



# Verify that the 5th "cnt" column is added to the sqlite_stat2 table
# on a full ANALYZE if the column is not already present.
#
do_test analyze8-4.0 {
  execsql {
    UPDATE t1 SET a=b;
    ANALYZE;
    PRAGMA writable_schema=ON;
    UPDATE sqlite_master 
       SET sql='CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample)'
     WHERE name='sqlite_stat2';
  }
  db close
  sqlite3 db test.db
  execsql {
    SELECT sample FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno
  }
} {2 4 6 8 10 12 14 16 18 20}
do_test analyze8-4.1 {
  catchsql {SELECT sample, cnt FROM sqlite_stat2}
} {1 {no such column: cnt}}
do_test analyze8-4.2 {
  execsql {
    ANALYZE;
  }
  db close;
  sqlite3 db test.db
  execsql {
    SELECT sample, +cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 2 4 2 6 2 8 2 10 2 12 2 14 2 16 2 18 2 20 2}



finish_test