SQLite

Check-in [c9f045295c]
Login

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

Overview
Comment:Improved presentation on the new code that prevents unnecessary writes to expressions on indexes during an UPDATE when the expression does not reference any of the columns that are changing.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c9f045295c4577752b0847ff2027b44661e6cb15bb08b942ccb3a0ef396f3dec
User & Date: drh 2018-09-16 15:01:25.994
Context
2018-09-16
16:18
Add the new "explain" virtual table in ext/misc. Use this virtual table for additional test cases for the optimization that avoids updating indexes on expressions when none of the columns changed by the UPDATE are in the expression. (check-in: 2404304cc1 user: drh tags: trunk)
15:01
Improved presentation on the new code that prevents unnecessary writes to expressions on indexes during an UPDATE when the expression does not reference any of the columns that are changing. (check-in: c9f045295c user: drh tags: trunk)
2018-09-15
21:43
Increase the version number to 3.26.0 as we start the next development cycle. (check-in: 885f0f8252 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/update.c.
︙︙
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98






99
100
101
102
103
104
105

}

/*
** Check to see if column iCol of index pIdx references any of the
** columns defined by aXRef and chngRowid.  Return true if it does
** and false if not.
**
** The iCol-th column of pIdx will be an expression.
**
** aXRef[j] will be non-negative if column j of the original table is
** being updated.  chngRowid will be true if the rowid of the table is
** being updated.
*/
static int indexExprRefsUpdatedColumn(
  Index *pIdx,      /* The index containing the expression to analyze */
  int iCol,         /* Which column of the index is the expression */
  int *aXRef,       /* aXRef[j]>=0 if column j is being updated */
  int chngRowid     /* true if the rowid is being updated */
){






  assert( pIdx->aColExpr!=0 );
  assert( pIdx->aColExpr->a[iCol].pExpr!=0 );
  return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr,
                                            aXRef,chngRowid);
}

/*








<
<




|
|
|



>
>
>
>
>
>








80
81
82
83
84
85
86


87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

}

/*
** Check to see if column iCol of index pIdx references any of the
** columns defined by aXRef and chngRowid.  Return true if it does
** and false if not.
**


** aXRef[j] will be non-negative if column j of the original table is
** being updated.  chngRowid will be true if the rowid of the table is
** being updated.
*/
static int indexColumnIsBeingUpdated(
  Index *pIdx,      /* The index to check */
  int iCol,         /* Which column of the index to check */
  int *aXRef,       /* aXRef[j]>=0 if column j is being updated */
  int chngRowid     /* true if the rowid is being updated */
){
  i16 iIdxCol = pIdx->aiColumn[iCol];
  if( iIdxCol>=0 ){
    return aXRef[iIdxCol]>=0;
  }
  if( iIdxCol==XN_ROWID ) return 1;
  assert( iIdxCol==XN_EXPR );
  assert( pIdx->aColExpr!=0 );
  assert( pIdx->aColExpr->a[iCol].pExpr!=0 );
  return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr,
                                            aXRef,chngRowid);
}

/*
︙︙
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349

    int reg;
    if( chngKey || hasFK>1 || pIdx->pPartIdxWhere || pIdx==pPk ){
      reg = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }else{
      reg = 0;
      for(i=0; i<pIdx->nKeyCol; i++){
        i16 iIdxCol = pIdx->aiColumn[i];
        if( (iIdxCol>=0 && aXRef[iIdxCol]>=0)
         || iIdxCol==XN_ROWID
         || (iIdxCol==XN_EXPR
             && indexExprRefsUpdatedColumn(pIdx,i,aXRef,chngRowid))
        ){
          reg = ++pParse->nMem;
          pParse->nMem += pIdx->nColumn;
          if( (onError==OE_Replace)
           || (onError==OE_Default && pIdx->onError==OE_Replace) 
          ){
            bReplace = 1;
          }








<
<
<
<
|
<








334
335
336
337
338
339
340




341

342
343
344
345
346
347
348

    int reg;
    if( chngKey || hasFK>1 || pIdx->pPartIdxWhere || pIdx==pPk ){
      reg = ++pParse->nMem;
      pParse->nMem += pIdx->nColumn;
    }else{
      reg = 0;
      for(i=0; i<pIdx->nKeyCol; i++){




        if( indexColumnIsBeingUpdated(pIdx, i, aXRef, chngRowid) ){

          reg = ++pParse->nMem;
          pParse->nMem += pIdx->nColumn;
          if( (onError==OE_Replace)
           || (onError==OE_Default && pIdx->onError==OE_Replace) 
          ){
            bReplace = 1;
          }
︙︙