Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add a bit to the SQLITE_TESTCTRL_OPTIMIZATIONS option for sqlite3_file_control() that will disable the use of SQLITE_STAT3 information in the query planner. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
60c19b86797fb8a37f175545929883eb |
| User & Date: | drh 2013-06-28 17:29:25.884 |
Context
|
2013-06-28
| ||
| 19:41 | Allow read transactions to be freely opened and closed by SQL statements run from within the implementation of user-functions if the user-function is called by a SELECT statement that does not access any database tables (e.g. "SELECT user_function();"). (check-in: f308c48517 user: dan tags: trunk) | |
| 17:29 | Add a bit to the SQLITE_TESTCTRL_OPTIMIZATIONS option for sqlite3_file_control() that will disable the use of SQLITE_STAT3 information in the query planner. (check-in: 60c19b8679 user: drh tags: trunk) | |
| 13:43 | Fix an issue with the OmitNoopJoin optimization and add test cases that are specifically for that optimization. (check-in: 5480d124b7 user: drh tags: trunk) | |
Changes
Changes to src/sqliteInt.h.
| ︙ | ︙ | |||
1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 | #define SQLITE_IdxRealAsInt 0x0010 /* Store REAL as INT in indices */ #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) | > | 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 | #define SQLITE_IdxRealAsInt 0x0010 /* Store REAL as INT in indices */ #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) |
| ︙ | ︙ |
Changes to src/test1.c.
| ︙ | ︙ | |||
5968 5969 5970 5971 5972 5973 5974 5975 5976 5977 5978 5979 5980 5981 |
{ "real-as-int", SQLITE_IdxRealAsInt },
{ "distinct-opt", SQLITE_DistinctOpt },
{ "cover-idx-scan", SQLITE_CoverIdxScan },
{ "order-by-idx-join", SQLITE_OrderByIdxJoin },
{ "transitive", SQLITE_Transitive },
{ "subquery-coroutine", SQLITE_SubqCoroutine },
{ "omit-noop-join", SQLITE_OmitNoopJoin },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
return TCL_ERROR;
}
if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
| > | 5968 5969 5970 5971 5972 5973 5974 5975 5976 5977 5978 5979 5980 5981 5982 |
{ "real-as-int", SQLITE_IdxRealAsInt },
{ "distinct-opt", SQLITE_DistinctOpt },
{ "cover-idx-scan", SQLITE_CoverIdxScan },
{ "order-by-idx-join", SQLITE_OrderByIdxJoin },
{ "transitive", SQLITE_Transitive },
{ "subquery-coroutine", SQLITE_SubqCoroutine },
{ "omit-noop-join", SQLITE_OmitNoopJoin },
{ "stat3", SQLITE_Stat3 },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
return TCL_ERROR;
}
if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
|
| ︙ | ︙ |
Changes to src/where.c.
| ︙ | ︙ | |||
1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 |
** TERM_VNULL tag will suppress the not-null check at the beginning
** of the loop. Without the TERM_VNULL flag, the not-null check at
** the start of the loop will prevent any results from being returned.
*/
if( pExpr->op==TK_NOTNULL
&& pExpr->pLeft->op==TK_COLUMN
&& pExpr->pLeft->iColumn>=0
){
Expr *pNewExpr;
Expr *pLeft = pExpr->pLeft;
int idxNew;
WhereTerm *pNewTerm;
pNewExpr = sqlite3PExpr(pParse, TK_GT,
| > | 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 |
** TERM_VNULL tag will suppress the not-null check at the beginning
** of the loop. Without the TERM_VNULL flag, the not-null check at
** the start of the loop will prevent any results from being returned.
*/
if( pExpr->op==TK_NOTNULL
&& pExpr->pLeft->op==TK_COLUMN
&& pExpr->pLeft->iColumn>=0
&& OptimizationEnabled(db, SQLITE_Stat3)
){
Expr *pNewExpr;
Expr *pLeft = pExpr->pLeft;
int idxNew;
WhereTerm *pNewTerm;
pNewExpr = sqlite3PExpr(pParse, TK_GT,
|
| ︙ | ︙ | |||
2567 2568 2569 2570 2571 2572 2573 |
WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */
WhereCost *pRangeDiv /* OUT: Reduce search space by this divisor */
){
int rc = SQLITE_OK;
#ifdef SQLITE_ENABLE_STAT3
| | | 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 |
WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */
WhereCost *pRangeDiv /* OUT: Reduce search space by this divisor */
){
int rc = SQLITE_OK;
#ifdef SQLITE_ENABLE_STAT3
if( nEq==0 && p->nSample && OptimizationEnabled(pParse->db, SQLITE_Stat3) ){
sqlite3_value *pRangeVal;
tRowcnt iLower = 0;
tRowcnt iUpper = p->aiRowEst[0];
tRowcnt a[2];
u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
if( pLower ){
|
| ︙ | ︙ | |||
4332 4333 4334 4335 4336 4337 4338 |
/* Adjust nOut and rRun for STAT3 range values */
WhereCost rDiv;
whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq,
pBtm, pTop, &rDiv);
pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10;
}
#ifdef SQLITE_ENABLE_STAT3
| | > | 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 4344 4345 4346 4347 4348 |
/* Adjust nOut and rRun for STAT3 range values */
WhereCost rDiv;
whereRangeScanEst(pParse, pProbe, pNew->u.btree.nEq,
pBtm, pTop, &rDiv);
pNew->nOut = saved_nOut>rDiv+10 ? saved_nOut - rDiv : 10;
}
#ifdef SQLITE_ENABLE_STAT3
if( pNew->u.btree.nEq==1 && pProbe->nSample
&& OptimizationEnabled(db, SQLITE_Stat3) ){
tRowcnt nOut = 0;
if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){
testcase( pTerm->eOperator & WO_EQ );
testcase( pTerm->eOperator & WO_ISNULL );
rc = whereEqualScanEst(pParse, pProbe, pTerm->pExpr->pRight, &nOut);
}else if( (pTerm->eOperator & WO_IN)
&& !ExprHasProperty(pTerm->pExpr, EP_xIsSelect) ){
|
| ︙ | ︙ |