/ Check-in [89d958ab]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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: 89d958abbac45f2ca5954080cd9e74ec9a07ebb2
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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             }