>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS Applicable to Unix only. Sets the >permissions that will be used when certain kinds of directories are created.
>SQLITE_DEFAULT_RECURSIVE_TRIGGERS Sets the default for the recursive_triggers >pragma (the default is 0 or off). >SQLITE_ENABLE_COSTMULT Enables the optimizer to use Cost Multiplier factors >in certain operations. >SQLITE_ENABLE_CURSOR_HINTS Sends additional information about the cursoring >function to the BTREE layer. >SQLITE_COUNTOFVIEW_OPTIMIZATION Enables a specific optimization for counting >records in a view (see the source documentation) >SQLITE_EXPLAIN_ESTIMATED_ROWS Adds information from Query Planner about >Estimated Rows to the Explain output >SQLITE_ENABLE_LOAD_EXTENSION Set the default load extension API to on. >SQLITE_ENABLE_MODULE_COMMENTS Adds additional comments to the explain showing >which module generated the code >SQLITE_STAT4_SAMPLES Changes the number of buckets in the stat4 histogram >created by ANALYZE; These two force type of Windows being used rather than guessing ... >SQLITE_OS_WIN >SQLITE_OS_WINNT >SQLITE_USE_MALLOC_H >SQLITE_USE_MSIZE These two are necessary because when using the MinGW compiler on Windows the code does not automatically detect that MALLOC USABLE SIZE is available and uses the MSVC semantics of the _msize call to get the size of the allocation from the system arena manager, and cannot easily be convinced to do so, except by adding these two internal defines, and getting there using proper compiler detection is problematic. The default assumption that system malloc does not track usable size means that mem1.c allocates an extra 8 bytes in each allocation so that it can store the size information at the beginning of the block, then moves the pointer along 8 bytes so that this size is not overwritten and the consumer of the allocation is none the wiser. This however causes problems because system malloc returns allocations aligned on paragraph (16-byte) boundaries. The extra 8 bytes allocation and the pointer moving up by 8 bytes means that the returned allocation (to user code) is always misaligned from the paragraph boundary leading to alignment exceptions (hardware exceptions) for items which require paragraph alignment because the builtin assumptions about the alignment of memory returned by malloc no longer match reality (so if you store certain items in sqlite3_malloc'd memory which require paragraph alignment the compiler does not and cannot align them correctly -- you can fix this either by (a) increasing the space used for the block header recording the allocation size to a full paragraph multiple or (b) tell the code that MALLOC USABLE SIZE is available and turning the problem over to the runtime library (or (c) use the win32_alloc allocator which does track usable size by default). (or you could generate slower code that assumes misaglinment). >SQLITE_USE_MALLOC_H >SQLITE_USE_MSIZE >SQLITE_DEFAULT_TEMP_CACHE_SIZE Sets the default maximum size for the temp db >in memory. Apparently no longer used. These enable some additional extensions. >SQLITE_ENABLE_STAT_VTAB >SQLITE_ENABLE_UNIONVTAB Once upon a time the sqlite 'now' was step-stable by default and the internal "now" (iCurrentTime in the Vdbe) was reset on each entry into the Vdbe (ie, each sqlite3_step). This is a change I made that only resets iCurrentTime on "first entry" to the Vdbe (pc=0) thus making 'now' statement stable rather than step stable. Changes along the line (when the SLOCHNG was introduced, I forget what version that was, to enable determinism for the query planner but not for indexing (when functions were allowed in index expressions)) meaning that for all intents and purposes this is now "suspenders" since the default is that 'now' is statement stable after that version. >SQLITE_NOW_STABILITY_STMT >SQLITE_USE_PRECISE_TIME >SQLITE_DATETIME_NEW >SQLITE_WIN32_FILE_RANDOM These enable some custom changes I made. >SQLITE_USE_PRECISE_TIME simply replaces the call to the >GetSystemTimeAsFileTime API in the win32 vfs so that it uses the >GetSystemTimePreciseAsFileTime API instead (which means the code will only run >on Windows with that API being supported (Windows 8/Windows Server 2012 kernel >or later -- eventually I may make it choose dynamically at runtime to restore >the compatibility with all versions of Windows and the define will not be >required). Although both APIs return the system time in huns (hundreds of >nanoseconds), the non-Precise version of the call reads from the system clock >which is only updated on each timer tick (which is somewhere between 16.5 ms >and 0.5 ms), depending on what you have requested the multimedia update >frequency be set to (note that the tick frequency is also the minimum sleep >interval since the scheduler is only dispatched on each tick). The Precise >version of the API returns the time in huns without reference to the clock >tick interval (by using the TSC, so if the CPU is old and has a non-stable TSC >then the result may not actually be very Precise after all, however, this is a >problem for Microsoft to fix if they choose to do so (it probably will be as >part of the change of the underlying OS time semantics from a POSIX base to a >UTC base). This means that the iJD (julian day in milliseconds, which is how >sqlite3 tracks time) will always have millisecond precision rather than being >limited to the update granularity of the system timer tick. >SQLITE_DATETIME_NEW turns on some more changes I made to the "datetime" module >by changing the semantics to always return the applicable UTC offset as part >of the resulting string, even when using "localtime" (and thus bypassing most >of the processing for 'utc' since the timestamp with an offset is always a UTC >timestamp). The replacement functions (datetime/date/time) all behave the >same and this turns on that behaviour (which requires a new variable in the >DateTime structure). The replacement datetime/date/time functions (which also >work without this being defined, though the timestamps are not localized) also >permit the use of an IANA standard timezone name in place of 'localtime' (thus >requiring the current IANA timezone database be loaded into the sqlite3 >database in use in order to work) thus fixing the timezone handling on Windows >so that it is accurate (or at least as accurate as the IANA database) for all >timezones for all times (Linux and other OSes that use the timezone database >in their localtime functions do not need this since they know how to compute >localtime properly -- only Windows is horridly broken in this case). >SQLITE_WIN32_FILE_RANDOM is another local change which forces all file opens >to pass the RANDOM flag to the win32 createfile API so as to ensure that the >Windows readahead is always disabled even when the OS is not WINCE. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >Sent: Wednesday, 12 September, 2018 12:12 >To: SQLite mailing list >Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on >update > >Whoa, all sorts of undocumented stuff. Where did you find all these >and what they do? > >Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24 >amalgamation): >SQLITE_DEFAULT_PROXYDIR_PERMISSIONS >SQLITE_DEFAULT_RECURSIVE_TRIGGERS >SQLITE_ENABLE_COSTMULT >SQLITE_ENABLE_CURSOR_HINTS >SQLITE_COUNTOFVIEW_OPTIMIZATION >SQLITE_EXPLAIN_ESTIMATED_ROWS >SQLITE_ENABLE_LOAD_EXTENSION >SQLITE_ENABLE_MODULE_COMMENTS >SQLITE_STAT4_SAMPLES >SQLITE_OS_WIN >SQLITE_OS_WINNT >SQLITE_USE_MALLOC_H >SQLITE_USE_MSIZE > >Search also not finding it in the 3.24 amalgamation either: >SQLITE_DEFAULT_TEMP_CACHE_SIZE >SQLITE_ENABLE_STAT_VTAB >SQLITE_ENABLE_UNIONVTAB >SQLITE_NOW_STABILITY_STMT >SQLITE_USE_PRECISE_TIME >SQLITE_DATETIME_NEW >SQLITE_WIN32_FILE_RANDOM > > >For my own I'm on Windows 7, apparently using MinGW-W64 with GCC >7.3.0 >Compiling from the 3.24.0 amalgamation, with only a couple tweaks to >shell.c to change the default settings. (.timer on, .eqp on, extra >newline to start the prompt, etc.) > >Apparently enable_explain_comments isn't included in the bit that >keeps track of things to report in pragma compile_options. >Also, apparently .version isn't listed in the .help output of the >CLI, but works. Wonder what else is missing. > > >Microsoft Windows [Version 6.1.7601] >Copyright (c) 2009 Microsoft Corporation. All rights reserved. > >D:\>sqlite3 >SQLite version 3.24.0 2018-06-04 19:24:41 >Enter ".help" for usage hints. >Connected to a transient in-memory database. >Use ".open FILENAME" to reopen on a persistent database. > >sqlite> .version >SQLite 3.24.0 2018-06-04 19:24:41 >c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca >gcc-7.3.0 > >sqlite> pragma compile_options; >compile_options >ALLOW_COVERING_INDEX_SCAN >COMPILER=gcc-7.3.0 >DEFAULT_AUTOMATIC_INDEX >DEFAULT_CACHE_SIZE=-65536 >DEFAULT_FILE_FORMAT=4 >DEFAULT_FOREIGN_KEYS >DEFAULT_JOURNAL_SIZE_LIMIT=0 >DEFAULT_LOCKING_MODE=0 >DEFAULT_MEMSTATUS >DEFAULT_MMAP_SIZE=0 >DEFAULT_PAGE_SIZE=4096 >DEFAULT_SYNCHRONOUS=0 >DEFAULT_WAL_AUTOCHECKPOINT=1 >DEFAULT_WAL_SYNCHRONOUS=0 >DEFAULT_WORKER_THREADS=4 >ENABLE_COLUMN_METADATA >ENABLE_DBSTAT_VTAB >ENABLE_MEMORY_MANAGEMENT >ENABLE_RTREE >ENABLE_STMT_SCANSTATUS >ENABLE_UNKNOWN_SQL_FUNCTION >LIKE_DOESNT_MATCH_BLOBS >MAX_ATTACHED=125 >MAX_EXPR_DEPTH=0 >MAX_MMAP_SIZE=0 >MAX_WORKER_THREADS=4 >OMIT_SHARED_CACHE >STMTJRNL_SPILL=4194304 >THREADSAFE=0 >USE_ALLOCA >Run Time: real 0.000 user 0.000000 sys 0.000000 > >sqlite> CREATE TABLE Foo(x, y, z); >QUERY PLAN >`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) >Run Time: real 0.000 user 0.000000 sys 0.000000 > >sqlite> CREATE INDEX FooX on Foo(x); >Run Time: real 0.000 user 0.000000 sys 0.000000 > >sqlite> CREATE INDEX FooZ on Foo(z); >Run Time: real 0.000 user 0.000000 sys 0.000000 > >sqlite> CREATE INDEX FooLenZ on Foo(length(z)); >Run Time: real 0.000 user 0.000000 sys 0.000000 > >sqlite> explain UPDATE foo SET x=1 WHERE rowid=1; >addr opcode p1 p2 p3 p4 p5 comment >---- ------------- ---- ---- ---- ------------- -- ----------- >-- >0 Init 0 33 0 00 Start at 33 >1 Null 0 7 8 00 >r[7..8]=NULL >2 OpenWrite 0 2 0 3 00 root=2 >iDb=0; Foo >3 Explain 3 0 0 SEARCH TABLE foo USING INTEGER >PRIMARY KEY (rowid=?) 00 >4 Integer 1 12 0 00 r[12]=1 >5 SeekRowid 0 7 12 00 >intkey=r[12]; pk >6 Rowid 0 8 0 00 r[8]=rowid >7 OpenWrite 1 5 0 k(2,,) 00 root=5 >iDb=0; FooLenZ >8 OpenWrite 3 3 0 k(2,,) 00 root=3 >iDb=0; FooX >9 IsNull 8 32 0 00 if >r[8]==NULL goto 32 >10 Integer 1 9 0 00 r[9]=1 >11 Column 0 1 10 00 r[10]=Foo.y >12 Column 0 2 11 00 r[11]=Foo.z >13 Noop 0 0 0 00 uniqueness >check for FooLenZ >14 Copy 11 13 0 00 r[13]=r[11] >15 PureFunc0 0 13 2 length(1) 01 FooLenZ >column 0 >16 IntCopy 8 3 0 00 r[3]=r[8]; >rowid >17 MakeRecord 2 2 1 00 >r[1]=mkrec(r[2..3]); for FooLenZ >18 Noop 0 0 0 00 uniqueness >check for FooX >19 SCopy 9 5 0 00 r[5]=r[9]; >x >20 IntCopy 8 6 0 00 r[6]=r[8]; >rowid >21 MakeRecord 5 2 4 00 >r[4]=mkrec(r[5..6]); for FooX >22 Copy 11 13 0 00 r[13]=r[11] >23 PureFunc0 0 13 14 length(1) 01 >24 Rowid 0 15 0 00 r[15]=rowid >25 IdxDelete 1 14 2 00 >key=r[14..15] >26 Column 0 0 14 00 r[14]=Foo.x >27 IdxDelete 3 14 2 00 >key=r[14..15] >28 IdxInsert 1 1 2 2 00 key=r[1] >29 IdxInsert 3 4 5 2 00 key=r[4] >30 MakeRecord 9 3 13 00 >r[13]=mkrec(r[9..11]) >31 Insert 0 13 8 Foo 05 intkey=r[8] >data=r[13] >32 Halt 0 0 0 00 >33 Transaction 0 1 4 0 01 >usesStmtJournal=0 >34 Goto 0 1 0 00 >Run Time: real 0.062 user 0.000000 sys 0.015600 > >sqlite> > >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Wednesday, September 12, 2018 1:04 PM >To: SQLite mailing list >Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on >update > > >The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 >(on Windows 10) ... with the following options defined: > >#define _WIN32_WINNT 0x0600 >#define WINVER _WIN32_WINNT >#define SQLITE_DEFAULT_CACHE_SIZE 262144 // 1 GB >#define SQLITE_DEFAULT_TEMP_CACHE_SIZE 262144 // 1 GB >default: 500 pages >#define SQLITE_DEFAULT_FOREIGN_KEYS 1 // >default: 0 >#define SQLITE_DEFAULT_PAGE_SIZE 4096 // >default: 4096 max: 65536 >#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 256 // >default: 1000 pages >#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755 // >default: 0755 >#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS 1 // >default: 0 >#define SQLITE_DEFAULT_SHARED_CACHE 0 // >default: 0 >#define SQLITE_DEFAULT_MMAP_SIZE 0 // >default: 0 >#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1 >#define SQLITE_INTROSPECTION_PRAGMAS 1 // Add >Instropsection Pragmas >#define SQLITE_ENABLE_8_3_NAMES 1 >#define SQLITE_ENABLE_API_ARMOR 1 // Enable >API Armour >#define SQLITE_ENABLE_COLUMN_METADATA 1 >#define SQLITE_ENABLE_COSTMULT 1 >#define SQLITE_ENABLE_CURSOR_HINTS 1 >#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1 >#define SQLITE_ENABLE_DBPAGE_VTAB 1 >#define SQLITE_ENABLE_DBSTAT_VTAB 1 >#define SQLITE_ENABLE_DESERIALIZE 1 >#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 >#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1 >#define SQLITE_ENABLE_FTS3 1 >#define SQLITE_ENABLE_FTS3_PARENTHESIS 1 >#define SQLITE_ENABLE_FTS4 1 >#define SQLITE_ENABLE_FTS5 1 >#define SQLITE_ENABLE_JSON1 1 // Enable >JSON1 -- when standard extension >#define SQLITE_ENABLE_LOAD_EXTENSION 1 >#define SQLITE_ENABLE_LOCKING_STYLE 1 >#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1 // Enable >Memory Management (sqlite3_release_memory) >#define SQLITE_ENABLE_MODULE_COMMENTS 1 >#define SQLITE_ENABLE_PREUPDATE_HOOK 1 >#define SQLITE_ENABLE_RTREE 1 >#define SQLITE_ENABLE_SORTER_REFERENCES 1 // Enable >Sorter References >#define SQLITE_ENABLE_STAT_VTAB 1 // Enable >dbstat_register called from shell >#define SQLITE_ENABLE_STAT1 1 >#define SQLITE_ENABLE_STAT2 1 >#define SQLITE_ENABLE_STAT3 1 >#define SQLITE_ENABLE_STAT4 1 >#define SQLITE_ENABLE_STMTVTAB 1 // Enable >Stmt VTAB >#define SQLITE_ENABLE_UNIONVTAB 1 // Enable >unionvtab >#define SQLITE_STAT4_SAMPLES 64 // >default: 24 samples >#define SQLITE_SOUNDEX 1 >#define SQLITE_THREADSAFE 1 // 0 = >Single Threaded, 1 = Serialized, 2 = Multithreaded >#define SQLITE_TEMP_STORE 2 // 0 = >Files Always, 1 = Files, 2 = Memory, 3 Memory Always >#define SQLITE_USE_URI 1 // Enable >URI Filenames >#define SQLITE_ALLOW_URI_AUTHORITY 1 // Allow >Authority (Host) in URI >#define SQLITE_MAX_ATTACHED 15 // >default: 10 max: 62 >#define SQLITE_OS_WIN 1 >#define SQLITE_OS_WINNT 1 >#define SQLITE_NOW_STABILITY_STMT 1 // Make >'now' stable within a statement, not only for a step >#define WHERE_PATH_SIMPLE 50 // Paths >to remember for 2-way joins >#define WHERE_PATH_COMPLEX 100 // Paths >to remember for >2-way joins >#define SQLITE_USE_PRECISE_TIME 1 // Use >GetSystemTimePreciseAsFileTime >#define SQLITE_DATETIME_NEW 1 // Use >New Datetime Functions >#define SQLITE_WIN32_FILE_RANDOM 1 // Force >Windows RANDOM access cache behaviour >#define SQLITE_DEFAULT_WAL_SYNCHRONOUS 1 // >Reduce Synchronous to NORMAL in WAL mode >#define SQLITE_DIRECT_OVERFLOW_READ 1 // Do >not cache overflow pages in SQLite pagecache >#define SQLITE_LIKE_DOESNT_MATCH_BLOBS 1 // >Disable LIKE matching for BLOBS >#define HAVE_FDATASYNC 1 >#define HAVE_GMTIME_R 1 >#define HAVE_LOCALTIME_S 1 >#define HAVE_USLEEP 1 >#define HAVE_UTIME 1 >#define UNICODE_STRING_MAX_BYTES ((WORD)65534) >#define UNICODE_STRING_MAX_CHARS (32766) >#define HAVE_ISNAN 1 >#define SQLITE_USE_MALLOC_H 1 >#define SQLITE_USE_MSIZE 1 >#define LONGDOUBLE_TYPE __float128 > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >>Sent: Wednesday, 12 September, 2018 10:21 >>To: SQLite mailing list >>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on >>update >> >>A little weird and definitely differet, what version are you using >>and how are you compiling it? I checked with the precompiled Windows >>binary to make sure it wasn't something weird from my compilation, >>and it looks pretty close to my original, but different. So now I'm >>wondering where the noop's, explain's, and comments are coming from >>and what affects them. >> >> >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >>Sent: Wednesday, September 12, 2018 11:40 AM >>To: SQLite mailing list >>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on >>update >> >> >>Interesting ... I get different output with explain comments >enabled: >> >>addr opcode p1 p2 p3 p4 p5 comment >>---- ------------- ---- ---- ---- ------------- -- ---------- >- >>-- >>0 Init 0 41 0 00 Start at >41 >>1 Null 0 7 8 00 >>r[7..8]=NULL >>2 OpenWrite 0 2 0 3 00 root=2 >>iDb=0; Foo >>3 Noop 0 0 0 00 Begin >>WHERE-loop0: Foo >>4 Integer 1 12 0 00 r[12]=1 >>5 SeekRowid 0 9 12 00 >>intkey=r[12] >>6 Noop 0 0 0 00 Begin >>WHERE-core >>7 Rowid 0 8 0 00 r[8]=rowid >>8 Noop 0 0 0 00 End WHERE- >>core >>9 Noop 0 0 0 00 End WHERE- >>loop0: Foo >>10 OpenWrite 1 5 0 k(2,,) 00 root=5 >>iDb=0; FooLenZ >>11 OpenWrite 3 3 0 k(2,,) 00 root=3 >>iDb=0; FooX >>12 IsNull 8 40 0 00 if >>r[8]==NULL goto 40 >>13 Integer 1 9 0 00 r[9]=1 >>14 Column 0 1 10 00 >r[10]=Foo.y >>15 Column 0 2 11 00 >r[11]=Foo.z >>16 Noop 0 0 0 00 BEGIN: >>GenCnstCks(0,1,8,8,0) >>17 Noop 0 0 0 00 uniqueness >>check for FooLenZ >>18 Copy 11 13 0 00 >r[13]=r[11] >>19 PureFunc0 0 13 2 length(1) 01 FooLenZ >>column 0 >>20 IntCopy 8 3 0 00 r[3]=r[8]; >>rowid >>21 MakeRecord 2 2 1 00 >>r[1]=mkrec(r[2..3]); for FooLenZ >>22 Noop 0 0 0 00 uniqueness >>check for FooX >>23 SCopy 9 5 0 00 r[5]=r[9]; >>x >>24 IntCopy 8 6 0 00 r[6]=r[8]; >>rowid >>25 MakeRecord 5 2 4 00 >>r[4]=mkrec(r[5..6]); for FooX >>26 Noop 0 0 0 00 END: >>GenCnstCks(0) >>27 Noop 0 0 0 00 >>GenRowIdxDel for FooLenZ >>28 Column 0 2 13 40 >r[13]=Foo.z >>29 PureFunc0 0 13 14 length(1) 01 >>30 Rowid 0 15 0 00 >r[15]=rowid >>31 IdxDelete 1 14 2 00 >>key=r[14..15] >>32 Noop 0 0 0 00 >>GenRowIdxDel for FooX >>33 Column 0 0 14 00 >r[14]=Foo.x >>34 IdxDelete 3 14 2 00 >>key=r[14..15] >>35 Delete 0 68 8 Foo 00 >>36 IdxInsert 1 1 2 2 00 key=r[1] >>37 IdxInsert 3 4 5 2 00 key=r[4] >>38 MakeRecord 9 3 13 00 >>r[13]=mkrec(r[9..11]) >>39 Insert 0 13 8 Foo 05 >intkey=r[8] >>data=r[13] >>40 Halt 0 0 0 00 >>41 Transaction 0 1 4 0 01 >>usesStmtJournal=0 >>42 Goto 0 1 0 00 >> >> >> >>--- >>The fact that there's a Highway to Hell but only a Stairway to >Heaven >>says a lot about anticipated traffic volume. >> >> >>>-----Original Message----- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond >>>Sent: Wednesday, 12 September, 2018 07:54 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes >on >>>update >>> >>>Just showing with "explain comments" enabled. >>> >>> >>>sqlite> explain update foo set x = 1 where rowid = 1; >>>addr opcode p1 p2 p3 p4 p5 comment >>>---- ------------- ---- ---- ---- ------------- -- --------- >- >>- >>>-- >>>0 Init 0 33 0 00 Start at >>33 >>>1 Null 0 7 8 00 >>>r[7..8]=NULL >>>2 OpenWrite 0 2 0 3 00 root=2 >>>iDb=0; Foo >>>3 Explain 3 0 0 SEARCH TABLE foo USING >>INTEGER >>>PRIMARY KEY (rowid=?) 00 >>>4 Integer 1 12 0 00 r[12]=1 >>>5 SeekRowid 0 7 12 00 >>>intkey=r[12]; pk >>>6 Rowid 0 8 0 00 >r[8]=rowid >>>7 OpenWrite 1 5 0 k(2,,) 00 root=5 >>>iDb=0; FooLenZ >>>8 OpenWrite 3 3 0 k(2,,) 00 root=3 >>>iDb=0; FooX >>>9 IsNull 8 32 0 00 if >>>r[8]==NULL goto 32 >>>10 Integer 1 9 0 00 r[9]=1 >>>11 Column 0 1 10 00 >>r[10]=Foo.y >>>12 Column 0 2 11 00 >>r[11]=Foo.z >>>13 Noop 0 0 0 00 >uniqueness >>>check for FooLenZ >>>14 Copy 11 13 0 00 >>r[13]=r[11] >>>15 PureFunc0 0 13 2 length(1) 01 FooLenZ >>>column 0 >>>16 IntCopy 8 3 0 00 >r[3]=r[8]; >>>rowid >>>17 MakeRecord 2 2 1 00 >>>r[1]=mkrec(r[2..3]); for FooLenZ >>>18 Noop 0 0 0 00 >uniqueness >>>check for FooX >>>19 SCopy 9 5 0 00 >r[5]=r[9]; >>>x >>>20 IntCopy 8 6 0 00 >r[6]=r[8]; >>>rowid >>>21 MakeRecord 5 2 4 00 >>>r[4]=mkrec(r[5..6]); for FooX >>>22 Copy 11 13 0 00 >>r[13]=r[11] >>>23 PureFunc0 0 13 14 length(1) 01 >>>24 Rowid 0 15 0 00 >>r[15]=rowid >>>25 IdxDelete 1 14 2 00 >>>key=r[14..15] >>>26 Column 0 0 14 00 >>r[14]=Foo.x >>>27 IdxDelete 3 14 2 00 >>>key=r[14..15] >>>28 IdxInsert 1 1 2 2 00 key=r[1] >>>29 IdxInsert 3 4 5 2 00 key=r[4] >>>30 MakeRecord 9 3 13 00 >>>r[13]=mkrec(r[9..11]) >>>31 Insert 0 13 8 Foo 05 >>intkey=r[8] >>>data=r[13] >>>32 Halt 0 0 0 00 >>>33 Transaction 0 1 4 0 01 >>>usesStmtJournal=0 >>>34 Goto 0 1 0 00 >>> >>> >>>-----Original Message----- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of Deon Brewis >>>Sent: Tuesday, September 11, 2018 8:20 PM >>>To: SQLite mailing list >>>Subject: [sqlite] SQLITE touches unchanged expression indexes on >>>update >>> >>>It seems like there is an opportunity for improvement on updates if >>>an index contains expressions. >>> >>>In the following example: >>> >>>CREATE TABLE Foo(x, y, z); >>>CREATE INDEX FooX on Foo(x); >>>CREATE INDEX FooZ on Foo(z); >>>CREATE INDEX FooLenZ on Foo(length(z)); >>> >>>explain UPDATE foo SET x=1 WHERE rowid=1; >>> >>>I see the plan below. Notice it's updating FooLenZ even though the >>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. >It >>>doesn't try to update FooZ, just FooLenZ, but both should be >>>untouched. >>> >>>RecNo addr opcode p1 p2 p3 p4 p5 comment >>>----- ---- ----------- -- -- -- --------- -- ------- >>> 1 0 Init 0 30 0 00 (null) >>> 2 1 Null 0 7 8 00 (null) >>> 3 2 OpenWrite 0 2 0 3 00 (null) // opening >>'Foo' >>>(expected) >>> 4 3 Integer 1 12 0 00 (null) >>> 5 4 SeekRowid 0 6 12 00 (null) >>> 6 5 Rowid 0 8 0 00 (null) >>> 7 6 OpenWrite 1 5 0 k(2,,) 00 (null) // opening >>>'FooLenZ' (NOT expected) >>> 8 7 OpenWrite 3 3 0 k(2,,) 00 (null) // opening >>>'FooX' (expected) >>> 9 8 IsNull 8 29 0 00 (null) >>> 10 9 Integer 1 9 0 00 (null) >>> 11 10 Column 0 1 10 00 (null) >>> 12 11 Column 0 2 11 00 (null) >>> 13 12 Copy 11 13 0 00 (null) >>> 14 13 Function0 0 13 2 length(1) 01 (null) >>> 15 14 IntCopy 8 3 0 00 (null) >>> 16 15 MakeRecord 2 2 1 00 (null) >>> 17 16 SCopy 9 5 0 00 (null) >>> 18 17 IntCopy 8 6 0 00 (null) >>> 19 18 MakeRecord 5 2 4 00 (null) >>> 20 19 Copy 11 13 0 00 (null) >>> 21 20 Function0 0 13 14 length(1) 01 (null) >>> 22 21 Rowid 0 15 0 00 (null) >>> 23 22 IdxDelete 1 14 2 00 (null) >>> 24 23 Column 0 0 14 00 (null) >>> 25 24 IdxDelete 3 14 2 00 (null) >>> 26 25 IdxInsert 1 1 2 2 00 (null) // updating >>>'FooLenZ' (NOT expected) >>> 27 26 IdxInsert 3 4 5 2 00 (null) // updating >>>'FooX' (expected) >>> 28 27 MakeRecord 9 3 13 00 (null) >>> 29 28 Insert 0 13 8 Foo 05 (null) >>> 30 29 Halt 0 0 0 00 (null) >>> 31 30 Transaction 0 1 42 0 01 (null) >>> 32 31 Goto 0 1 0 00 (null) >>> >>>sqlite_master: >>>RecNo type name tbl_name rootpage sql >>>----- ----- ------- -------- -------- ----------------------------- >- >>- >>>------- >>> 1 table Foo Foo 2 CREATE TABLE Foo(x, y, z) >>> 2 index FooX Foo 3 CREATE INDEX FooX on Foo(x) >>> 3 index FooZ Foo 4 CREATE INDEX FooZ on Foo(z) >>> 4 index FooLenZ Foo 5 CREATE INDEX FooLenZ on >>>Foo(length(z)) >>> >>> >>> >>>_______________________________________________ >>>sqlite-users mailing list >>>sqlite-users@mailinglists.sqlite.org >>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >>>_______________________________________________ >>>sqlite-users mailing list >>>sqlite-users@mailinglists.sqlite.org >>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >> >> >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
