Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Further optimizations to the UPDATE logic to avoid making changes to partial indexes if none of the columns mentioned in the WHERE clause are modified by the UPDATE. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | d1365a5bf0ee2f145427b81d2a593f53 |
| User & Date: | drh 2018-09-17 20:47:38 |
Context
|
2018-09-18
| ||
| 16:06 | Minor improvements to the permutations.test script. Most notably, a command like "permutations.test alter%" now runs all test scripts with names that match the LIKE pattern "alter%". Or, say, "permutations wal alter%" to run all alter% tests in wal mode. check-in: 3e1a2f66 user: dan tags: trunk | |
|
2018-09-17
| ||
| 20:47 | Further optimizations to the UPDATE logic to avoid making changes to partial indexes if none of the columns mentioned in the WHERE clause are modified by the UPDATE. check-in: d1365a5b user: drh tags: trunk | |
| 15:19 | Disable the ORDER BY LIMIT optimization in queries using window functions. This fixes a problem that was introduced by check-in [206720129ed2fa8875a286] which attempted to fix ticket [9936b2fa443fec03ff25f9]. This changes is a fix for the follow-in tocket [510cde277783b5fb5de628]. check-in: c6c9585f user: drh tags: trunk | |
Changes
Changes to src/update.c.
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 ... 101 102 103 104 105 106 107 108 109 110 111 112 113 114 ... 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 |
} #endif } /* ** 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 */ ................................................................................ } assert( iIdxCol==XN_EXPR ); assert( pIdx->aColExpr!=0 ); assert( pIdx->aColExpr->a[iCol].pExpr!=0 ); return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr, aXRef,chngRowid); } /* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere ................................................................................ /* There is one entry in the aRegIdx[] array for each index on the table ** being updated. Fill in aRegIdx[] with a register number that will hold ** the key for accessing each index. */ for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ 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; |
| > > > > > > > > > > > > > > > > > > > > > > > > | > > |
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 ... 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 ... 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 |
} #endif } /* ** 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. This is an optimization. False-positives are a ** performance degradation, but false-negatives can result in a corrupt ** index and incorrect answers. ** ** 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 */ ................................................................................ } assert( iIdxCol==XN_EXPR ); assert( pIdx->aColExpr!=0 ); assert( pIdx->aColExpr->a[iCol].pExpr!=0 ); return sqlite3ExprReferencesUpdatedColumn(pIdx->aColExpr->a[iCol].pExpr, aXRef,chngRowid); } /* ** Check to see if index pIdx is a partial index whose conditional ** expression might change values due to an UPDATE. Return true if ** the index is subject to change and false if the index is guaranteed ** to be unchanged. This is an optimization. False-positives are a ** performance degradation, but false-negatives can result in a corrupt ** index and incorrect answers. ** ** 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 indexWhereClauseMightChange( Index *pIdx, /* The index to check */ int *aXRef, /* aXRef[j]>=0 if column j is being updated */ int chngRowid /* true if the rowid is being updated */ ){ if( pIdx->pPartIdxWhere==0 ) return 0; return sqlite3ExprReferencesUpdatedColumn(pIdx->pPartIdxWhere, aXRef, chngRowid); } /* ** Process an UPDATE statement. ** ** UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL; ** \_______/ \________/ \______/ \________________/ * onError pTabList pChanges pWhere ................................................................................ /* There is one entry in the aRegIdx[] array for each index on the table ** being updated. Fill in aRegIdx[] with a register number that will hold ** the key for accessing each index. */ for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ int reg; if( chngKey || hasFK>1 || pIdx==pPk || indexWhereClauseMightChange(pIdx,aXRef,chngRowid) ){ 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; |