/ Check-in [9fced545]
Login
☰Home Timeline Branches

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

Overview
Comment:Change the name of the analyze_as_needed pragma to "optimize". Enhance the comment (which will become documentation, assuming these changes land on trunk) to explain that the optimize pragma is likely to be enhanced in various ways in future releases and that applications should not depend upon the current behavior.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | auto-analyze
Files: files | file ages | folders
SHA1: 9fced545a6f80c55d6dc4a6106cb2d3569566b3e
User & Date: drh 2017-02-22 20:24:10
Context
2017-02-23
00:58
Do a single OP_Expire at the very end of "PRAGMA optimize", and omit the OP_Expire on ANALYZE commands invoked by the pragma. (check-in: 188300a3 user: drh tags: auto-analyze)
2017-02-22
20:24
Change the name of the analyze_as_needed pragma to "optimize". Enhance the comment (which will become documentation, assuming these changes land on trunk) to explain that the optimize pragma is likely to be enhanced in various ways in future releases and that applications should not depend upon the current behavior. (check-in: 9fced545 user: drh tags: auto-analyze)
19:49
Merge integrity_check and other improvements from trunk. (check-in: fe073905 user: drh tags: auto-analyze)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pragma.c. 

1859
1860
1861
1862
1863
1864
1865
1866
1867
1868












1869
1870
1871
1872
1873
1874

1875
1876
1877
1878
1879
1880
1881
1882
1883



1884
1885
1886
1887
1888
1889
1890
1891
1892

  */
  case PragTyp_SHRINK_MEMORY: {
    sqlite3_db_release_memory(db);
    break;
  }

  /*
  **  PRAGMA analyze_as_needed
  **  PRAGMA schema.analyze_as_needed
  **












  ** This pragma runs ANALYZE on any tables which would have benefitted
  ** from having recent statistics at some point since the start of the
  ** current connection.  Only tables in "schema" are analyzed in the 
  ** second form.  In the first form, all tables except TEMP tables are
  ** checked.
  **

  ** A table is analyzed only if both of the following are true:
  **
  ** (1) The query planner used sqlite_stat1-style statistics for one or
  **     more indexes of the table at some point during the lifetime of
  **     the current connection.
  **
  ** (2) One or more indexes of the table are currently unanalyzed OR
  **     the number of rows in the table has increased by 25 times or more
  **     since the last time ANALYZE was run.



  */
  case PragTyp_ANALYZE_AS_NEEDED: {
    int iDbLast;           /* Loop termination point for the schema loop */
    int iTabCur;           /* Cursor for a table whose size needs checking */
    HashElem *k;           /* Loop over tables of a schema */
    Schema *pSchema;       /* The current schema */
    Table *pTab;           /* A table in the schema */
    Index *pIdx;           /* An index of the table */
    LogEst szThreshold;    /* Size threshold above which reanalysis is needd */








|
|

>
>
>
>
>
>
>
>
>
>
>
>
|
|
|



>
|








>
>
>

|








1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908

  */
  case PragTyp_SHRINK_MEMORY: {
    sqlite3_db_release_memory(db);
    break;
  }

  /*
  **  PRAGMA optimize
  **  PRAGMA schema.optimize
  **
  ** Attempt to optimize the database.  All schemas are optimized in the first
  ** form, and only the specified schema is optimized in the second form.
  **
  ** The details of optimizations performed by this pragma does are expected
  ** to change and improve over time.  Applications should anticipate that
  ** this pragma will perform new optimizations in future releases.
  **
  ** Argments to this pragma are currently ignored, but future enhancements
  ** might make use of arguments to control which optimizations are allowed
  ** or to suggest limits on how much CPU time and I/O should be expended
  ** in the optimization effort.
  **
  ** The current implementation runs ANALYZE on any tables which might have 
  ** benefitted from having recent statistics at some point since the start
  ** of the current connection.  Only tables in "schema" are analyzed in the 
  ** second form.  In the first form, all tables except TEMP tables are
  ** checked.
  **
  ** In the current implementation, a table is analyzed only if both of
  ** the following are true:
  **
  ** (1) The query planner used sqlite_stat1-style statistics for one or
  **     more indexes of the table at some point during the lifetime of
  **     the current connection.
  **
  ** (2) One or more indexes of the table are currently unanalyzed OR
  **     the number of rows in the table has increased by 25 times or more
  **     since the last time ANALYZE was run.
  **
  ** The rules for when tables are analyzed are likely to change in
  ** future releases.
  */
  case PragTyp_OPTIMIZE: {
    int iDbLast;           /* Loop termination point for the schema loop */
    int iTabCur;           /* Cursor for a table whose size needs checking */
    HashElem *k;           /* Loop over tables of a schema */
    Schema *pSchema;       /* The current schema */
    Table *pTab;           /* A table in the schema */
    Index *pIdx;           /* An index of the table */
    LogEst szThreshold;    /* Size threshold above which reanalysis is needd */

Changes to src/pragma.h. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

34
35
36
37
38
39
40
...
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
...
415
416
417
418
419
420
421







422
423
424
425
426
427
428

/* DO NOT EDIT!
** This file is automatically generated by the script at
** ../tool/mkpragmatab.tcl.  To update the set of pragmas, edit
** that script and rerun it.
*/

/* The various pragma types */
#define PragTyp_ANALYZE_AS_NEEDED              0
#define PragTyp_HEADER_VALUE                   1
#define PragTyp_AUTO_VACUUM                    2
#define PragTyp_FLAG                           3
#define PragTyp_BUSY_TIMEOUT                   4
#define PragTyp_CACHE_SIZE                     5
#define PragTyp_CACHE_SPILL                    6
#define PragTyp_CASE_SENSITIVE_LIKE            7
#define PragTyp_COLLATION_LIST                 8
#define PragTyp_COMPILE_OPTIONS                9
#define PragTyp_DATA_STORE_DIRECTORY          10
#define PragTyp_DATABASE_LIST                 11
#define PragTyp_DEFAULT_CACHE_SIZE            12
#define PragTyp_ENCODING                      13
#define PragTyp_FOREIGN_KEY_CHECK             14
#define PragTyp_FOREIGN_KEY_LIST              15
#define PragTyp_INCREMENTAL_VACUUM            16
#define PragTyp_INDEX_INFO                    17
#define PragTyp_INDEX_LIST                    18
#define PragTyp_INTEGRITY_CHECK               19
#define PragTyp_JOURNAL_MODE                  20
#define PragTyp_JOURNAL_SIZE_LIMIT            21
#define PragTyp_LOCK_PROXY_FILE               22
#define PragTyp_LOCKING_MODE                  23
#define PragTyp_PAGE_COUNT                    24
#define PragTyp_MMAP_SIZE                     25

#define PragTyp_PAGE_SIZE                     26
#define PragTyp_SECURE_DELETE                 27
#define PragTyp_SHRINK_MEMORY                 28
#define PragTyp_SOFT_HEAP_LIMIT               29
#define PragTyp_SYNCHRONOUS                   30
#define PragTyp_TABLE_INFO                    31
#define PragTyp_TEMP_STORE                    32
................................................................................
#if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD)
 {/* zName:     */ "activate_extensions",
  /* ePragTyp:  */ PragTyp_ACTIVATE_EXTENSIONS,
  /* ePragFlg:  */ 0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
 {/* zName:     */ "analyze_as_needed",
  /* ePragTyp:  */ PragTyp_ANALYZE_AS_NEEDED,
  /* ePragFlg:  */ PragFlg_NoColumns,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
 {/* zName:     */ "application_id",
  /* ePragTyp:  */ PragTyp_HEADER_VALUE,
  /* ePragFlg:  */ PragFlg_NoColumns1|PragFlg_Result0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ BTREE_APPLICATION_ID },
#endif
................................................................................
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
 {/* zName:     */ "mmap_size",
  /* ePragTyp:  */ PragTyp_MMAP_SIZE,
  /* ePragFlg:  */ 0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },







 {/* zName:     */ "page_count",
  /* ePragTyp:  */ PragTyp_PAGE_COUNT,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
 {/* zName:     */ "page_size",
  /* ePragTyp:  */ PragTyp_PAGE_SIZE,








<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







<
<
<
<
<







 







>
>
>
>
>
>
>








1
2
3
4
5
6
7

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
...
129
130
131
132
133
134
135





136
137
138
139
140
141
142
...
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

/* DO NOT EDIT!
** This file is automatically generated by the script at
** ../tool/mkpragmatab.tcl.  To update the set of pragmas, edit
** that script and rerun it.
*/

/* The various pragma types */

#define PragTyp_HEADER_VALUE                   0
#define PragTyp_AUTO_VACUUM                    1
#define PragTyp_FLAG                           2
#define PragTyp_BUSY_TIMEOUT                   3
#define PragTyp_CACHE_SIZE                     4
#define PragTyp_CACHE_SPILL                    5
#define PragTyp_CASE_SENSITIVE_LIKE            6
#define PragTyp_COLLATION_LIST                 7
#define PragTyp_COMPILE_OPTIONS                8
#define PragTyp_DATA_STORE_DIRECTORY           9
#define PragTyp_DATABASE_LIST                 10
#define PragTyp_DEFAULT_CACHE_SIZE            11
#define PragTyp_ENCODING                      12
#define PragTyp_FOREIGN_KEY_CHECK             13
#define PragTyp_FOREIGN_KEY_LIST              14
#define PragTyp_INCREMENTAL_VACUUM            15
#define PragTyp_INDEX_INFO                    16
#define PragTyp_INDEX_LIST                    17
#define PragTyp_INTEGRITY_CHECK               18
#define PragTyp_JOURNAL_MODE                  19
#define PragTyp_JOURNAL_SIZE_LIMIT            20
#define PragTyp_LOCK_PROXY_FILE               21
#define PragTyp_LOCKING_MODE                  22
#define PragTyp_PAGE_COUNT                    23
#define PragTyp_MMAP_SIZE                     24
#define PragTyp_OPTIMIZE                      25
#define PragTyp_PAGE_SIZE                     26
#define PragTyp_SECURE_DELETE                 27
#define PragTyp_SHRINK_MEMORY                 28
#define PragTyp_SOFT_HEAP_LIMIT               29
#define PragTyp_SYNCHRONOUS                   30
#define PragTyp_TABLE_INFO                    31
#define PragTyp_TEMP_STORE                    32
................................................................................
#if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD)
 {/* zName:     */ "activate_extensions",
  /* ePragTyp:  */ PragTyp_ACTIVATE_EXTENSIONS,
  /* ePragFlg:  */ 0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif





#if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS)
 {/* zName:     */ "application_id",
  /* ePragTyp:  */ PragTyp_HEADER_VALUE,
  /* ePragFlg:  */ PragFlg_NoColumns1|PragFlg_Result0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ BTREE_APPLICATION_ID },
#endif
................................................................................
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
 {/* zName:     */ "mmap_size",
  /* ePragTyp:  */ PragTyp_MMAP_SIZE,
  /* ePragFlg:  */ 0,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
 {/* zName:     */ "optimize",
  /* ePragTyp:  */ PragTyp_OPTIMIZE,
  /* ePragFlg:  */ PragFlg_NoColumns,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
 {/* zName:     */ "page_count",
  /* ePragTyp:  */ PragTyp_PAGE_COUNT,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
 {/* zName:     */ "page_size",
  /* ePragTyp:  */ PragTyp_PAGE_SIZE,

Changes to tool/mkpragmatab.tcl. 

358
359
360
361
362
363
364
365
366
367
368
369
370
371
372


  NAME: soft_heap_limit
  FLAG: Result0

  NAME: threads
  FLAG: Result0

  NAME: analyze_as_needed
  FLAG: NoColumns
}

# Open the output file
#
set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"
puts "Overwriting $destfile with new pragma table..."








|








358
359
360
361
362
363
364
365
366
367
368
369
370
371
372


  NAME: soft_heap_limit
  FLAG: Result0

  NAME: threads
  FLAG: Result0

  NAME: optimize
  FLAG: NoColumns
}

# Open the output file
#
set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"
puts "Overwriting $destfile with new pragma table..."

This page was generated in about 0.01s by Fossil 2.11 [8ccab68fc6] 2020-03-27 22:25:20