/ Check-in [3c367004]
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:Provide the BTREE_SEEK_EQ hint to the b-tree layer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3c367004dab0a1a24d955482c97b0f2d84129ab6
User & Date: drh 2015-03-20 16:54:29
Context
2015-03-21
02:22
Improvements to the MSVC build. Fix harmless compiler warnings. Enable use of 'stdcall'. check-in: 737630b8 user: mistachkin tags: trunk
2015-03-20
16:54
Provide the BTREE_SEEK_EQ hint to the b-tree layer. check-in: 3c367004 user: drh tags: trunk
16:34
Fix to get SQLITE_SMALL_STACK working correctly again after the previous change. Closed-Leaf check-in: 78df0ce1 user: drh tags: seek-eq
08:43
Fix a problem causing collation sequence names to be dequoted multiple times under some circumstances. check-in: eddc05e7 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  7476   7476             ** different page). Once this subsequent call to balance_nonroot() 
  7477   7477             ** has completed, it is safe to release the pSpace buffer used by
  7478   7478             ** the previous call, as the overflow cell data will have been 
  7479   7479             ** copied either into the body of a database page or into the new
  7480   7480             ** pSpace buffer passed to the latter call to balance_nonroot().
  7481   7481             */
  7482   7482             u8 *pSpace = sqlite3PageMalloc(pCur->pBt->pageSize);
  7483         -          rc = balance_nonroot(pParent, iIdx, pSpace, iPage==1, pCur->hints);
         7483  +          rc = balance_nonroot(pParent, iIdx, pSpace, iPage==1,
         7484  +                               pCur->hints&BTREE_BULKLOAD);
  7484   7485             if( pFree ){
  7485   7486               /* If pFree is not NULL, it points to the pSpace buffer used 
  7486   7487               ** by a previous call to balance_nonroot(). Its contents are
  7487   7488               ** now stored either on real database pages or within the 
  7488   7489               ** new pSpace buffer, so it may be safely freed here. */
  7489   7490               sqlite3PageFree(pFree);
  7490   7491             }
................................................................................
  9139   9140     }
  9140   9141   
  9141   9142     pBt->btsFlags &= ~BTS_NO_WAL;
  9142   9143     return rc;
  9143   9144   }
  9144   9145   
  9145   9146   /*
  9146         -** set the mask of hint flags for cursor pCsr. Currently the only valid
  9147         -** values are 0 and BTREE_BULKLOAD.
         9147  +** set the mask of hint flags for cursor pCsr.
  9148   9148   */
  9149   9149   void sqlite3BtreeCursorHints(BtCursor *pCsr, unsigned int mask){
  9150         -  assert( mask==BTREE_BULKLOAD || mask==0 );
         9150  +  assert( mask==BTREE_BULKLOAD || mask==BTREE_SEEK_EQ || mask==0 );
  9151   9151     pCsr->hints = mask;
  9152   9152   }
         9153  +
         9154  +#ifdef SQLITE_DEBUG
         9155  +/*
         9156  +** Return true if the cursor has a hint specified.  This routine is
         9157  +** only used from within assert() statements
         9158  +*/
         9159  +int sqlite3BtreeCursorHasHint(BtCursor *pCsr, unsigned int mask){
         9160  +  return (pCsr->hints & mask)!=0;
         9161  +}
         9162  +#endif
  9153   9163   
  9154   9164   /*
  9155   9165   ** Return true if the given Btree is read-only.
  9156   9166   */
  9157   9167   int sqlite3BtreeIsReadonly(Btree *p){
  9158   9168     return (p->pBt->btsFlags & BTS_READ_ONLY)!=0;
  9159   9169   }
  9160   9170   
  9161   9171   /*
  9162   9172   ** Return the size of the header added to each page by this module.
  9163   9173   */
  9164   9174   int sqlite3HeaderSizeBtree(void){ return ROUND8(sizeof(MemPage)); }

Changes to src/btree.h.

   148    148   #define BTREE_INCR_VACUUM         7
   149    149   #define BTREE_APPLICATION_ID      8
   150    150   #define BTREE_DATA_VERSION        15  /* A virtual meta-value */
   151    151   
   152    152   /*
   153    153   ** Values that may be OR'd together to form the second argument of an
   154    154   ** sqlite3BtreeCursorHints() call.
          155  +**
          156  +** The BTREE_BULKLOAD flag is set on index cursors when the index is going
          157  +** to be filled with content that is already in sorted order.
          158  +**
          159  +** The BTREE_SEEK_EQ flag is set on cursors that will get OP_SeekGE or
          160  +** OP_SeekLE opcodes for a range search, but where the range of entries
          161  +** selected will all have the same key.  In other words, the cursor will
          162  +** be used only for equality key searches.
          163  +**
   155    164   */
   156         -#define BTREE_BULKLOAD 0x00000001
          165  +#define BTREE_BULKLOAD 0x00000001  /* Used to full index in sorted order */
          166  +#define BTREE_SEEK_EQ  0x00000002  /* EQ seeks only - no range seeks */
   157    167   
   158    168   int sqlite3BtreeCursor(
   159    169     Btree*,                              /* BTree containing table to open */
   160    170     int iTable,                          /* Index of root page */
   161    171     int wrFlag,                          /* 1 for writing.  0 for read-only */
   162    172     struct KeyInfo*,                     /* First argument to compare function */
   163    173     BtCursor *pCursor                    /* Space to write cursor structure */
................................................................................
   195    205   struct Pager *sqlite3BtreePager(Btree*);
   196    206   
   197    207   int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
   198    208   void sqlite3BtreeIncrblobCursor(BtCursor *);
   199    209   void sqlite3BtreeClearCursor(BtCursor *);
   200    210   int sqlite3BtreeSetVersion(Btree *pBt, int iVersion);
   201    211   void sqlite3BtreeCursorHints(BtCursor *, unsigned int mask);
          212  +#ifdef SQLITE_DEBUG
          213  +int sqlite3BtreeCursorHasHint(BtCursor*, unsigned int mask);
          214  +#endif
   202    215   int sqlite3BtreeIsReadonly(Btree *pBt);
   203    216   int sqlite3HeaderSizeBtree(void);
   204    217   
   205    218   #ifndef NDEBUG
   206    219   int sqlite3BtreeCursorIsValid(BtCursor*);
   207    220   #endif
   208    221   

Changes to src/sqliteInt.h.

  2688   2688   #define OPFLAG_LASTROWID     0x02    /* Set to update db->lastRowid */
  2689   2689   #define OPFLAG_ISUPDATE      0x04    /* This OP_Insert is an sql UPDATE */
  2690   2690   #define OPFLAG_APPEND        0x08    /* This is likely to be an append */
  2691   2691   #define OPFLAG_USESEEKRESULT 0x10    /* Try to avoid a seek in BtreeInsert() */
  2692   2692   #define OPFLAG_LENGTHARG     0x40    /* OP_Column only used for length() */
  2693   2693   #define OPFLAG_TYPEOFARG     0x80    /* OP_Column only used for typeof() */
  2694   2694   #define OPFLAG_BULKCSR       0x01    /* OP_Open** used to open bulk cursor */
  2695         -#define OPFLAG_P2ISREG       0x02    /* P2 to OP_Open** is a register number */
         2695  +#define OPFLAG_SEEKEQ        0x02    /* OP_Open** cursor uses EQ seek only */
         2696  +#define OPFLAG_P2ISREG       0x04    /* P2 to OP_Open** is a register number */
  2696   2697   #define OPFLAG_PERMUTE       0x01    /* OP_Compare: use the permutation */
  2697   2698   
  2698   2699   /*
  2699   2700    * Each trigger present in the database schema is stored as an instance of
  2700   2701    * struct Trigger. 
  2701   2702    *
  2702   2703    * Pointers to instances of struct Trigger are stored in two ways.

Changes to src/vdbe.c.

  3231   3231   ** This instruction works just like OpenRead except that it opens the cursor
  3232   3232   ** in read/write mode.  For a given table, there can be one or more read-only
  3233   3233   ** cursors or a single read/write cursor but not both.
  3234   3234   **
  3235   3235   ** See also OpenRead.
  3236   3236   */
  3237   3237   case OP_ReopenIdx: {
  3238         -  VdbeCursor *pCur;
  3239         -
  3240         -  assert( pOp->p5==0 );
  3241         -  assert( pOp->p4type==P4_KEYINFO );
  3242         -  pCur = p->apCsr[pOp->p1];
  3243         -  if( pCur && pCur->pgnoRoot==(u32)pOp->p2 ){
  3244         -    assert( pCur->iDb==pOp->p3 );      /* Guaranteed by the code generator */
  3245         -    break;
  3246         -  }
  3247         -  /* If the cursor is not currently open or is open on a different
  3248         -  ** index, then fall through into OP_OpenRead to force a reopen */
  3249         -}
  3250         -case OP_OpenRead:
  3251         -case OP_OpenWrite: {
  3252   3238     int nField;
  3253   3239     KeyInfo *pKeyInfo;
  3254   3240     int p2;
  3255   3241     int iDb;
  3256   3242     int wrFlag;
  3257   3243     Btree *pX;
  3258   3244     VdbeCursor *pCur;
  3259   3245     Db *pDb;
  3260   3246   
  3261         -  assert( (pOp->p5&(OPFLAG_P2ISREG|OPFLAG_BULKCSR))==pOp->p5 );
  3262         -  assert( pOp->opcode==OP_OpenWrite || pOp->p5==0 );
         3247  +  assert( pOp->p5==0 || pOp->p5==OPFLAG_SEEKEQ );
         3248  +  assert( pOp->p4type==P4_KEYINFO );
         3249  +  pCur = p->apCsr[pOp->p1];
         3250  +  if( pCur && pCur->pgnoRoot==(u32)pOp->p2 ){
         3251  +    assert( pCur->iDb==pOp->p3 );      /* Guaranteed by the code generator */
         3252  +    goto open_cursor_set_hints;
         3253  +  }
         3254  +  /* If the cursor is not currently open or is open on a different
         3255  +  ** index, then fall through into OP_OpenRead to force a reopen */
         3256  +case OP_OpenRead:
         3257  +case OP_OpenWrite:
         3258  +
         3259  +  assert( (pOp->p5&(OPFLAG_P2ISREG|OPFLAG_BULKCSR|OPFLAG_SEEKEQ))==pOp->p5 );
         3260  +  assert( pOp->opcode==OP_OpenWrite || pOp->p5==0 || pOp->p5==OPFLAG_SEEKEQ );
  3263   3261     assert( p->bIsReader );
  3264   3262     assert( pOp->opcode==OP_OpenRead || pOp->opcode==OP_ReopenIdx
  3265   3263             || p->readOnly==0 );
  3266   3264   
  3267   3265     if( p->expired ){
  3268   3266       rc = SQLITE_ABORT_ROLLBACK;
  3269   3267       break;
................................................................................
  3318   3316     pCur = allocateCursor(p, pOp->p1, nField, iDb, 1);
  3319   3317     if( pCur==0 ) goto no_mem;
  3320   3318     pCur->nullRow = 1;
  3321   3319     pCur->isOrdered = 1;
  3322   3320     pCur->pgnoRoot = p2;
  3323   3321     rc = sqlite3BtreeCursor(pX, p2, wrFlag, pKeyInfo, pCur->pCursor);
  3324   3322     pCur->pKeyInfo = pKeyInfo;
  3325         -  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
  3326         -  sqlite3BtreeCursorHints(pCur->pCursor, (pOp->p5 & OPFLAG_BULKCSR));
  3327         -
  3328   3323     /* Set the VdbeCursor.isTable variable. Previous versions of
  3329   3324     ** SQLite used to check if the root-page flags were sane at this point
  3330   3325     ** and report database corruption if they were not, but this check has
  3331   3326     ** since moved into the btree layer.  */  
  3332   3327     pCur->isTable = pOp->p4type!=P4_KEYINFO;
         3328  +
         3329  +open_cursor_set_hints:
         3330  +  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
         3331  +  assert( OPFLAG_SEEKEQ==BTREE_SEEK_EQ );
         3332  +  sqlite3BtreeCursorHints(pCur->pCursor,
         3333  +                          (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
  3333   3334     break;
  3334   3335   }
  3335   3336   
  3336   3337   /* Opcode: OpenEphemeral P1 P2 * P4 P5
  3337   3338   ** Synopsis: nColumn=P2
  3338   3339   **
  3339   3340   ** Open a new cursor P1 to a transient table.
................................................................................
  3586   3587     assert( pC->isOrdered );
  3587   3588     assert( pC->pCursor!=0 );
  3588   3589     oc = pOp->opcode;
  3589   3590     pC->nullRow = 0;
  3590   3591   #ifdef SQLITE_DEBUG
  3591   3592     pC->seekOp = pOp->opcode;
  3592   3593   #endif
         3594  +
         3595  +  /* For a cursor with the BTREE_SEEK_EQ hint, only the OP_SeekGE and
         3596  +  ** OP_SeekLE opcodes are allowed, and these must be immediately followed
         3597  +  ** by an OP_IdxGT or OP_IdxLT opcode, respectively, with the same key.
         3598  +  */
         3599  +#ifdef SQLITE_DEBUG
         3600  +  if( sqlite3BtreeCursorHasHint(pC->pCursor, BTREE_SEEK_EQ) ){
         3601  +    assert( pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE );
         3602  +    assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT );
         3603  +    assert( pOp[1].p1==pOp[0].p1 );
         3604  +    assert( pOp[1].p2==pOp[0].p2 );
         3605  +    assert( pOp[1].p3==pOp[0].p3 );
         3606  +    assert( pOp[1].p4.i==pOp[0].p4.i );
         3607  +  }
         3608  +#endif
         3609  + 
  3593   3610     if( pC->isTable ){
  3594   3611       /* The input value in P3 might be of any type: integer, real, string,
  3595   3612       ** blob, or NULL.  But it needs to be an integer before we can do
  3596   3613       ** the seek, so convert it. */
  3597   3614       pIn3 = &aMem[pOp->p3];
  3598   3615       if( (pIn3->flags & (MEM_Int|MEM_Real|MEM_Str))==MEM_Str ){
  3599   3616         applyNumericAffinity(pIn3, 0);

Changes to src/where.c.

  6797   6797         }
  6798   6798         pLevel->iIdxCur = iIndexCur;
  6799   6799         assert( pIx->pSchema==pTab->pSchema );
  6800   6800         assert( iIndexCur>=0 );
  6801   6801         if( op ){
  6802   6802           sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
  6803   6803           sqlite3VdbeSetP4KeyInfo(pParse, pIx);
         6804  +        if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0
         6805  +         && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0
         6806  +         && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0
         6807  +        ){
         6808  +          sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
         6809  +        }
  6804   6810           VdbeComment((v, "%s", pIx->zName));
  6805   6811         }
  6806   6812       }
  6807   6813       if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
  6808   6814       notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  6809   6815     }
  6810   6816     pWInfo->iTop = sqlite3VdbeCurrentAddr(v);