Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Experimental est_count pragma. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | est_count_pragma |
Files: | files | file ages | folders |
SHA1: |
340822afbe0c6b47fdd789c56acd9e10 |
User & Date: | drh 2016-10-20 22:02:43.512 |
Context
2016-10-21
| ||
15:36 | Fix problems in the est_count pragma for indexes and WITHOUT ROWID tables. (check-in: c39fd9b8f1 user: drh tags: est_count_pragma) | |
2016-10-20
| ||
22:02 | Experimental est_count pragma. (check-in: 340822afbe user: drh tags: est_count_pragma) | |
18:20 | Add the ability for the PRAGMA statement to accept multiple arguments. Currently all arguments other than the first are ignored. (Leaf check-in: fd81d8a430 user: drh tags: multi-arg-pragma) | |
Changes
Changes to src/btree.c.
︙ | ︙ | |||
5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 5045 | pCur->curFlags &= ~BTCF_AtLast; } } } return rc; } /* Move the cursor so that it points to an entry near the key ** specified by pIdxKey or intKey. Return a success code. ** ** For INTKEY tables, the intKey parameter is used. pIdxKey ** must be NULL. For index tables, pIdxKey is used and intKey ** is ignored. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 5049 5050 5051 5052 5053 5054 5055 5056 5057 5058 5059 5060 5061 5062 5063 5064 5065 5066 5067 5068 5069 5070 5071 5072 5073 5074 5075 5076 5077 5078 5079 5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 5107 5108 | pCur->curFlags &= ~BTCF_AtLast; } } } return rc; } /* ** Move the cursor pCur to a location within its b-tree that is ** approximately the x/1e9*nRow entry in the table, assuming the ** table contains nRow entries. So, in other words, if x==0 move ** to the first entry and if x=1e9 move to the last entry and if ** x=5e8 move to the middle entry. The final landing spot is ** approximate. ** ** Write an estimate of the number of entries in the b-tree into ** the *pnRowEst variable. ** ** This routine works by first moving the cursor to the root of the ** b-tree, then following pointers down to a leaf, selecting a pointer ** according to x. ** ** The estimated number of entries is found by multiplying the number of ** entries on the leaf page by the number of pointers at each layer of ** non-leaf pages. ** ** Return SQLITE_OK on success or an error code if problems are encountered. */ int sqlite3BtreeMovetoProportional( BtCursor *pCur, /* Cursor to reposition */ u32 x, /* approximate location to position the cursor */ sqlite3_uint64 *pnRowEst /* Write estimated entry count here */ ){ sqlite3_uint64 n = 1; int rc; Pgno chldPg; u32 mx = 1000000000; u32 perChild; u16 rx; MemPage *pPage; rc = moveToRoot(pCur); if( rc ) return rc; pPage = pCur->apPage[0]; while( !pPage->leaf ){ perChild = (mx+pPage->nCell)/(pPage->nCell+1); rx = x/perChild; x %= perChild; mx = perChild; if( rx>=pPage->nCell ){ chldPg = get4byte(&pPage->aData[pPage->hdrOffset+8]); }else{ chldPg = get4byte(findCell(pPage,rx)); } n *= pPage->nCell+1; pCur->aiIdx[pCur->iPage] = rx; rc = moveToChild(pCur, chldPg); if( rc ) return rc; pPage = pCur->apPage[pCur->iPage]; } *pnRowEst = n*pPage->nCell; if( pPage->nCell==0 ){ rx = 0; }else{ perChild = mx/pPage->nCell; rx = x/perChild; } pCur->aiIdx[pCur->iPage] = rx; return SQLITE_OK; } /* Move the cursor so that it points to an entry near the key ** specified by pIdxKey or intKey. Return a success code. ** ** For INTKEY tables, the intKey parameter is used. pIdxKey ** must be NULL. For index tables, pIdxKey is used and intKey ** is ignored. |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
234 235 236 237 238 239 240 241 242 243 244 245 246 247 | void sqlite3BtreeCursorZero(BtCursor*); void sqlite3BtreeCursorHintFlags(BtCursor*, unsigned); #ifdef SQLITE_ENABLE_CURSOR_HINTS void sqlite3BtreeCursorHint(BtCursor*, int, ...); #endif int sqlite3BtreeCloseCursor(BtCursor*); int sqlite3BtreeMovetoUnpacked( BtCursor*, UnpackedRecord *pUnKey, i64 intKey, int bias, int *pRes ); | > | 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | void sqlite3BtreeCursorZero(BtCursor*); void sqlite3BtreeCursorHintFlags(BtCursor*, unsigned); #ifdef SQLITE_ENABLE_CURSOR_HINTS void sqlite3BtreeCursorHint(BtCursor*, int, ...); #endif int sqlite3BtreeCloseCursor(BtCursor*); int sqlite3BtreeMovetoProportional(BtCursor*,u32,u64*); int sqlite3BtreeMovetoUnpacked( BtCursor*, UnpackedRecord *pUnKey, i64 intKey, int bias, int *pRes ); |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 | case PragTyp_CASE_SENSITIVE_LIKE: { if( zRight ){ sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0)); } } break; #ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX # define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100 #endif #ifndef SQLITE_OMIT_INTEGRITY_CHECK /* Pragma "quick_check" is reduced version of ** integrity_check designed to detect most database corruption | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 | case PragTyp_CASE_SENSITIVE_LIKE: { if( zRight ){ sqlite3RegisterLikeFunctions(db, sqlite3GetBoolean(zRight, 0)); } } break; /* ** PRAGMA est_row_cnt(<table-or-index>,<fraction>); ** ** Seek in <table-or-index> through the first <fraction> of rows and ** estimate the total number of rows based on the path back up to the ** root. */ case PragTyp_EST_COUNT: { Index *pIdx; Table *pTab; Pgno iRoot = 0; const char *zName = 0; int regResult; double r; static const char *azCol[] = { "est" }; if( (pIdx = sqlite3FindIndex(db, zRight, zDb))!=0 ){ iRoot = pIdx->tnum; zName = pIdx->zName; }else if( (pTab = sqlite3FindTable(db, zRight, zDb))!=0 ){ iRoot = pTab->tnum; zName = pTab->zName; }else{ break; } sqlite3TableLock(pParse, iDb, iRoot, 0, zName); regResult = ++pParse->nMem; setAllColumnNames(v, 1, azCol); if( pValues->nId>=2 ){ const char *z = pValues->a[1].zName; sqlite3AtoF(z, &r, sqlite3Strlen30(z), SQLITE_UTF8); }else{ r = 0.5; } if( r<0.0 ) r = 0.0; if( r>1.0 ) r = 1.0; sqlite3CodeVerifySchema(pParse, iDb); pParse->nTab++; sqlite3VdbeAddOp4Int(v, OP_OpenRead, 0, iRoot, iDb, 1); sqlite3VdbeAddOp3(v, OP_EstRowCnt, 0, regResult, (int)(r*1000000000)); sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 1); } break; #ifndef SQLITE_INTEGRITY_CHECK_ERROR_MAX # define SQLITE_INTEGRITY_CHECK_ERROR_MAX 100 #endif #ifndef SQLITE_OMIT_INTEGRITY_CHECK /* Pragma "quick_check" is reduced version of ** integrity_check designed to detect most database corruption |
︙ | ︙ |
Changes to src/pragma.h.
︙ | ︙ | |||
12 13 14 15 16 17 18 | #define PragTyp_CASE_SENSITIVE_LIKE 6 #define PragTyp_COLLATION_LIST 7 #define PragTyp_COMPILE_OPTIONS 8 #define PragTyp_DATA_STORE_DIRECTORY 9 #define PragTyp_DATABASE_LIST 10 #define PragTyp_DEFAULT_CACHE_SIZE 11 #define PragTyp_ENCODING 12 | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 12 13 14 15 16 17 18 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 | #define PragTyp_CASE_SENSITIVE_LIKE 6 #define PragTyp_COLLATION_LIST 7 #define PragTyp_COMPILE_OPTIONS 8 #define PragTyp_DATA_STORE_DIRECTORY 9 #define PragTyp_DATABASE_LIST 10 #define PragTyp_DEFAULT_CACHE_SIZE 11 #define PragTyp_ENCODING 12 #define PragTyp_EST_COUNT 13 #define PragTyp_FOREIGN_KEY_CHECK 14 #define PragTyp_FOREIGN_KEY_LIST 15 #define PragTyp_INCREMENTAL_VACUUM 16 #define PragTyp_INDEX_INFO 17 #define PragTyp_INDEX_LIST 18 #define PragTyp_INTEGRITY_CHECK 19 #define PragTyp_JOURNAL_MODE 20 #define PragTyp_JOURNAL_SIZE_LIMIT 21 #define PragTyp_LOCK_PROXY_FILE 22 #define PragTyp_LOCKING_MODE 23 #define PragTyp_PAGE_COUNT 24 #define PragTyp_MMAP_SIZE 25 #define PragTyp_PAGE_SIZE 26 #define PragTyp_SECURE_DELETE 27 #define PragTyp_SHRINK_MEMORY 28 #define PragTyp_SOFT_HEAP_LIMIT 29 #define PragTyp_STATS 30 #define PragTyp_SYNCHRONOUS 31 #define PragTyp_TABLE_INFO 32 #define PragTyp_TEMP_STORE 33 #define PragTyp_TEMP_STORE_DIRECTORY 34 #define PragTyp_THREADS 35 #define PragTyp_WAL_AUTOCHECKPOINT 36 #define PragTyp_WAL_CHECKPOINT 37 #define PragTyp_ACTIVATE_EXTENSIONS 38 #define PragTyp_HEXKEY 39 #define PragTyp_KEY 40 #define PragTyp_REKEY 41 #define PragTyp_LOCK_STATUS 42 #define PragTyp_PARSER_TRACE 43 #define PragFlag_NeedSchema 0x01 #define PragFlag_ReadOnly 0x02 static const struct sPragmaNames { const char *const zName; /* Name of pragma */ u8 ePragTyp; /* PragTyp_XXX value */ u8 mPragFlag; /* Zero or more PragFlag_XXX values */ u32 iArg; /* Extra argument */ |
︙ | ︙ | |||
168 169 170 171 172 173 174 175 176 177 178 179 180 181 | #endif #if !defined(SQLITE_OMIT_UTF16) { /* zName: */ "encoding", /* ePragTyp: */ PragTyp_ENCODING, /* ePragFlag: */ 0, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) { /* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlag: */ PragFlag_NeedSchema, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) | > > > > | 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 | #endif #if !defined(SQLITE_OMIT_UTF16) { /* zName: */ "encoding", /* ePragTyp: */ PragTyp_ENCODING, /* ePragFlag: */ 0, /* iArg: */ 0 }, #endif { /* zName: */ "est_count", /* ePragTyp: */ PragTyp_EST_COUNT, /* ePragFlag: */ PragFlag_NeedSchema, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) { /* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlag: */ PragFlag_NeedSchema, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) |
︙ | ︙ | |||
457 458 459 460 461 462 463 | #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) { /* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlag: */ 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; | | | 462 463 464 465 466 467 468 469 | #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) { /* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlag: */ 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; /* Number of pragmas: 61 on by default, 74 total. */ |
Changes to src/vdbe.c.
︙ | ︙ | |||
4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 | if( rc ) goto abort_due_to_error; pC->nullRow = (u8)res; assert( pOp->p2>0 && pOp->p2<p->nOp ); VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; break; } /* Opcode: Next P1 P2 P3 P4 P5 ** ** Advance cursor P1 so that it points to the next key/data pair in its ** table or index. If there are no more key/value pairs then fall through ** to the following instruction. But if the cursor advance was successful, ** jump immediately to P2. | > > > > > > > > > > > > > > > > > > > > > > > > > | 4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 4913 4914 4915 4916 4917 4918 4919 4920 4921 4922 4923 4924 4925 4926 4927 4928 4929 4930 | if( rc ) goto abort_due_to_error; pC->nullRow = (u8)res; assert( pOp->p2>0 && pOp->p2<p->nOp ); VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; break; } /* ** Opcode: EstRowCnt P1 P2 P3 * * * ** ** Estimate the number of entries in btree for cursor P1 do a proportional ** seek to of P3. Store the result as a floating point value in P2. */ case OP_EstRowCnt: { /* out2 */ VdbeCursor *pC; BtCursor *pCrsr; int rc; sqlite3_uint64 n = 0; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC->eCurType==CURTYPE_BTREE ); pCrsr = pC->uc.pCursor; assert( pCrsr ); rc = sqlite3BtreeMovetoProportional(pCrsr, pOp->p3, &n); if( rc ) goto abort_due_to_error; pOut = out2Prerelease(p, pOp); pOut->flags = MEM_Real; pOut->u.r = n; break; } /* Opcode: Next P1 P2 P3 P4 P5 ** ** Advance cursor P1 so that it points to the next key/data pair in its ** table or index. If there are no more key/value pairs then fall through ** to the following instruction. But if the cursor advance was successful, ** jump immediately to P2. |
︙ | ︙ |
Changes to tool/mkpragmatab.tcl.
︙ | ︙ | |||
247 248 249 250 251 252 253 254 255 256 257 258 259 260 | IF: !defined(SQLITE_OMIT_INTEGRITY_CHECK) NAME: quick_check TYPE: INTEGRITY_CHECK FLAG: NeedSchema IF: !defined(SQLITE_OMIT_INTEGRITY_CHECK) NAME: encoding IF: !defined(SQLITE_OMIT_UTF16) NAME: schema_version TYPE: HEADER_VALUE ARG: BTREE_SCHEMA_VERSION IF: !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) | > > > | 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | IF: !defined(SQLITE_OMIT_INTEGRITY_CHECK) NAME: quick_check TYPE: INTEGRITY_CHECK FLAG: NeedSchema IF: !defined(SQLITE_OMIT_INTEGRITY_CHECK) NAME: est_count FLAG: NeedSchema NAME: encoding IF: !defined(SQLITE_OMIT_UTF16) NAME: schema_version TYPE: HEADER_VALUE ARG: BTREE_SCHEMA_VERSION IF: !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) |
︙ | ︙ |