Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the OP_ReopenIdx opcode that works like OP_OpenRead except that it becomes a no-op if the cursor is already open on the same index. Update the OR-optimization logic to make use of OP_ReopenIdx in order to avoid unnecessary cursor open requests sent to the B-Tree layer. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
77f412caf0192d3e7fecb377d6d72123 |
User & Date: | drh 2014-07-22 20:02:19.743 |
Context
2014-07-22
| ||
22:46 | When running ANALYZE, it is not necessary to check the right-most key column for changes since that column will always change if none of the previous columns have. (check-in: 48f40861db user: drh tags: trunk) | |
20:02 | Add the OP_ReopenIdx opcode that works like OP_OpenRead except that it becomes a no-op if the cursor is already open on the same index. Update the OR-optimization logic to make use of OP_ReopenIdx in order to avoid unnecessary cursor open requests sent to the B-Tree layer. (check-in: 77f412caf0 user: drh tags: trunk) | |
19:14 | The optimization of check-in [b67a6e33f2] does not work (it generates incorrect VDBE code) if an OR term is AND-ed with a constant expression. So back that optimization out and add a test case to make sure it does not get added back in. (check-in: eed754fe93 user: drh tags: trunk) | |
Changes
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 | #define WHERE_FORCE_TABLE 0x0020 /* Do not use an index-only search */ #define WHERE_ONETABLE_ONLY 0x0040 /* Only code the 1st table in pTabList */ #define WHERE_AND_ONLY 0x0080 /* Don't use indices for OR terms */ #define WHERE_GROUPBY 0x0100 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0200 /* pOrderby is really a DISTINCT clause */ #define WHERE_WANT_DISTINCT 0x0400 /* All output needs to be distinct */ #define WHERE_SORTBYGROUP 0x0800 /* Support sqlite3WhereIsSorted() */ /* Allowed return values from sqlite3WhereIsDistinct() */ #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ #define WHERE_DISTINCT_ORDERED 2 /* All duplicates are adjacent */ #define WHERE_DISTINCT_UNORDERED 3 /* Duplicates are scattered */ | > | 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 | #define WHERE_FORCE_TABLE 0x0020 /* Do not use an index-only search */ #define WHERE_ONETABLE_ONLY 0x0040 /* Only code the 1st table in pTabList */ #define WHERE_AND_ONLY 0x0080 /* Don't use indices for OR terms */ #define WHERE_GROUPBY 0x0100 /* pOrderBy is really a GROUP BY */ #define WHERE_DISTINCTBY 0x0200 /* pOrderby is really a DISTINCT clause */ #define WHERE_WANT_DISTINCT 0x0400 /* All output needs to be distinct */ #define WHERE_SORTBYGROUP 0x0800 /* Support sqlite3WhereIsSorted() */ #define WHERE_REOPEN_IDX 0x1000 /* Try to use OP_ReopenIdx */ /* Allowed return values from sqlite3WhereIsDistinct() */ #define WHERE_DISTINCT_NOOP 0 /* DISTINCT keyword not used */ #define WHERE_DISTINCT_UNIQUE 1 /* No duplicates */ #define WHERE_DISTINCT_ORDERED 2 /* All duplicates are adjacent */ #define WHERE_DISTINCT_UNORDERED 3 /* Duplicates are scattered */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
3201 3202 3203 3204 3205 3206 3207 | ** ** The P4 value may be either an integer (P4_INT32) or a pointer to ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo ** structure, then said structure defines the content and collating ** sequence of the index being opened. Otherwise, if P4 is an integer ** value, it is set to the number of columns in the table. ** | | > > > > > > > > > > > > > > | 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 3218 3219 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 | ** ** The P4 value may be either an integer (P4_INT32) or a pointer to ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo ** structure, then said structure defines the content and collating ** sequence of the index being opened. Otherwise, if P4 is an integer ** value, it is set to the number of columns in the table. ** ** See also: OpenWrite, ReopenIdx */ /* Opcode: ReopenIdx P1 P2 P3 P4 P5 ** Synopsis: root=P2 iDb=P3 ** ** The ReopenIdx opcode works exactly like ReadOpen except that it first ** checks to see if the cursor on P1 is already open with a root page ** number of P2 and if it is this opcode becomes a no-op. In other words, ** if the cursor is already open, do not reopen it. ** ** The ReopenIdx opcode may only be used with P5==0 and with P4 being ** a P4_KEYINFO object. Furthermore, the P3 value must be the same as ** every other ReopenIdx or OpenRead for the same cursor number. ** ** See the OpenRead opcode documentation for additional information. */ /* Opcode: OpenWrite P1 P2 P3 P4 P5 ** Synopsis: root=P2 iDb=P3 ** ** Open a read/write cursor named P1 on the table or index whose root ** page is P2. Or if P5!=0 use the content of register P2 to find the ** root page. |
︙ | ︙ | |||
3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 | ** ** This instruction works just like OpenRead except that it opens the cursor ** in read/write mode. For a given table, there can be one or more read-only ** cursors or a single read/write cursor but not both. ** ** See also OpenRead. */ case OP_OpenRead: case OP_OpenWrite: { int nField; KeyInfo *pKeyInfo; int p2; int iDb; int wrFlag; Btree *pX; VdbeCursor *pCur; Db *pDb; assert( (pOp->p5&(OPFLAG_P2ISREG|OPFLAG_BULKCSR))==pOp->p5 ); assert( pOp->opcode==OP_OpenWrite || pOp->p5==0 ); assert( p->bIsReader ); | > > > > > > > > > > > > > | > | 3237 3238 3239 3240 3241 3242 3243 3244 3245 3246 3247 3248 3249 3250 3251 3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 | ** ** This instruction works just like OpenRead except that it opens the cursor ** in read/write mode. For a given table, there can be one or more read-only ** cursors or a single read/write cursor but not both. ** ** See also OpenRead. */ case OP_ReopenIdx: { VdbeCursor *pCur; assert( pOp->p5==0 ); assert( pOp->p4type==P4_KEYINFO ); pCur = p->apCsr[pOp->p1]; if( pCur && pCur->pgnoRoot==pOp->p2 ){ assert( pCur->iDb==pOp->p3 ); /* Guaranteed by the code generator */ break; } /* If the cursor is not currently open or is open on a different ** index, then fall through into OP_OpenRead to force a reopen */ } case OP_OpenRead: case OP_OpenWrite: { int nField; KeyInfo *pKeyInfo; int p2; int iDb; int wrFlag; Btree *pX; VdbeCursor *pCur; Db *pDb; assert( (pOp->p5&(OPFLAG_P2ISREG|OPFLAG_BULKCSR))==pOp->p5 ); assert( pOp->opcode==OP_OpenWrite || pOp->p5==0 ); assert( p->bIsReader ); assert( pOp->opcode==OP_OpenRead || pOp->opcode==OP_ReopenIdx || p->readOnly==0 ); if( p->expired ){ rc = SQLITE_ABORT; break; } nField = 0; |
︙ | ︙ | |||
3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 | assert( pOp->p1>=0 ); assert( nField>=0 ); testcase( nField==0 ); /* Table with INTEGER PRIMARY KEY and nothing else */ pCur = allocateCursor(p, pOp->p1, nField, iDb, 1); if( pCur==0 ) goto no_mem; pCur->nullRow = 1; pCur->isOrdered = 1; rc = sqlite3BtreeCursor(pX, p2, wrFlag, pKeyInfo, pCur->pCursor); pCur->pKeyInfo = pKeyInfo; assert( OPFLAG_BULKCSR==BTREE_BULKLOAD ); sqlite3BtreeCursorHints(pCur->pCursor, (pOp->p5 & OPFLAG_BULKCSR)); /* Since it performs no memory allocation or IO, the only value that ** sqlite3BtreeCursor() may return is SQLITE_OK. */ | > | 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 3333 3334 3335 3336 | assert( pOp->p1>=0 ); assert( nField>=0 ); testcase( nField==0 ); /* Table with INTEGER PRIMARY KEY and nothing else */ pCur = allocateCursor(p, pOp->p1, nField, iDb, 1); if( pCur==0 ) goto no_mem; pCur->nullRow = 1; pCur->isOrdered = 1; pCur->pgnoRoot = p2; rc = sqlite3BtreeCursor(pX, p2, wrFlag, pKeyInfo, pCur->pCursor); pCur->pKeyInfo = pKeyInfo; assert( OPFLAG_BULKCSR==BTREE_BULKLOAD ); sqlite3BtreeCursorHints(pCur->pCursor, (pOp->p5 & OPFLAG_BULKCSR)); /* Since it performs no memory allocation or IO, the only value that ** sqlite3BtreeCursor() may return is SQLITE_OK. */ |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
72 73 74 75 76 77 78 79 80 81 82 83 84 85 | u8 nullRow; /* True if pointing to a row with no data */ u8 rowidIsValid; /* True if lastRowid is valid */ u8 deferredMoveto; /* A call to sqlite3BtreeMoveto() is needed */ Bool isEphemeral:1; /* True for an ephemeral table */ Bool useRandomRowid:1;/* Generate new record numbers semi-randomly */ Bool isTable:1; /* True if a table requiring integer keys */ Bool isOrdered:1; /* True if the underlying table is BTREE_UNORDERED */ sqlite3_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ i64 seqCount; /* Sequence counter */ i64 movetoTarget; /* Argument to the deferred sqlite3BtreeMoveto() */ i64 lastRowid; /* Rowid being deleted by OP_Delete */ VdbeSorter *pSorter; /* Sorter object for OP_SorterOpen cursors */ /* Cached information about the header for the data record that the | > | 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | u8 nullRow; /* True if pointing to a row with no data */ u8 rowidIsValid; /* True if lastRowid is valid */ u8 deferredMoveto; /* A call to sqlite3BtreeMoveto() is needed */ Bool isEphemeral:1; /* True for an ephemeral table */ Bool useRandomRowid:1;/* Generate new record numbers semi-randomly */ Bool isTable:1; /* True if a table requiring integer keys */ Bool isOrdered:1; /* True if the underlying table is BTREE_UNORDERED */ Pgno pgnoRoot; /* Root page of the open btree cursor */ sqlite3_vtab_cursor *pVtabCursor; /* The cursor for a virtual table */ i64 seqCount; /* Sequence counter */ i64 movetoTarget; /* Argument to the deferred sqlite3BtreeMoveto() */ i64 lastRowid; /* Rowid being deleted by OP_Delete */ VdbeSorter *pSorter; /* Sorter object for OP_SorterOpen cursors */ /* Cached information about the header for the data record that the |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 | int regReturn = ++pParse->nMem; /* Register used with OP_Gosub */ int regRowset = 0; /* Register for RowSet object */ int regRowid = 0; /* Register holding rowid */ int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ int iRetInit; /* Address of regReturn init */ int untestedTerms = 0; /* Some terms not completely tested */ int ii; /* Loop counter */ Expr *pAndExpr = 0; /* An ".. AND (...)" expression */ Table *pTab = pTabItem->pTab; pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->eOperator & WO_OR ); assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); | > | 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 | int regReturn = ++pParse->nMem; /* Register used with OP_Gosub */ int regRowset = 0; /* Register for RowSet object */ int regRowid = 0; /* Register holding rowid */ int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ int iRetInit; /* Address of regReturn init */ int untestedTerms = 0; /* Some terms not completely tested */ int ii; /* Loop counter */ u16 wctrlFlags; /* Flags for sub-WHERE clause */ Expr *pAndExpr = 0; /* An ".. AND (...)" expression */ Table *pTab = pTabItem->pTab; pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->eOperator & WO_OR ); assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); |
︙ | ︙ | |||
3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 | } } /* Run a separate WHERE clause for each term of the OR clause. After ** eliminating duplicates from other WHERE clauses, the action for each ** sub-WHERE clause is to to invoke the main loop body as a subroutine. */ for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */ int j1 = 0; /* Address of jump operation */ if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ pAndExpr->pLeft = pOrExpr; pOrExpr = pAndExpr; } /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, | > > < | | 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 | } } /* Run a separate WHERE clause for each term of the OR clause. After ** eliminating duplicates from other WHERE clauses, the action for each ** sub-WHERE clause is to to invoke the main loop body as a subroutine. */ wctrlFlags = WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY | WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY; for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */ int j1 = 0; /* Address of jump operation */ if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){ pAndExpr->pLeft = pOrExpr; pOrExpr = pAndExpr; } /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, wctrlFlags, iCovCur); assert( pSubWInfo || pParse->nErr || db->mallocFailed ); if( pSubWInfo ){ WhereLoop *pSubLoop; explainOneScan( pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 ); /* This is the sub-WHERE clause body. First skip over |
︙ | ︙ | |||
3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 | assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 ); if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0 && (ii==0 || pSubLoop->u.btree.pIndex==pCov) && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex)) ){ assert( pSubWInfo->a[0].iIdxCur==iCovCur ); pCov = pSubLoop->u.btree.pIndex; }else{ pCov = 0; } /* Finish the loop through table entries that match term pOrTerm. */ sqlite3WhereEnd(pSubWInfo); } | > | 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 | assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 ); if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0 && (ii==0 || pSubLoop->u.btree.pIndex==pCov) && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex)) ){ assert( pSubWInfo->a[0].iIdxCur==iCovCur ); pCov = pSubLoop->u.btree.pIndex; wctrlFlags |= WHERE_REOPEN_IDX; }else{ pCov = 0; } /* Finish the loop through table entries that match term pOrTerm. */ sqlite3WhereEnd(pSubWInfo); } |
︙ | ︙ | |||
6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 6226 6227 6228 | iIndexCur++; pJ = pJ->pNext; } op = OP_OpenWrite; pWInfo->aiCurOnePass[1] = iIndexCur; }else if( iIdxCur && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ){ iIndexCur = iIdxCur; }else{ iIndexCur = pParse->nTab++; } pLevel->iIdxCur = iIndexCur; assert( pIx->pSchema==pTab->pSchema ); assert( iIndexCur>=0 ); if( op ){ | > | 6218 6219 6220 6221 6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 | iIndexCur++; pJ = pJ->pNext; } op = OP_OpenWrite; pWInfo->aiCurOnePass[1] = iIndexCur; }else if( iIdxCur && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ){ iIndexCur = iIdxCur; if( wctrlFlags & WHERE_REOPEN_IDX ) op = OP_ReopenIdx; }else{ iIndexCur = pParse->nTab++; } pLevel->iIdxCur = iIndexCur; assert( pIx->pSchema==pTab->pSchema ); assert( iIndexCur>=0 ); if( op ){ |
︙ | ︙ |