Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Avoid unnecessary affinity transformations when building indices using data from a table. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
10d851353c2abeadbd2852c210a7ff9f |
User & Date: | drh 2014-01-02 19:35:30.181 |
References
2021-06-14
| ||
14:00 | An index on the expression "likely(X)" should store the true value of X, even if X is a real number with no fractional part. The optimization of storing some REAL indexed values as INT (see [10d851353c2abead]) should be omitted for index columns that are expressions. Forum post 3b940c437a. (check-in: 791473bc66 user: drh tags: trunk) | |
2015-05-20
| ||
14:04 | • New ticket [f2ad7de056] Corrupt table created by CREATE TABLE AS followed by compound SELECT. (artifact: 86aa4e2262 user: drh) | |
Context
2014-01-02
| ||
21:05 | Try to factor constant subcomponents of the WHERE clause out of the loop. (check-in: 9d05777fe2 user: drh tags: trunk) | |
19:35 | Avoid unnecessary affinity transformations when building indices using data from a table. (check-in: 10d851353c user: drh tags: trunk) | |
17:57 | Be more aggressive in optimizing constant conditional expressions. (check-in: b7e39851a7 user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
780 781 782 783 784 785 786 | } } nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn; regBase = sqlite3GetTempRange(pParse, nCol); for(j=0; j<nCol; j++){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pIdx->aiColumn[j], regBase+j); | > > > > > > > > | < < < < < < < < | > < | 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 | } } nCol = (prefixOnly && pIdx->uniqNotNull) ? pIdx->nKeyCol : pIdx->nColumn; regBase = sqlite3GetTempRange(pParse, nCol); for(j=0; j<nCol; j++){ sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, pIdx->aiColumn[j], regBase+j); /* If the column affinity is REAL but the number is an integer, then it ** might be stored in the table as an integer (using a compact ** representation) then converted to REAL by an OP_RealAffinity opcode. ** But we are getting ready to store this value back into an index, where ** it should be converted by to INTEGER again. So omit the OP_RealAffinity ** opcode if it is present */ if( sqlite3VdbeGetOp(v, -1)->opcode==OP_RealAffinity ){ sqlite3VdbeDeleteLastOpcode(v); } } if( regOut ){ sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol, regOut); } sqlite3ReleaseTempRange(pParse, regBase, nCol); return regBase; } |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1052 1053 1054 1055 1056 1057 1058 | ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ #define SQLITE_ColumnCache 0x0002 /* Column cache */ #define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */ #define SQLITE_FactorOutConst 0x0008 /* Constant factoring */ | | | 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 | ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ #define SQLITE_ColumnCache 0x0002 /* Column cache */ #define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */ #define SQLITE_FactorOutConst 0x0008 /* Constant factoring */ /* not used 0x0010 // Was: SQLITE_IdxRealAsInt */ #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ |
︙ | ︙ |
Changes to src/test1.c.
︙ | ︙ | |||
6156 6157 6158 6159 6160 6161 6162 | } aOpt[] = { { "all", SQLITE_AllOpts }, { "none", 0 }, { "query-flattener", SQLITE_QueryFlattener }, { "column-cache", SQLITE_ColumnCache }, { "groupby-order", SQLITE_GroupByOrder }, { "factor-constants", SQLITE_FactorOutConst }, | < | 6156 6157 6158 6159 6160 6161 6162 6163 6164 6165 6166 6167 6168 6169 | } aOpt[] = { { "all", SQLITE_AllOpts }, { "none", 0 }, { "query-flattener", SQLITE_QueryFlattener }, { "column-cache", SQLITE_ColumnCache }, { "groupby-order", SQLITE_GroupByOrder }, { "factor-constants", SQLITE_FactorOutConst }, { "distinct-opt", SQLITE_DistinctOpt }, { "cover-idx-scan", SQLITE_CoverIdxScan }, { "order-by-idx-join", SQLITE_OrderByIdxJoin }, { "transitive", SQLITE_Transitive }, { "subquery-coroutine", SQLITE_SubqCoroutine }, { "omit-noop-join", SQLITE_OmitNoopJoin }, { "stat3", SQLITE_Stat3 }, |
︙ | ︙ |
Changes to src/vdbe.h.
︙ | ︙ | |||
171 172 173 174 175 176 177 178 179 180 181 182 183 184 | void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*); void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3); void sqlite3VdbeChangeP5(Vdbe*, u8 P5); void sqlite3VdbeJumpHere(Vdbe*, int addr); void sqlite3VdbeChangeToNoop(Vdbe*, int addr); void sqlite3VdbeChangeP4(Vdbe*, int addr, const char *zP4, int N); void sqlite3VdbeSetP4KeyInfo(Parse*, Index*); void sqlite3VdbeUsesBtree(Vdbe*, int); VdbeOp *sqlite3VdbeGetOp(Vdbe*, int); int sqlite3VdbeMakeLabel(Vdbe*); void sqlite3VdbeRunOnlyOnce(Vdbe*); void sqlite3VdbeDelete(Vdbe*); | > | 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 | void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*); void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3); void sqlite3VdbeChangeP5(Vdbe*, u8 P5); void sqlite3VdbeJumpHere(Vdbe*, int addr); void sqlite3VdbeChangeToNoop(Vdbe*, int addr); void sqlite3VdbeDeleteLastOpcode(Vdbe*); void sqlite3VdbeChangeP4(Vdbe*, int addr, const char *zP4, int N); void sqlite3VdbeSetP4KeyInfo(Parse*, Index*); void sqlite3VdbeUsesBtree(Vdbe*, int); VdbeOp *sqlite3VdbeGetOp(Vdbe*, int); int sqlite3VdbeMakeLabel(Vdbe*); void sqlite3VdbeRunOnlyOnce(Vdbe*); void sqlite3VdbeDelete(Vdbe*); |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
721 722 723 724 725 726 727 728 729 730 731 732 733 734 | sqlite3 *db = p->db; freeP4(db, pOp->p4type, pOp->p4.p); memset(pOp, 0, sizeof(pOp[0])); pOp->opcode = OP_Noop; if( addr==p->nOp-1 ) p->nOp--; } } /* ** Change the value of the P4 operand for a specific instruction. ** This routine is useful when a large program is loaded from a ** static array using sqlite3VdbeAddOpList but we want to make a ** few minor changes to the program. ** | > > > > > > > | 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 | sqlite3 *db = p->db; freeP4(db, pOp->p4type, pOp->p4.p); memset(pOp, 0, sizeof(pOp[0])); pOp->opcode = OP_Noop; if( addr==p->nOp-1 ) p->nOp--; } } /* ** Remove the last opcode inserted */ void sqlite3VdbeDeleteLastOpcode(Vdbe *p){ p->nOp--; } /* ** Change the value of the P4 operand for a specific instruction. ** This routine is useful when a large program is loaded from a ** static array using sqlite3VdbeAddOpList but we want to make a ** few minor changes to the program. ** |
︙ | ︙ |