Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enable ORDER BY clauses that span joins to be optimized out. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | qp-enhancements |
| Files: | files | file ages | folders |
| SHA1: | c29538f9b1ee4d4869999570604c9618 |
| User & Date: | drh 2012-09-27 14:11:36 |
Context
|
2012-09-27
| ||
| 15:05 | Add more bits to the bit vector that is used to disable optimizations for built-in test. Add specific bit patterns to disable ORDER BY using an index in general and for joins. Use macros to test for bits in the disabled-optimization bit vector, in order to make the code clearer. check-in: d2fcba1e user: drh tags: qp-enhancements | |
| 14:11 | Enable ORDER BY clauses that span joins to be optimized out. check-in: c29538f9 user: drh tags: qp-enhancements | |
| 12:05 | Further tweaks to the query planner logic in preparation for adding ORDER BY opt-out for joins. check-in: 53efc10a user: drh tags: qp-enhancements | |
Changes
Changes to src/where.c.
2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 |
if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
*pnRow = nRowEst;
WHERETRACE(("IN row estimate: est=%g\n", nRowEst));
}
return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT3) */
/*
** pTerm is an == constraint. Check to see if the other side of
** the == is a constant or a value that is guaranteed to be ordered
** by outer loops. Return 1 if pTerm is ordered, and 0 if not.
*/
static int isOrderedTerm(WhereBestIdx *p, WhereTerm *pTerm, int *pbRev){
if( p->i==0 ){
return 1; /* All == are ordered in the outer loop */
}
/* If we cannot prove that the constraint is ordered, assume it is not */
return 0;
}
/*
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > | |
2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 2916 2917 2918 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 |
if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
*pnRow = nRowEst;
WHERETRACE(("IN row estimate: est=%g\n", nRowEst));
}
return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT3) */
/*
** Check to see if column iCol of the table with cursor iTab will appear
** in sorted order according to the current query plan. Return true if
** it will and false if not.
**
** If *pbRev is initially 2 (meaning "unknown") then set *pbRev to the
** sort order of iTab.iCol. If *pbRev is 0 or 1 but does not match
** the sort order of iTab.iCol, then consider the column to be unordered.
*/
static int isOrderedColumn(WhereBestIdx *p, int iTab, int iCol, int *pbRev){
int i, j;
WhereLevel *pLevel = &p->aLevel[p->i-1];
Index *pIdx;
u8 sortOrder;
for(i=p->i-1; i>=0; i--, pLevel--){
if( pLevel->iTabCur!=iTab ) continue;
if( (pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE))!=0 ){
if( iCol!=(-1) ) return 0;
sortOrder = 0;
}else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
pIdx = pLevel->plan.u.pIdx;
for(j=0; j<pIdx->nColumn; j++){
if( iCol==pIdx->aiColumn[j] ) break;
}
if( j>=pIdx->nColumn ) return 0;
sortOrder = pIdx->aSortOrder[j];
}else{
if( iCol!=(-1) ) return 0;
sortOrder = 0;
}
if( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ) sortOrder = 1 - sortOrder;
if( *pbRev==2 ){
*pbRev = sortOrder;
return 1;
}
return (*pbRev==sortOrder);
}
return 0;
}
/*
** pTerm is an == constraint. Check to see if the other side of
** the == is a constant or a value that is guaranteed to be ordered
** by outer loops. Return 1 if pTerm is ordered, and 0 if not.
*/
static int isOrderedTerm(WhereBestIdx *p, WhereTerm *pTerm, int *pbRev){
Expr *pExpr = pTerm->pExpr;
assert( pExpr->op==TK_EQ );
assert( pExpr->pLeft!=0 && pExpr->pLeft->op==TK_COLUMN );
assert( pExpr->pRight!=0 );
if( p->i==0 ){
return 1; /* All == are ordered in the outer loop */
}
if( pTerm->prereqRight==0 ){
return 1; /* RHS of the == is a constant */
}
if( pExpr->pRight->op==TK_COLUMN
&& isOrderedColumn(p, pExpr->pRight->iTable, pExpr->pRight->iColumn, pbRev)
){
return 1;
}
/* If we cannot prove that the constraint is ordered, assume it is not */
return 0;
}
/*
|