1 : /*
2 : ** 2005 July 8
3 : **
4 : ** The author disclaims copyright to this source code. In place of
5 : ** a legal notice, here is a blessing:
6 : **
7 : ** May you do good and not evil.
8 : ** May you find forgiveness for yourself and forgive others.
9 : ** May you share freely, never taking more than you give.
10 : **
11 : *************************************************************************
12 : ** This file contains code associated with the ANALYZE command.
13 : **
14 : ** @(#) $Id: analyze.c 233497 2007-04-09 16:35:11Z iliaa $
15 : */
16 : #ifndef SQLITE_OMIT_ANALYZE
17 : #include "sqliteInt.h"
18 :
19 : /*
20 : ** This routine generates code that opens the sqlite_stat1 table on cursor
21 : ** iStatCur.
22 : **
23 : ** If the sqlite_stat1 tables does not previously exist, it is created.
24 : ** If it does previously exist, all entires associated with table zWhere
25 : ** are removed. If zWhere==0 then all entries are removed.
26 : */
27 : static void openStatTable(
28 : Parse *pParse, /* Parsing context */
29 : int iDb, /* The database we are looking in */
30 : int iStatCur, /* Open the sqlite_stat1 table on this cursor */
31 : const char *zWhere /* Delete entries associated with this table */
32 0 : ){
33 0 : sqlite3 *db = pParse->db;
34 : Db *pDb;
35 : int iRootPage;
36 : Table *pStat;
37 0 : Vdbe *v = sqlite3GetVdbe(pParse);
38 :
39 0 : pDb = &db->aDb[iDb];
40 0 : if( (pStat = sqlite3FindTable(db, "sqlite_stat1", pDb->zName))==0 ){
41 : /* The sqlite_stat1 tables does not exist. Create it.
42 : ** Note that a side-effect of the CREATE TABLE statement is to leave
43 : ** the rootpage of the new table on the top of the stack. This is
44 : ** important because the OpenWrite opcode below will be needing it. */
45 0 : sqlite3NestedParse(pParse,
46 : "CREATE TABLE %Q.sqlite_stat1(tbl,idx,stat)",
47 : pDb->zName
48 : );
49 0 : iRootPage = 0; /* Cause rootpage to be taken from top of stack */
50 0 : }else if( zWhere ){
51 : /* The sqlite_stat1 table exists. Delete all entries associated with
52 : ** the table zWhere. */
53 0 : sqlite3NestedParse(pParse,
54 : "DELETE FROM %Q.sqlite_stat1 WHERE tbl=%Q",
55 : pDb->zName, zWhere
56 : );
57 0 : iRootPage = pStat->tnum;
58 : }else{
59 : /* The sqlite_stat1 table already exists. Delete all rows. */
60 0 : iRootPage = pStat->tnum;
61 0 : sqlite3VdbeAddOp(v, OP_Clear, pStat->tnum, iDb);
62 : }
63 :
64 : /* Open the sqlite_stat1 table for writing. Unless it was created
65 : ** by this vdbe program, lock it for writing at the shared-cache level.
66 : ** If this vdbe did create the sqlite_stat1 table, then it must have
67 : ** already obtained a schema-lock, making the write-lock redundant.
68 : */
69 0 : if( iRootPage>0 ){
70 0 : sqlite3TableLock(pParse, iDb, iRootPage, 1, "sqlite_stat1");
71 : }
72 0 : sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
73 0 : sqlite3VdbeAddOp(v, OP_OpenWrite, iStatCur, iRootPage);
74 0 : sqlite3VdbeAddOp(v, OP_SetNumColumns, iStatCur, 3);
75 0 : }
76 :
77 : /*
78 : ** Generate code to do an analysis of all indices associated with
79 : ** a single table.
80 : */
81 : static void analyzeOneTable(
82 : Parse *pParse, /* Parser context */
83 : Table *pTab, /* Table whose indices are to be analyzed */
84 : int iStatCur, /* Cursor that writes to the sqlite_stat1 table */
85 : int iMem /* Available memory locations begin here */
86 0 : ){
87 : Index *pIdx; /* An index to being analyzed */
88 : int iIdxCur; /* Cursor number for index being analyzed */
89 : int nCol; /* Number of columns in the index */
90 : Vdbe *v; /* The virtual machine being built up */
91 : int i; /* Loop counter */
92 : int topOfLoop; /* The top of the loop */
93 : int endOfLoop; /* The end of the loop */
94 : int addr; /* The address of an instruction */
95 : int iDb; /* Index of database containing pTab */
96 :
97 0 : v = sqlite3GetVdbe(pParse);
98 0 : if( pTab==0 || pTab->pIndex==0 ){
99 : /* Do no analysis for tables that have no indices */
100 0 : return;
101 : }
102 :
103 0 : iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
104 : assert( iDb>=0 );
105 : #ifndef SQLITE_OMIT_AUTHORIZATION
106 0 : if( sqlite3AuthCheck(pParse, SQLITE_ANALYZE, pTab->zName, 0,
107 : pParse->db->aDb[iDb].zName ) ){
108 0 : return;
109 : }
110 : #endif
111 :
112 : /* Establish a read-lock on the table at the shared-cache level. */
113 0 : sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
114 :
115 0 : iIdxCur = pParse->nTab;
116 0 : for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
117 0 : KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
118 :
119 : /* Open a cursor to the index to be analyzed
120 : */
121 : assert( iDb==sqlite3SchemaToIndex(pParse->db, pIdx->pSchema) );
122 0 : sqlite3VdbeAddOp(v, OP_Integer, iDb, 0);
123 : VdbeComment((v, "# %s", pIdx->zName));
124 0 : sqlite3VdbeOp3(v, OP_OpenRead, iIdxCur, pIdx->tnum,
125 : (char *)pKey, P3_KEYINFO_HANDOFF);
126 0 : nCol = pIdx->nColumn;
127 0 : if( iMem+nCol*2>=pParse->nMem ){
128 0 : pParse->nMem = iMem+nCol*2+1;
129 : }
130 0 : sqlite3VdbeAddOp(v, OP_SetNumColumns, iIdxCur, nCol+1);
131 :
132 : /* Memory cells are used as follows:
133 : **
134 : ** mem[iMem]: The total number of rows in the table.
135 : ** mem[iMem+1]: Number of distinct values in column 1
136 : ** ...
137 : ** mem[iMem+nCol]: Number of distinct values in column N
138 : ** mem[iMem+nCol+1] Last observed value of column 1
139 : ** ...
140 : ** mem[iMem+nCol+nCol]: Last observed value of column N
141 : **
142 : ** Cells iMem through iMem+nCol are initialized to 0. The others
143 : ** are initialized to NULL.
144 : */
145 0 : for(i=0; i<=nCol; i++){
146 0 : sqlite3VdbeAddOp(v, OP_MemInt, 0, iMem+i);
147 : }
148 0 : for(i=0; i<nCol; i++){
149 0 : sqlite3VdbeAddOp(v, OP_MemNull, iMem+nCol+i+1, 0);
150 : }
151 :
152 : /* Do the analysis.
153 : */
154 0 : endOfLoop = sqlite3VdbeMakeLabel(v);
155 0 : sqlite3VdbeAddOp(v, OP_Rewind, iIdxCur, endOfLoop);
156 0 : topOfLoop = sqlite3VdbeCurrentAddr(v);
157 0 : sqlite3VdbeAddOp(v, OP_MemIncr, 1, iMem);
158 0 : for(i=0; i<nCol; i++){
159 0 : sqlite3VdbeAddOp(v, OP_Column, iIdxCur, i);
160 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem+nCol+i+1, 0);
161 0 : sqlite3VdbeAddOp(v, OP_Ne, 0x100, 0);
162 : }
163 0 : sqlite3VdbeAddOp(v, OP_Goto, 0, endOfLoop);
164 0 : for(i=0; i<nCol; i++){
165 0 : addr = sqlite3VdbeAddOp(v, OP_MemIncr, 1, iMem+i+1);
166 0 : sqlite3VdbeChangeP2(v, topOfLoop + 3*i + 3, addr);
167 0 : sqlite3VdbeAddOp(v, OP_Column, iIdxCur, i);
168 0 : sqlite3VdbeAddOp(v, OP_MemStore, iMem+nCol+i+1, 1);
169 : }
170 0 : sqlite3VdbeResolveLabel(v, endOfLoop);
171 0 : sqlite3VdbeAddOp(v, OP_Next, iIdxCur, topOfLoop);
172 0 : sqlite3VdbeAddOp(v, OP_Close, iIdxCur, 0);
173 :
174 : /* Store the results.
175 : **
176 : ** The result is a single row of the sqlite_stmt1 table. The first
177 : ** two columns are the names of the table and index. The third column
178 : ** is a string composed of a list of integer statistics about the
179 : ** index. The first integer in the list is the total number of entires
180 : ** in the index. There is one additional integer in the list for each
181 : ** column of the table. This additional integer is a guess of how many
182 : ** rows of the table the index will select. If D is the count of distinct
183 : ** values and K is the total number of rows, then the integer is computed
184 : ** as:
185 : **
186 : ** I = (K+D-1)/D
187 : **
188 : ** If K==0 then no entry is made into the sqlite_stat1 table.
189 : ** If K>0 then it is always the case the D>0 so division by zero
190 : ** is never possible.
191 : */
192 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
193 0 : addr = sqlite3VdbeAddOp(v, OP_IfNot, 0, 0);
194 0 : sqlite3VdbeAddOp(v, OP_NewRowid, iStatCur, 0);
195 0 : sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
196 0 : sqlite3VdbeOp3(v, OP_String8, 0, 0, pIdx->zName, 0);
197 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
198 0 : sqlite3VdbeOp3(v, OP_String8, 0, 0, " ", 0);
199 0 : for(i=0; i<nCol; i++){
200 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
201 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
202 0 : sqlite3VdbeAddOp(v, OP_Add, 0, 0);
203 0 : sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
204 0 : sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
205 0 : sqlite3VdbeAddOp(v, OP_Divide, 0, 0);
206 0 : sqlite3VdbeAddOp(v, OP_ToInt, 0, 0);
207 0 : if( i==nCol-1 ){
208 0 : sqlite3VdbeAddOp(v, OP_Concat, nCol*2-1, 0);
209 : }else{
210 0 : sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
211 : }
212 : }
213 0 : sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "aaa", 0);
214 0 : sqlite3VdbeAddOp(v, OP_Insert, iStatCur, OPFLAG_APPEND);
215 0 : sqlite3VdbeJumpHere(v, addr);
216 : }
217 : }
218 :
219 : /*
220 : ** Generate code that will cause the most recent index analysis to
221 : ** be laoded into internal hash tables where is can be used.
222 : */
223 0 : static void loadAnalysis(Parse *pParse, int iDb){
224 0 : Vdbe *v = sqlite3GetVdbe(pParse);
225 0 : sqlite3VdbeAddOp(v, OP_LoadAnalysis, iDb, 0);
226 0 : }
227 :
228 : /*
229 : ** Generate code that will do an analysis of an entire database
230 : */
231 0 : static void analyzeDatabase(Parse *pParse, int iDb){
232 0 : sqlite3 *db = pParse->db;
233 0 : Schema *pSchema = db->aDb[iDb].pSchema; /* Schema of database iDb */
234 : HashElem *k;
235 : int iStatCur;
236 : int iMem;
237 :
238 0 : sqlite3BeginWriteOperation(pParse, 0, iDb);
239 0 : iStatCur = pParse->nTab++;
240 0 : openStatTable(pParse, iDb, iStatCur, 0);
241 0 : iMem = pParse->nMem;
242 0 : for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
243 0 : Table *pTab = (Table*)sqliteHashData(k);
244 0 : analyzeOneTable(pParse, pTab, iStatCur, iMem);
245 : }
246 0 : loadAnalysis(pParse, iDb);
247 0 : }
248 :
249 : /*
250 : ** Generate code that will do an analysis of a single table in
251 : ** a database.
252 : */
253 0 : static void analyzeTable(Parse *pParse, Table *pTab){
254 : int iDb;
255 : int iStatCur;
256 :
257 : assert( pTab!=0 );
258 0 : iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
259 0 : sqlite3BeginWriteOperation(pParse, 0, iDb);
260 0 : iStatCur = pParse->nTab++;
261 0 : openStatTable(pParse, iDb, iStatCur, pTab->zName);
262 0 : analyzeOneTable(pParse, pTab, iStatCur, pParse->nMem);
263 0 : loadAnalysis(pParse, iDb);
264 0 : }
265 :
266 : /*
267 : ** Generate code for the ANALYZE command. The parser calls this routine
268 : ** when it recognizes an ANALYZE command.
269 : **
270 : ** ANALYZE -- 1
271 : ** ANALYZE <database> -- 2
272 : ** ANALYZE ?<database>.?<tablename> -- 3
273 : **
274 : ** Form 1 causes all indices in all attached databases to be analyzed.
275 : ** Form 2 analyzes all indices the single database named.
276 : ** Form 3 analyzes all indices associated with the named table.
277 : */
278 0 : void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2){
279 0 : sqlite3 *db = pParse->db;
280 : int iDb;
281 : int i;
282 : char *z, *zDb;
283 : Table *pTab;
284 : Token *pTableName;
285 :
286 : /* Read the database schema. If an error occurs, leave an error message
287 : ** and code in pParse and return NULL. */
288 0 : if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
289 0 : return;
290 : }
291 :
292 0 : if( pName1==0 ){
293 : /* Form 1: Analyze everything */
294 0 : for(i=0; i<db->nDb; i++){
295 0 : if( i==1 ) continue; /* Do not analyze the TEMP database */
296 0 : analyzeDatabase(pParse, i);
297 : }
298 0 : }else if( pName2==0 || pName2->n==0 ){
299 : /* Form 2: Analyze the database or table named */
300 0 : iDb = sqlite3FindDb(db, pName1);
301 0 : if( iDb>=0 ){
302 0 : analyzeDatabase(pParse, iDb);
303 : }else{
304 0 : z = sqlite3NameFromToken(pName1);
305 0 : pTab = sqlite3LocateTable(pParse, z, 0);
306 0 : sqliteFree(z);
307 0 : if( pTab ){
308 0 : analyzeTable(pParse, pTab);
309 : }
310 : }
311 : }else{
312 : /* Form 3: Analyze the fully qualified table name */
313 0 : iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pTableName);
314 0 : if( iDb>=0 ){
315 0 : zDb = db->aDb[iDb].zName;
316 0 : z = sqlite3NameFromToken(pTableName);
317 0 : pTab = sqlite3LocateTable(pParse, z, zDb);
318 0 : sqliteFree(z);
319 0 : if( pTab ){
320 0 : analyzeTable(pParse, pTab);
321 : }
322 : }
323 : }
324 : }
325 :
326 : /*
327 : ** Used to pass information from the analyzer reader through to the
328 : ** callback routine.
329 : */
330 : typedef struct analysisInfo analysisInfo;
331 : struct analysisInfo {
332 : sqlite3 *db;
333 : const char *zDatabase;
334 : };
335 :
336 : /*
337 : ** This callback is invoked once for each index when reading the
338 : ** sqlite_stat1 table.
339 : **
340 : ** argv[0] = name of the index
341 : ** argv[1] = results of analysis - on integer for each column
342 : */
343 0 : static int analysisLoader(void *pData, int argc, char **argv, char **azNotUsed){
344 0 : analysisInfo *pInfo = (analysisInfo*)pData;
345 : Index *pIndex;
346 : int i, c;
347 : unsigned int v;
348 : const char *z;
349 :
350 : assert( argc==2 );
351 0 : if( argv==0 || argv[0]==0 || argv[1]==0 ){
352 0 : return 0;
353 : }
354 0 : pIndex = sqlite3FindIndex(pInfo->db, argv[0], pInfo->zDatabase);
355 0 : if( pIndex==0 ){
356 0 : return 0;
357 : }
358 0 : z = argv[1];
359 0 : for(i=0; *z && i<=pIndex->nColumn; i++){
360 0 : v = 0;
361 0 : while( (c=z[0])>='0' && c<='9' ){
362 0 : v = v*10 + c - '0';
363 0 : z++;
364 : }
365 0 : pIndex->aiRowEst[i] = v;
366 0 : if( *z==' ' ) z++;
367 : }
368 0 : return 0;
369 : }
370 :
371 : /*
372 : ** Load the content of the sqlite_stat1 table into the index hash tables.
373 : */
374 4 : void sqlite3AnalysisLoad(sqlite3 *db, int iDb){
375 : analysisInfo sInfo;
376 : HashElem *i;
377 : char *zSql;
378 :
379 : /* Clear any prior statistics */
380 4 : for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
381 0 : Index *pIdx = sqliteHashData(i);
382 0 : sqlite3DefaultRowEst(pIdx);
383 : }
384 :
385 : /* Check to make sure the sqlite_stat1 table existss */
386 4 : sInfo.db = db;
387 4 : sInfo.zDatabase = db->aDb[iDb].zName;
388 4 : if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){
389 4 : return;
390 : }
391 :
392 :
393 : /* Load new statistics out of the sqlite_stat1 table */
394 0 : zSql = sqlite3MPrintf("SELECT idx, stat FROM %Q.sqlite_stat1",
395 : sInfo.zDatabase);
396 0 : sqlite3SafetyOff(db);
397 0 : sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
398 0 : sqlite3SafetyOn(db);
399 0 : sqliteFree(zSql);
400 : }
401 :
402 :
403 : #endif /* SQLITE_OMIT_ANALYZE */
|
