SQLite

Check-in [794fde6f91]
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
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: 794fde6f918b405ebe47068dea76a2d3f7d97733
User & Date: drh 2011-08-07 00:21:17.038
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: d1248165e3 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: 794fde6f91 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: 6d1e2372fe user: drh tags: query-planner-tweaks)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/analyze.c.
︙
175
176
177
178
179
180
181









182
183
184
185
186
187
188

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197








+
+
+
+
+
+
+
+
+








        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);
︙
603
604
605
606
607
608
609
610

611
612
613
614
615
616
617

612
613
614
615
616
617
618

619
620
621
622
623
624
625
626








-
+








        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

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