/ Check-in [868279c7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Store the total number of rows as part of the ANALYZE statistics. (CVS 2563)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:868279c78e056d27b2d1bea81127fe689b2ce478
User & Date: drh 2005-07-23 14:52:12
Context
2005-07-23
22:59
A new optimizer that breaks a lot of tests. But none of them critically, I think. Nevertheless, there is a lot of work ahead to stabilize the code. (CVS 2564) check-in: 86ce56cc user: drh tags: trunk
14:52
Store the total number of rows as part of the ANALYZE statistics. (CVS 2563) check-in: 868279c7 user: drh tags: trunk
03:18
The results of ANALYZE are now loaded into internal data structures where they can be used. But they are not actually used yet. (CVS 2562) check-in: 1996bacf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
202
203
204
205
...
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
...
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code associated with the ANALYZE command.
**
** @(#) $Id: analyze.c,v 1.4 2005/07/23 03:18:40 drh Exp $
*/
#ifndef SQLITE_OMIT_ANALYZE
#include "sqliteInt.h"

/*
** This routine generates code that opens the sqlite_stat1 table on cursor
** iStatCur.
................................................................................
    sqlite3VdbeAddOp(v, OP_Close, iIdxCur, 0);

    /* Store the results.  
    **
    ** The result is a single row of the sqlite_stmt1 table.  The first
    ** two columns are the names of the table and index.  The third column
    ** is a string composed of a list of integer statistics about the
    ** index.  There is one integer in the list for each column of the table.

    ** This integer is a guess of how many rows of the table the index will
    ** select.  If D is the count of distinct values and K is the total
    ** number of rows, then the integer is computed as:

    **
    **        I = (K+D-1)/D
    **
    ** If K==0 then no entry is made into the sqlite_stat1 table.  
    ** If K>0 then it is always the case the D>0 so division by zero
    ** is never possible.
    */
    sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
    addr = sqlite3VdbeAddOp(v, OP_IfNot, 0, 0);
    sqlite3VdbeAddOp(v, OP_NewRowid, iStatCur, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pIdx->zName, 0);


    for(i=0; i<nCol; i++){
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
      sqlite3VdbeAddOp(v, OP_Add, 0, 0);
      sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
      sqlite3VdbeAddOp(v, OP_Divide, 0, 0);
      if( i==nCol-1 ){
        if( i>0 ){
          sqlite3VdbeAddOp(v, OP_Concat, nCol*2-3, 0);
        }
      }else{
        if( i==0 ){
          sqlite3VdbeOp3(v, OP_String8, 0, 0, " ", 0);
        }else{
          sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
        }
      }
    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0);
    sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0);
    sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v));
  }
}
................................................................................
    return 0;
  }
  pIndex = sqlite3FindIndex(pInfo->db, argv[0], pInfo->zDatabase);
  if( pIndex==0 ){
    return 0;
  }
  z = argv[1];
  for(i=0; *z && i<pIndex->nColumn; i++){
    v = 0;
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    pIndex->aiRowEst[i] = v;
    if( *z==' ' ) z++;
................................................................................
  HashElem *i;
  char *zSql;

  /* Clear any prior statistics */
  for(i=sqliteHashFirst(&db->aDb[iDb].idxHash); i; i=sqliteHashNext(i)){
    Index *pIdx = sqliteHashData(i);
    int j;
    for(j=pIdx->nColumn-1; j>=0; j--){
      pIdx->aiRowEst[j] = 100;
    }
  }

  /* Check to make sure the sqlite_stat1 table existss */
  sInfo.db = db;
  sInfo.zDatabase = db->aDb[iDb].zName;
  if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){







|







 







|
>
|
|
|
>












>
>








<
|
<

<
<
<
|
<







 







|







 







|
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
202
203
...
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code associated with the ANALYZE command.
**
** @(#) $Id: analyze.c,v 1.5 2005/07/23 14:52:12 drh Exp $
*/
#ifndef SQLITE_OMIT_ANALYZE
#include "sqliteInt.h"

/*
** This routine generates code that opens the sqlite_stat1 table on cursor
** iStatCur.
................................................................................
    sqlite3VdbeAddOp(v, OP_Close, iIdxCur, 0);

    /* Store the results.  
    **
    ** The result is a single row of the sqlite_stmt1 table.  The first
    ** two columns are the names of the table and index.  The third column
    ** is a string composed of a list of integer statistics about the
    ** index.  The first integer in the list is the total number of entires
    ** in the index.  There is one additional integer in the list for each
    ** column of the table.  This additional integer is a guess of how many
    ** rows of the table the index will select.  If D is the count of distinct
    ** values and K is the total number of rows, then the integer is computed
    ** as:
    **
    **        I = (K+D-1)/D
    **
    ** If K==0 then no entry is made into the sqlite_stat1 table.  
    ** If K>0 then it is always the case the D>0 so division by zero
    ** is never possible.
    */
    sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
    addr = sqlite3VdbeAddOp(v, OP_IfNot, 0, 0);
    sqlite3VdbeAddOp(v, OP_NewRowid, iStatCur, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pIdx->zName, 0);
    sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, " ", 0);
    for(i=0; i<nCol; i++){
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem, 0);
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
      sqlite3VdbeAddOp(v, OP_Add, 0, 0);
      sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
      sqlite3VdbeAddOp(v, OP_MemLoad, iMem+i+1, 0);
      sqlite3VdbeAddOp(v, OP_Divide, 0, 0);
      if( i==nCol-1 ){

        sqlite3VdbeAddOp(v, OP_Concat, nCol*2-1, 0);

      }else{



        sqlite3VdbeAddOp(v, OP_Dup, 1, 0);

      }
    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0);
    sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0);
    sqlite3VdbeChangeP2(v, addr, sqlite3VdbeCurrentAddr(v));
  }
}
................................................................................
    return 0;
  }
  pIndex = sqlite3FindIndex(pInfo->db, argv[0], pInfo->zDatabase);
  if( pIndex==0 ){
    return 0;
  }
  z = argv[1];
  for(i=0; *z && i<=pIndex->nColumn; i++){
    v = 0;
    while( (c=z[0])>='0' && c<='9' ){
      v = v*10 + c - '0';
      z++;
    }
    pIndex->aiRowEst[i] = v;
    if( *z==' ' ) z++;
................................................................................
  HashElem *i;
  char *zSql;

  /* Clear any prior statistics */
  for(i=sqliteHashFirst(&db->aDb[iDb].idxHash); i; i=sqliteHashNext(i)){
    Index *pIdx = sqliteHashData(i);
    int j;
    for(j=0; j<=pIdx->nColumn; j++){
      pIdx->aiRowEst[j] = j<100 ? 1000*(100-j) : 100;
    }
  }

  /* Check to make sure the sqlite_stat1 table existss */
  sInfo.db = db;
  sInfo.zDatabase = db->aDb[iDb].zName;
  if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){

Changes to src/build.c.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
....
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.334 2005/07/23 03:18:40 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
................................................................................
    pList = sqlite3ExprListAppend(0, 0, &nullId);
    if( pList==0 ) goto exit_create_index;
  }

  /* 
  ** Allocate the index structure. 
  */
  pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 +
                        (sizeof(int)*2 + sizeof(CollSeq*))*pList->nExpr );
  if( sqlite3_malloc_failed ) goto exit_create_index;
  pIndex->aiColumn = (int*)&pIndex->keyInfo.aColl[pList->nExpr];
  pIndex->aiRowEst = &pIndex->aiColumn[pList->nExpr];
  pIndex->zName = (char*)&pIndex->aiRowEst[pList->nExpr];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nExpr;
  pIndex->onError = onError;
  pIndex->autoIndex = pName==0;
  pIndex->iDb = iDb;








|







 







|




|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
....
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.335 2005/07/23 14:52:12 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
................................................................................
    pList = sqlite3ExprListAppend(0, 0, &nullId);
    if( pList==0 ) goto exit_create_index;
  }

  /* 
  ** Allocate the index structure. 
  */
  pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 + sizeof(int) +
                        (sizeof(int)*2 + sizeof(CollSeq*))*pList->nExpr );
  if( sqlite3_malloc_failed ) goto exit_create_index;
  pIndex->aiColumn = (int*)&pIndex->keyInfo.aColl[pList->nExpr];
  pIndex->aiRowEst = &pIndex->aiColumn[pList->nExpr];
  pIndex->zName = (char*)&pIndex->aiRowEst[pList->nExpr+1];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nExpr;
  pIndex->onError = onError;
  pIndex->autoIndex = pName==0;
  pIndex->iDb = iDb;

Changes to test/analyze.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the ANALYZE command.
#
# $Id: analyze.test,v 1.1 2005/07/23 02:17:03 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze} {
................................................................................
do_test analyze-3.1 {
  execsql {
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(1,3);
    ANALYZE main.t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 2 t1i2 1 t1i3 {2 1}}
do_test analyze-3.2 {
  execsql {
    INSERT INTO t1 VALUES(1,4);
    INSERT INTO t1 VALUES(1,5);
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 4 t1i2 1 t1i3 {4 1}}
do_test analyze-3.3 {
  execsql {
    INSERT INTO t1 VALUES(2,5);
    ANALYZE main;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 3 t1i2 2 t1i3 {3 1}}
do_test analyze-3.4 {
  execsql {
    CREATE TABLE t2 AS SELECT * FROM t1;
    CREATE INDEX t2i1 ON t2(a);
    CREATE INDEX t2i2 ON t2(b);
    CREATE INDEX t2i3 ON t2(a,b);
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 3 t1i2 2 t1i3 {3 1} t2i1 3 t2i2 2 t2i3 {3 1}}
do_test analyze-3.5 {
  execsql {
    DROP INDEX t2i3;
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 3 t1i2 2 t1i3 {3 1} t2i1 3 t2i2 2 t2i3 {3 1}}
do_test analyze-3.6 {
  execsql {
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 3 t1i2 2 t1i3 {3 1} t2i1 3 t2i2 2}
do_test analyze-3.7 {
  execsql {
    DROP INDEX t2i2;
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 3 t1i2 2 t1i3 {3 1}}
do_test analyze-3.8 {
  execsql {
    CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
    CREATE INDEX t3i1 ON t3(a);
    CREATE INDEX t3i2 ON t3(a,b,c,d);
    CREATE INDEX t3i3 ON t3(d,b,c,a);
    DROP TABLE t1;
    DROP TABLE t2;
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t3i1 3 t3i2 {3 1 1 1} t3i3 {5 2 1 1}}


finish_test







|







 







|







|






|









|






|





|






|











|



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the ANALYZE command.
#
# $Id: analyze.test,v 1.2 2005/07/23 14:52:12 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze} {
................................................................................
do_test analyze-3.1 {
  execsql {
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(1,3);
    ANALYZE main.t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {2 2} t1i2 {2 1} t1i3 {2 2 1}}
do_test analyze-3.2 {
  execsql {
    INSERT INTO t1 VALUES(1,4);
    INSERT INTO t1 VALUES(1,5);
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {4 4} t1i2 {4 1} t1i3 {4 4 1}}
do_test analyze-3.3 {
  execsql {
    INSERT INTO t1 VALUES(2,5);
    ANALYZE main;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}
do_test analyze-3.4 {
  execsql {
    CREATE TABLE t2 AS SELECT * FROM t1;
    CREATE INDEX t2i1 ON t2(a);
    CREATE INDEX t2i2 ON t2(b);
    CREATE INDEX t2i3 ON t2(a,b);
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
do_test analyze-3.5 {
  execsql {
    DROP INDEX t2i3;
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
do_test analyze-3.6 {
  execsql {
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
do_test analyze-3.7 {
  execsql {
    DROP INDEX t2i2;
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}
do_test analyze-3.8 {
  execsql {
    CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
    CREATE INDEX t3i1 ON t3(a);
    CREATE INDEX t3i2 ON t3(a,b,c,d);
    CREATE INDEX t3i3 ON t3(d,b,c,a);
    DROP TABLE t1;
    DROP TABLE t2;
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}


finish_test