Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the OP_IdxInsert opcode to optionally accept unpacked key material. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | unpacked-IdxInsert |
| Files: | files | file ages | folders |
| SHA1: |
89d958abbac45f2ca5954080cd9e74ec |
| User & Date: | drh 2016-11-09 00:10:33 |
Context
|
2016-11-09
| ||
| 01:19 | Fix a typo on the OP_IdxInsert documentation. No code changes. (check-in: e4acd982 user: drh tags: unpacked-IdxInsert) | |
| 00:10 | Enhance the OP_IdxInsert opcode to optionally accept unpacked key material. (check-in: 89d958ab user: drh tags: unpacked-IdxInsert) | |
|
2016-11-08
| ||
| 19:22 | Avoid superfluous cursor seeks in "INSERT OR REPLACE" statements. (check-in: bec5b6d4 user: dan tags: trunk) | |
Changes
Changes to src/btree.c.
8015 8015 && pCur->info.nKey==pX->nKey-1 ){ 8016 8016 loc = -1; 8017 8017 }else if( loc==0 ){ 8018 8018 rc = sqlite3BtreeMovetoUnpacked(pCur, 0, pX->nKey, appendBias, &loc); 8019 8019 if( rc ) return rc; 8020 8020 } 8021 8021 }else if( loc==0 ){ 8022 - rc = btreeMoveto(pCur, pX->pKey, pX->nKey, appendBias, &loc); 8022 + if( pX->nMem ){ 8023 + UnpackedRecord r; 8024 + memset(&r, 0, sizeof(r)); 8025 + r.pKeyInfo = pCur->pKeyInfo; 8026 + r.aMem = pX->aMem; 8027 + r.nField = pX->nMem; 8028 + rc = sqlite3BtreeMovetoUnpacked(pCur, &r, 0, appendBias, &loc); 8029 + }else{ 8030 + rc = btreeMoveto(pCur, pX->pKey, pX->nKey, appendBias, &loc); 8031 + } 8023 8032 if( rc ) return rc; 8024 8033 } 8025 8034 assert( pCur->eState==CURSOR_VALID || (pCur->eState==CURSOR_INVALID && loc) ); 8026 8035 8027 8036 pPage = pCur->apPage[pCur->iPage]; 8028 8037 assert( pPage->intKey || pX->nKey>=0 ); 8029 8038 assert( pPage->leaf || !pPage->intKey );
Changes to src/btree.h.
271 271 ** organized and understandable, and it also helps the resulting code to 272 272 ** run a little faster by using fewer registers for parameter passing. 273 273 */ 274 274 struct BtreePayload { 275 275 const void *pKey; /* Key content for indexes. NULL for tables */ 276 276 sqlite3_int64 nKey; /* Size of pKey for indexes. PRIMARY KEY for tabs */ 277 277 const void *pData; /* Data for tables. NULL for indexes */ 278 + struct Mem *aMem; /* First of nMem value in the unpacked pKey */ 279 + u16 nMem; /* Number of aMem[] value. Might be zero */ 278 280 int nData; /* Size of pData. 0 if none. */ 279 281 int nZero; /* Extra zero data appended after pData,nData */ 280 282 }; 281 283 282 284 int sqlite3BtreeInsert(BtCursor*, const BtreePayload *pPayload, 283 285 int bias, int seekResult); 284 286 int sqlite3BtreeFirst(BtCursor*, int *pRes);
Changes to src/build.c.
2814 2814 pIndex->nKeyCol); VdbeCoverage(v); 2815 2815 sqlite3UniqueConstraint(pParse, OE_Abort, pIndex); 2816 2816 }else{ 2817 2817 addr2 = sqlite3VdbeCurrentAddr(v); 2818 2818 } 2819 2819 sqlite3VdbeAddOp3(v, OP_SorterData, iSorter, regRecord, iIdx); 2820 2820 sqlite3VdbeAddOp3(v, OP_Last, iIdx, 0, -1); 2821 - sqlite3VdbeAddOp3(v, OP_IdxInsert, iIdx, regRecord, 0); 2821 + sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdx, regRecord); 2822 2822 sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); 2823 2823 sqlite3ReleaseTempReg(pParse, regRecord); 2824 2824 sqlite3VdbeAddOp2(v, OP_SorterNext, iSorter, addr2); VdbeCoverage(v); 2825 2825 sqlite3VdbeJumpHere(v, addr1); 2826 2826 2827 2827 sqlite3VdbeAddOp1(v, OP_Close, iTab); 2828 2828 sqlite3VdbeAddOp1(v, OP_Close, iIdx);
Changes to src/delete.c.
445 445 }else{ 446 446 if( pPk ){ 447 447 /* Add the PK key for this row to the temporary table */ 448 448 iKey = ++pParse->nMem; 449 449 nKey = 0; /* Zero tells OP_Found to use a composite key */ 450 450 sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey, 451 451 sqlite3IndexAffinityStr(pParse->db, pPk), nPk); 452 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey); 452 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEphCur, iKey, iPk, nPk); 453 453 }else{ 454 454 /* Add the rowid of the row to be deleted to the RowSet */ 455 455 nKey = 1; /* OP_Seek always uses a single rowid */ 456 456 sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, iKey); 457 457 } 458 458 } 459 459
Changes to src/expr.c.
2534 2534 sqlite3VdbeAddOp2(v, OP_MustBeInt, r3, 2535 2535 sqlite3VdbeCurrentAddr(v)+2); 2536 2536 VdbeCoverage(v); 2537 2537 sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3); 2538 2538 }else{ 2539 2539 sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1); 2540 2540 sqlite3ExprCacheAffinityChange(pParse, r3, 1); 2541 - sqlite3VdbeAddOp2(v, OP_IdxInsert, pExpr->iTable, r2); 2541 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pExpr->iTable, r2, r3, 1); 2542 2542 } 2543 2543 } 2544 2544 } 2545 2545 sqlite3ReleaseTempReg(pParse, r1); 2546 2546 sqlite3ReleaseTempReg(pParse, r2); 2547 2547 } 2548 2548 if( pKeyInfo ){
Changes to src/insert.c.
2176 2176 idxInsFlags = OPFLAG_USESEEKRESULT; 2177 2177 sqlite3VdbeAddOp3(v, OP_Last, iDest, 0, -1); 2178 2178 } 2179 2179 } 2180 2180 if( !HasRowid(pSrc) && pDestIdx->idxType==2 ){ 2181 2181 idxInsFlags |= OPFLAG_NCHANGE; 2182 2182 } 2183 - sqlite3VdbeAddOp3(v, OP_IdxInsert, iDest, regData, 1); 2184 - sqlite3VdbeChangeP5(v, idxInsFlags); 2183 + sqlite3VdbeAddOp2(v, OP_IdxInsert, iDest, regData); 2184 + sqlite3VdbeChangeP5(v, idxInsFlags|OPFLAG_APPEND); 2185 2185 sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1+1); VdbeCoverage(v); 2186 2186 sqlite3VdbeJumpHere(v, addr1); 2187 2187 sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0); 2188 2188 sqlite3VdbeAddOp2(v, OP_Close, iDest, 0); 2189 2189 } 2190 2190 if( emptySrcTest ) sqlite3VdbeJumpHere(v, emptySrcTest); 2191 2191 sqlite3ReleaseTempReg(pParse, regRowid);
Changes to src/select.c.
651 651 Vdbe *v; 652 652 int r1; 653 653 654 654 v = pParse->pVdbe; 655 655 r1 = sqlite3GetTempReg(pParse); 656 656 sqlite3VdbeAddOp4Int(v, OP_Found, iTab, addrRepeat, iMem, N); VdbeCoverage(v); 657 657 sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, N, r1); 658 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iTab, r1); 658 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r1, iMem, N); 659 659 sqlite3ReleaseTempReg(pParse, r1); 660 660 } 661 661 662 662 /* 663 663 ** This routine generates the code for the inside of the inner loop 664 664 ** of a SELECT. 665 665 ** ................................................................................ 804 804 ** table iParm. 805 805 */ 806 806 #ifndef SQLITE_OMIT_COMPOUND_SELECT 807 807 case SRT_Union: { 808 808 int r1; 809 809 r1 = sqlite3GetTempReg(pParse); 810 810 sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1); 811 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); 811 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regResult, nResultCol); 812 812 sqlite3ReleaseTempReg(pParse, r1); 813 813 break; 814 814 } 815 815 816 816 /* Construct a record from the query result, but instead of 817 817 ** saving that record, use it as a key to delete elements from 818 818 ** the temporary table iParm. ................................................................................ 841 841 ** on an ephemeral index. If the current row is already present 842 842 ** in the index, do not write it to the output. If not, add the 843 843 ** current row to the index and proceed with writing it to the 844 844 ** output table as well. */ 845 845 int addr = sqlite3VdbeCurrentAddr(v) + 4; 846 846 sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); 847 847 VdbeCoverage(v); 848 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1); 848 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm+1, r1,regResult,nResultCol); 849 849 assert( pSort==0 ); 850 850 } 851 851 #endif 852 852 if( pSort ){ 853 853 pushOntoSorter(pParse, pSort, p, r1+nPrefixReg,regResult,1,nPrefixReg); 854 854 }else{ 855 855 int r2 = sqlite3GetTempReg(pParse); ................................................................................ 877 877 pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg); 878 878 }else{ 879 879 int r1 = sqlite3GetTempReg(pParse); 880 880 assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol ); 881 881 sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, 882 882 r1, pDest->zAffSdst, nResultCol); 883 883 sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol); 884 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); 884 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, regResult, nResultCol); 885 885 sqlite3ReleaseTempReg(pParse, r1); 886 886 } 887 887 break; 888 888 } 889 889 890 890 /* If any row exist in the result set, record that fact and abort. 891 891 */ ................................................................................ 963 963 for(i=0; i<nKey; i++){ 964 964 sqlite3VdbeAddOp2(v, OP_SCopy, 965 965 regResult + pSO->a[i].u.x.iOrderByCol - 1, 966 966 r2+i); 967 967 } 968 968 sqlite3VdbeAddOp2(v, OP_Sequence, iParm, r2+nKey); 969 969 sqlite3VdbeAddOp3(v, OP_MakeRecord, r2, nKey+2, r1); 970 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1); 970 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, r1, r2, nKey+2); 971 971 if( addrTest ) sqlite3VdbeJumpHere(v, addrTest); 972 972 sqlite3ReleaseTempReg(pParse, r1); 973 973 sqlite3ReleaseTempRange(pParse, r2, nKey+2); 974 974 break; 975 975 } 976 976 #endif /* SQLITE_OMIT_CTE */ 977 977 ................................................................................ 1260 1260 } 1261 1261 #ifndef SQLITE_OMIT_SUBQUERY 1262 1262 case SRT_Set: { 1263 1263 assert( nColumn==sqlite3Strlen30(pDest->zAffSdst) ); 1264 1264 sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, nColumn, regRowid, 1265 1265 pDest->zAffSdst, nColumn); 1266 1266 sqlite3ExprCacheAffinityChange(pParse, regRow, nColumn); 1267 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid); 1267 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iParm, regRowid, regRow, nColumn); 1268 1268 break; 1269 1269 } 1270 1270 case SRT_Mem: { 1271 1271 /* The LIMIT clause will terminate the loop for us */ 1272 1272 break; 1273 1273 } 1274 1274 #endif ................................................................................ 2636 2636 case SRT_Set: { 2637 2637 int r1; 2638 2638 testcase( pIn->nSdst>1 ); 2639 2639 r1 = sqlite3GetTempReg(pParse); 2640 2640 sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iSdst, pIn->nSdst, 2641 2641 r1, pDest->zAffSdst, pIn->nSdst); 2642 2642 sqlite3ExprCacheAffinityChange(pParse, pIn->iSdst, pIn->nSdst); 2643 - sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iSDParm, r1); 2643 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pDest->iSDParm, r1, 2644 + pIn->iSdst, pIn->nSdst); 2644 2645 sqlite3ReleaseTempReg(pParse, r1); 2645 2646 break; 2646 2647 } 2647 2648 2648 2649 /* If this is a scalar select that is part of an expression, then 2649 2650 ** store the results in the appropriate memory cell and break out 2650 2651 ** of the scan loop.
Changes to src/update.c.
394 394 if( okOnePass ){ 395 395 sqlite3VdbeChangeToNoop(v, addrOpen); 396 396 nKey = nPk; 397 397 regKey = iPk; 398 398 }else{ 399 399 sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, regKey, 400 400 sqlite3IndexAffinityStr(db, pPk), nPk); 401 - sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, regKey); 401 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iEph, regKey, iPk, nPk); 402 402 } 403 403 sqlite3WhereEnd(pWInfo); 404 404 } 405 405 406 406 /* Initialize the count of updated rows 407 407 */ 408 408 if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab ){
Changes to src/vdbe.c.
5013 5013 goto jump_to_p2_and_check_for_interrupt; 5014 5014 }else{ 5015 5015 pC->nullRow = 1; 5016 5016 } 5017 5017 goto check_for_interrupt; 5018 5018 } 5019 5019 5020 -/* Opcode: IdxInsert P1 P2 P3 * P5 5020 +/* Opcode: IdxInsert P1 P2 P3 P4 P5 5021 5021 ** Synopsis: key=r[P2] 5022 5022 ** 5023 5023 ** Register P2 holds an SQL index key made using the 5024 5024 ** MakeRecord instructions. This opcode writes that key 5025 5025 ** into the index P1. Data for the entry is nil. 5026 5026 ** 5027 -** P3 is a flag that provides a hint to the b-tree layer that this 5028 -** insert is likely to be an append. 5027 +** If P4 is not zero, the it is the number of values in the unpacked 5028 +** key of reg(P2). In that case, P3 is the index of the first register 5029 +** for the unpacked key. The availability of the unpacked key can sometimes 5030 +** be an optimization. 5031 +** 5032 +** If P5 has the OPFLAG_APPEND bit set, that is a hint to the b-tree layer 5033 +** that this insert is likely to be an append. 5029 5034 ** 5030 5035 ** If P5 has the OPFLAG_NCHANGE bit set, then the change counter is 5031 5036 ** incremented by this instruction. If the OPFLAG_NCHANGE bit is clear, 5032 5037 ** then the change counter is unchanged. 5033 5038 ** 5034 5039 ** If P5 has the OPFLAG_USESEEKRESULT bit set, then the cursor must have 5035 5040 ** just done a seek to the spot where the new entry is to be inserted. ................................................................................ 5062 5067 rc = ExpandBlob(pIn2); 5063 5068 if( rc ) goto abort_due_to_error; 5064 5069 if( pOp->opcode==OP_SorterInsert ){ 5065 5070 rc = sqlite3VdbeSorterWrite(pC, pIn2); 5066 5071 }else{ 5067 5072 x.nKey = pIn2->n; 5068 5073 x.pKey = pIn2->z; 5069 - rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, pOp->p3, 5074 + x.aMem = aMem + pOp->p3; 5075 + x.nMem = (u16)pOp->p4.i; 5076 + rc = sqlite3BtreeInsert(pC->uc.pCursor, &x, 5077 + (pOp->p5 & OPFLAG_APPEND)!=0, 5070 5078 ((pOp->p5 & OPFLAG_USESEEKRESULT) ? pC->seekResult : 0) 5071 5079 ); 5072 5080 assert( pC->deferredMoveto==0 ); 5073 5081 pC->cacheStatus = CACHE_STALE; 5074 5082 } 5075 5083 if( rc) goto abort_due_to_error; 5076 5084 break;
Changes to src/wherecode.c.
1840 1840 ** be tested for. */ 1841 1841 if( iSet ){ 1842 1842 jmp1 = sqlite3VdbeAddOp4Int(v, OP_Found, regRowset, 0, r, nPk); 1843 1843 VdbeCoverage(v); 1844 1844 } 1845 1845 if( iSet>=0 ){ 1846 1846 sqlite3VdbeAddOp3(v, OP_MakeRecord, r, nPk, regRowid); 1847 - sqlite3VdbeAddOp3(v, OP_IdxInsert, regRowset, regRowid, 0); 1847 + sqlite3VdbeAddOp4Int(v, OP_IdxInsert, regRowset, regRowid, 1848 + r, nPk); 1848 1849 if( iSet ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); 1849 1850 } 1850 1851 1851 1852 /* Release the array of temp registers */ 1852 1853 sqlite3ReleaseTempRange(pParse, r, nPk); 1853 1854 } 1854 1855 }