Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | If there is data in both the sqlite_stat4 and sqlite_stat3 tables for a single index, ignore the sqlite_stat3 records. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sqlite_stat4 |
Files: | files | file ages | folders |
SHA1: |
2a41736728d83a777ea8112da927cb04 |
User & Date: | dan 2013-08-12 17:31:32.368 |
Context
2013-08-12
| ||
20:14 | If ENABLE_STAT3 is defined but ENABLE_STAT4 is not, have ANALYZE create and populate the sqlite_stat3 table instead of sqlite_stat4. (check-in: cca8bf4372 user: dan tags: sqlite_stat4) | |
17:31 | If there is data in both the sqlite_stat4 and sqlite_stat3 tables for a single index, ignore the sqlite_stat3 records. (check-in: 2a41736728 user: dan tags: sqlite_stat4) | |
17:00 | Handle a NULL input to decodeIntArray() that can result from a prior OOM. (check-in: fa1588adab user: drh tags: sqlite_stat4) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
1311 1312 1313 1314 1315 1316 1317 | int i; /* Bytes of space required */ tRowcnt *pSpace; zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = sqlite3FindIndex(db, zIndex, zDb); | > > > | < | 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 | int i; /* Bytes of space required */ tRowcnt *pSpace; zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = sqlite3FindIndex(db, zIndex, zDb); assert( pIdx==0 || bStat3 || pIdx->nSample==0 ); /* Index.nSample is non-zero at this point if data has already been ** loaded from the stat4 table. In this case ignore stat3 data. */ if( pIdx==0 || pIdx->nSample ) continue; if( bStat3==0 ){ nIdxCol = pIdx->nColumn+1; nAvgCol = pIdx->nColumn; } pIdx->nSample = nSample; nByte = sizeof(IndexSample) * nSample; nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample; |
︙ | ︙ | |||
1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 | if( pIdx==pPrevIdx ){ idx++; }else{ pPrevIdx = pIdx; idx = 0; } assert( idx<pIdx->nSample ); pSample = &pIdx->aSample[idx]; if( bStat3==0 ){ nCol = pIdx->nColumn+1; } decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0); | > > > | 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 | if( pIdx==pPrevIdx ){ idx++; }else{ pPrevIdx = pIdx; idx = 0; } assert( idx<pIdx->nSample ); /* This next condition is true if data has already been loaded from ** the sqlite_stat4 table. In this case ignore stat3 data. */ if( bStat3 && pIdx->aSample[idx].anEq[0] ) continue; pSample = &pIdx->aSample[idx]; if( bStat3==0 ){ nCol = pIdx->nColumn+1; } decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0); |
︙ | ︙ |
Changes to test/analyzeA.test.
︙ | ︙ | |||
19 20 21 22 23 24 25 | set testprefix analyzeA ifcapable !stat4&&!stat3 { finish_test return } | > > | | > | | < > > > | > > > > > > > > > > > > > > > > > > > > | > > > > | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | set testprefix analyzeA ifcapable !stat4&&!stat3 { finish_test return } # Populate the stat3 table according to the current contents of the db # proc populate_stat3 {{bDropTable 1}} { # Open a second connection on database "test.db" and run ANALYZE. If this # is an ENABLE_STAT3 build, this is all that is required to create and # populate the sqlite_stat3 table. # sqlite3 db2 test.db execsql { ANALYZE } # Now, if this is an ENABLE_STAT4 build, create and populate the # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE # above. Then drop the sqlite_stat4 table. # ifcapable stat4 { db2 func lindex lindex execsql { PRAGMA writable_schema = on; CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample); INSERT INTO sqlite_stat3 SELECT DISTINCT tbl, idx, lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0) FROM sqlite_stat4; } db2 if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 } execsql { PRAGMA writable_schema = off } } # Modify the database schema cookie to ensure that the other connection # reloads the schema. # execsql { CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } # Populate the stat4 table according to the current contents of the db. # Leave deceptive data in the stat3 table. This data should be ignored # in favour of that from the stat4 table. # proc populate_both {} { populate_stat3 0 sqlite3 db2 test.db execsql { PRAGMA writable_schema = on; UPDATE sqlite_stat3 SET idx = CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b' END; PRAGMA writable_schema = off; CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } # Populate the stat4 table according to the current contents of the db # proc populate_stat4 {} { execsql { ANALYZE } # ifcapable stat3 { # execsql { # PRAGMA writable_schema = on; # CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); # INSERT INTO sqlite_stat4 SELECT # tbl, idx, nlt, neq, ndlt, # test_extract(sample, 1) # FROM sqlite_stat4; # DROP TABLE sqlite_stat4; # PRAGMA writable_schema = off; # ANALYZE sqlite_master; # } # } } foreach {tn analyze_cmd} { 1 populate_stat4 2 populate_stat3 3 populate_both } { reset_db do_test 1.$tn.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) } for {set i 0} {$i < 100} {incr i} { set c [expr int(pow(1.1,$i)/100)] set b [expr 125 - int(pow(1.1,99-$i))/100] execsql {INSERT INTO t1 VALUES($i, $b, $c)} |
︙ | ︙ | |||
106 107 108 109 110 111 112 | do_execsql_test 1.$tn.3.1 { SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 } {6} do_execsql_test 1.$tn.3.2 { SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 } {90} | < | 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | do_execsql_test 1.$tn.3.1 { SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 } {6} do_execsql_test 1.$tn.3.2 { SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 } {90} do_execsql_test 1.$tn.3.3 { SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125 } {90} do_execsql_test 1.$tn.3.4 { SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125 } {6} |
︙ | ︙ |