/ Check-in [70ec88b2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Avoid unnecessary cursors and seeking when running a DELETE against a WITHOUT ROWID table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | delete-without-rowid-opt
Files: files | file ages | folders
SHA1: 70ec88b29982f1a2437239ad4d3a9bf39bcc2a60
User & Date: drh 2015-09-28 23:45:19
Context
2015-09-28
23:45
Avoid unnecessary cursors and seeking when running a DELETE against a WITHOUT ROWID table. Leaf check-in: 70ec88b2 user: drh tags: delete-without-rowid-opt
17:05
Extra information provided by .wheretrace on input flags to the query planner and on the result of sqlite3WhereOkOnePass(). check-in: c5566bb3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   430    430       }else{
   431    431         iKey = pParse->nMem + 1;
   432    432         iKey = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iTabCur, iKey, 0);
   433    433         if( iKey>pParse->nMem ) pParse->nMem = iKey;
   434    434       }
   435    435     
   436    436       if( eOnePass!=ONEPASS_OFF ){
   437         -      /* For ONEPASS, no need to store the rowid/primary-key. There is only
   438         -      ** one, so just keep it in its register(s) and fall through to the
   439         -      ** delete code.  */
          437  +      /* For ONEPASS, no need to store the rowid/primary-key because rows
          438  +      ** are deleted as they are discovered */
   440    439         nKey = nPk; /* OP_Found will use an unpacked key */
   441    440         aToOpen = sqlite3DbMallocRaw(db, nIdx+2);
   442    441         if( aToOpen==0 ){
   443    442           sqlite3WhereEnd(pWInfo);
   444    443           goto delete_from_cleanup;
   445    444         }
   446    445         memset(aToOpen, 1, nIdx+1);
   447    446         aToOpen[nIdx+1] = 0;
   448    447         if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
   449    448         if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
   450    449         if( addrEphOpen ) sqlite3VdbeChangeToNoop(v, addrEphOpen);
   451    450       }else{
          451  +      /* For non-ONEPASS, remember the rowid/primary-key of the row to
          452  +      ** be deleted */
   452    453         if( pPk ){
   453    454           /* Add the PK key for this row to the temporary table */
   454    455           iKey = ++pParse->nMem;
   455    456           nKey = 0;   /* Zero tells OP_Found to use a composite key */
   456    457           sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
   457    458               sqlite3IndexAffinityStr(pParse->db, pPk), nPk);
   458    459           sqlite3VdbeAddOp2(v, OP_IdxInsert, iEphCur, iKey);
................................................................................
   518    519         sqlite3VdbeChangeP5(v, OE_Abort);
   519    520         sqlite3MayAbort(pParse);
   520    521       }else
   521    522   #endif
   522    523       {
   523    524         int count = (pParse->nested==0);    /* True to count changes */
   524    525         int iIdxNoSeek = -1;
          526  +      if( eOnePass!=ONEPASS_OFF ){
          527  +        sqlite3WhereHenceforthUseTableCursor(pWInfo, sqlite3VdbeCurrentAddr(v));
          528  +      }
   525    529         if( bComplex==0 && aiCurOnePass[1]!=iDataCur ){
   526    530           iIdxNoSeek = aiCurOnePass[1];
   527    531         }
   528    532         sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
   529    533             iKey, nKey, count, OE_Default, eOnePass, iIdxNoSeek);
   530    534       }
   531    535     

Changes to src/insert.c.

  1676   1676     }else{
  1677   1677       sqlite3TableLock(pParse, iDb, pTab->tnum, op==OP_OpenWrite, pTab->zName);
  1678   1678     }
  1679   1679     if( piIdxCur ) *piIdxCur = iBase;
  1680   1680     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1681   1681       int iIdxCur = iBase++;
  1682   1682       assert( pIdx->pSchema==pTab->pSchema );
  1683         -    if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) && piDataCur ){
  1684         -      *piDataCur = iIdxCur;
         1683  +    if( IsPrimaryKeyIndex(pIdx) && !HasRowid(pTab) ){
         1684  +      if( aToOpen && aToOpen[0]==0 ){
         1685  +        iIdxCur = iDataCur;
         1686  +        aToOpen[i+1] = 0;
         1687  +      }
         1688  +      if( piDataCur ) *piDataCur = iIdxCur;
  1685   1689       }
  1686   1690       if( aToOpen==0 || aToOpen[i+1] ){
  1687   1691         sqlite3VdbeAddOp3(v, op, iIdxCur, pIdx->tnum, iDb);
  1688   1692         sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1689   1693         VdbeComment((v, "%s", pIdx->zName));
  1690   1694       }
  1691   1695     }

Changes to src/sqliteInt.h.

  3370   3370   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  3371   3371   Expr *sqlite3LimitWhere(Parse*,SrcList*,Expr*,ExprList*,Expr*,Expr*,char*);
  3372   3372   #endif
  3373   3373   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  3374   3374   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  3375   3375   WhereInfo *sqlite3WhereBegin(Parse*,SrcList*,Expr*,ExprList*,ExprList*,u16,int);
  3376   3376   void sqlite3WhereEnd(WhereInfo*);
         3377  +void sqlite3WhereHenceforthUseTableCursor(WhereInfo*,int addr);
  3377   3378   u64 sqlite3WhereOutputRowCount(WhereInfo*);
  3378   3379   int sqlite3WhereIsDistinct(WhereInfo*);
  3379   3380   int sqlite3WhereIsOrdered(WhereInfo*);
  3380   3381   int sqlite3WhereIsSorted(WhereInfo*);
  3381   3382   int sqlite3WhereContinueLabel(WhereInfo*);
  3382   3383   int sqlite3WhereBreakLabel(WhereInfo*);
  3383   3384   int sqlite3WhereOkOnePass(WhereInfo*, int*);

Changes to src/where.c.

    23     23   static int whereLoopResize(sqlite3*, WhereLoop*, int);
    24     24   
    25     25   /* Test variable that can be set to enable WHERE tracing */
    26     26   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
    27     27   /***/ int sqlite3WhereTrace = 0;
    28     28   #endif
    29     29   
           30  +/*
           31  +** When generating the end-of-WHERE-loop, do not transform references to
           32  +** the main table into references to the index after this address.
           33  +**
           34  +** This interface is invoked by DELETE at a point after when the
           35  +** table cursor is pointing to the correct address but before any
           36  +** of the index cursors have been deleted.
           37  +*/
           38  +void sqlite3WhereHenceforthUseTableCursor(WhereInfo *pWInfo, int addr){
           39  +  pWInfo->addrUseTabCur = addr;
           40  +}
           41  +
    30     42   
    31     43   /*
    32     44   ** Return the estimated number of output rows from a WHERE clause
    33     45   */
    34     46   u64 sqlite3WhereOutputRowCount(WhereInfo *pWInfo){
    35     47     return sqlite3LogEstToInt(pWInfo->nRowOut);
    36     48   }
................................................................................
  4534   4546       */
  4535   4547       if( pLoop->wsFlags & (WHERE_INDEXED|WHERE_IDX_ONLY) ){
  4536   4548         pIdx = pLoop->u.btree.pIndex;
  4537   4549       }else if( pLoop->wsFlags & WHERE_MULTI_OR ){
  4538   4550         pIdx = pLevel->u.pCovidx;
  4539   4551       }
  4540   4552       if( pIdx
  4541         -     && (pWInfo->eOnePass==ONEPASS_OFF || !HasRowid(pIdx->pTable))
  4542   4553        && !db->mallocFailed
  4543   4554       ){
  4544         -      last = sqlite3VdbeCurrentAddr(v);
         4555  +      if( pWInfo->addrUseTabCur ){
         4556  +        last = pWInfo->addrUseTabCur;
         4557  +      }else{
         4558  +        last = sqlite3VdbeCurrentAddr(v);
         4559  +      }
  4545   4560         k = pLevel->addrBody;
  4546   4561         pOp = sqlite3VdbeGetOp(v, k);
  4547   4562         for(; k<last; k++, pOp++){
  4548   4563           if( pOp->p1!=pLevel->iTabCur ) continue;
  4549   4564           if( pOp->opcode==OP_Column ){
  4550   4565             int x = pOp->p2;
  4551   4566             assert( pIdx->pTable==pTab );

Changes to src/whereInt.h.

   415    415     u8 eOnePass;              /* ONEPASS_OFF, or _SINGLE, or _MULTI */
   416    416     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   417    417     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   418    418     u8 nLevel;                /* Number of nested loop */
   419    419     int iTop;                 /* The very beginning of the WHERE loop */
   420    420     int iContinue;            /* Jump here to continue with next record */
   421    421     int iBreak;               /* Jump here to break out of the loop */
          422  +  int addrUseTabCur;        /* Do not use index cursor after this address */
   422    423     int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   423    424     int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
   424    425     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   425    426     WhereClause sWC;          /* Decomposition of the WHERE clause */
   426    427     WhereLevel a[1];          /* Information about each nest loop in WHERE */
   427    428   };
   428    429