| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="../sqlite.css" rel="stylesheet"> |
| 6 |
<title>Virtual Table Indexing Information</title> |
| 7 |
<!-- path=../ --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="../index.html"> |
| 12 |
<img class="logo" src="../images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="../index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='../about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="../docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="../download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='../copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="../support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="../prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='../about.html'>About</a> |
| 35 |
<li><a href='../docs.html'>Documentation</a> |
| 36 |
<li><a href='../download.html'>Download</a> |
| 37 |
<li><a href='../support.html'>Support</a> |
| 38 |
<li><a href='../prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="../search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<!-- keywords: sqlite3_index_info --> |
| 119 |
<div class=nosearch> |
| 120 |
<a href="../c3ref/intro.html"><h2>SQLite C Interface</h2></a> |
| 121 |
<h2>Virtual Table Indexing Information</h2> |
| 122 |
</div> |
| 123 |
<blockquote><pre> |
| 124 |
struct sqlite3_index_info { |
| 125 |
/* Inputs */ |
| 126 |
int nConstraint; /* Number of entries in aConstraint */ |
| 127 |
struct sqlite3_index_constraint { |
| 128 |
int iColumn; /* Column constrained. -1 for ROWID */ |
| 129 |
unsigned char op; /* Constraint operator */ |
| 130 |
unsigned char usable; /* True if this constraint is usable */ |
| 131 |
int iTermOffset; /* Used internally - xBestIndex should ignore */ |
| 132 |
} *aConstraint; /* Table of WHERE clause constraints */ |
| 133 |
int nOrderBy; /* Number of terms in the ORDER BY clause */ |
| 134 |
struct sqlite3_index_orderby { |
| 135 |
int iColumn; /* Column number */ |
| 136 |
unsigned char desc; /* True for DESC. False for ASC. */ |
| 137 |
} *aOrderBy; /* The ORDER BY clause */ |
| 138 |
/* Outputs */ |
| 139 |
struct sqlite3_index_constraint_usage { |
| 140 |
int argvIndex; /* if >0, constraint is part of argv to xFilter */ |
| 141 |
unsigned char omit; /* Do not code a test for this constraint */ |
| 142 |
} *aConstraintUsage; |
| 143 |
int idxNum; /* Number used to identify the index */ |
| 144 |
char *idxStr; /* String, possibly obtained from sqlite3_malloc */ |
| 145 |
int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */ |
| 146 |
int orderByConsumed; /* True if output is already ordered */ |
| 147 |
double estimatedCost; /* Estimated cost of using this index */ |
| 148 |
/* Fields below are only available in SQLite 3.8.2 and later */ |
| 149 |
sqlite3_int64 estimatedRows; /* Estimated number of rows returned */ |
| 150 |
/* Fields below are only available in SQLite 3.9.0 and later */ |
| 151 |
int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */ |
| 152 |
/* Fields below are only available in SQLite 3.10.0 and later */ |
| 153 |
sqlite3_uint64 colUsed; /* Input: Mask of columns used by statement */ |
| 154 |
}; |
| 155 |
</pre></blockquote> |
| 156 |
<p> |
| 157 |
The sqlite3_index_info structure and its substructures is used as part |
| 158 |
of the <a href="../vtab.html">virtual table</a> interface to |
| 159 |
pass information into and receive the reply from the <a href="../vtab.html#xbestindex">xBestIndex</a> |
| 160 |
method of a <a href="../c3ref/module.html">virtual table module</a>. The fields under **Inputs** are the |
| 161 |
inputs to xBestIndex and are read-only. xBestIndex inserts its |
| 162 |
results into the **Outputs** fields.</p> |
| 163 |
|
| 164 |
<p>The aConstraint[] array records WHERE clause constraints of the form:</p> |
| 165 |
|
| 166 |
<p><blockquote>column OP expr</blockquote></p> |
| 167 |
|
| 168 |
<p>where OP is =, <, <=, >, or >=. The particular operator is |
| 169 |
stored in aConstraint[].op using one of the |
| 170 |
<a href="../c3ref/c_index_constraint_eq.html">SQLITE_INDEX_CONSTRAINT_ values</a>. |
| 171 |
The index of the column is stored in |
| 172 |
aConstraint[].iColumn. aConstraint[].usable is TRUE if the |
| 173 |
expr on the right-hand side can be evaluated (and thus the constraint |
| 174 |
is usable) and false if it cannot.</p> |
| 175 |
|
| 176 |
<p>The optimizer automatically inverts terms of the form "expr OP column" |
| 177 |
and makes other simplifications to the WHERE clause in an attempt to |
| 178 |
get as many WHERE clause terms into the form shown above as possible. |
| 179 |
The aConstraint[] array only reports WHERE clause terms that are |
| 180 |
relevant to the particular virtual table being queried.</p> |
| 181 |
|
| 182 |
<p>Information about the ORDER BY clause is stored in aOrderBy[]. |
| 183 |
Each term of aOrderBy records a column of the ORDER BY clause.</p> |
| 184 |
|
| 185 |
<p>The colUsed field indicates which columns of the virtual table may be |
| 186 |
required by the current scan. Virtual table columns are numbered from |
| 187 |
zero in the order in which they appear within the CREATE TABLE statement |
| 188 |
passed to sqlite3_declare_vtab(). For the first 63 columns (columns 0-62), |
| 189 |
the corresponding bit is set within the colUsed mask if the column may be |
| 190 |
required by SQLite. If the table has at least 64 columns and any column |
| 191 |
to the right of the first 63 is required, then bit 63 of colUsed is also |
| 192 |
set. In other words, column iCol may be required if the expression |
| 193 |
(colUsed & ((sqlite3_uint64)1 << (iCol>=63 ? 63 : iCol))) evaluates to |
| 194 |
non-zero.</p> |
| 195 |
|
| 196 |
<p>The <a href="../vtab.html#xbestindex">xBestIndex</a> method must fill aConstraintUsage[] with information |
| 197 |
about what parameters to pass to xFilter. If argvIndex>0 then |
| 198 |
the right-hand side of the corresponding aConstraint[] is evaluated |
| 199 |
and becomes the argvIndex-th entry in argv. If aConstraintUsage[].omit |
| 200 |
is true, then the constraint is assumed to be fully handled by the |
| 201 |
virtual table and might not be checked again by the byte code. The |
| 202 |
aConstraintUsage[].omit flag is an optimization hint. When the omit flag |
| 203 |
is left in its default setting of false, the constraint will always be |
| 204 |
checked separately in byte code. If the omit flag is changed to true, then |
| 205 |
the constraint may or may not be checked in byte code. In other words, |
| 206 |
when the omit flag is true there is no guarantee that the constraint will |
| 207 |
not be checked again using byte code.</p> |
| 208 |
|
| 209 |
<p>The idxNum and idxStr values are recorded and passed into the |
| 210 |
<a href="../vtab.html#xfilter">xFilter</a> method. |
| 211 |
<a href="../c3ref/free.html">sqlite3_free()</a> is used to free idxStr if and only if |
| 212 |
needToFreeIdxStr is true.</p> |
| 213 |
|
| 214 |
<p>The orderByConsumed means that output from <a href="../vtab.html#xfilter">xFilter</a>/<a href="../vtab.html#xnext">xNext</a> will occur in |
| 215 |
the correct order to satisfy the ORDER BY clause so that no separate |
| 216 |
sorting step is required.</p> |
| 217 |
|
| 218 |
<p>The estimatedCost value is an estimate of the cost of a particular |
| 219 |
strategy. A cost of N indicates that the cost of the strategy is similar |
| 220 |
to a linear scan of an SQLite table with N rows. A cost of log(N) |
| 221 |
indicates that the expense of the operation is similar to that of a |
| 222 |
binary search on a unique indexed field of an SQLite table with N rows.</p> |
| 223 |
|
| 224 |
<p>The estimatedRows value is an estimate of the number of rows that |
| 225 |
will be returned by the strategy.</p> |
| 226 |
|
| 227 |
<p>The xBestIndex method may optionally populate the idxFlags field with a |
| 228 |
mask of SQLITE_INDEX_SCAN_* flags. One such flag is |
| 229 |
<a href="../c3ref/c_index_scan_hex.html">SQLITE_INDEX_SCAN_HEX</a>, which if set causes the <a href="../eqp.html">EXPLAIN QUERY PLAN</a> |
| 230 |
output to show the idxNum as hex instead of as decimal. Another flag is |
| 231 |
SQLITE_INDEX_SCAN_UNIQUE, which if set indicates that the query plan will |
| 232 |
return at most one row.</p> |
| 233 |
|
| 234 |
<p>Additionally, if xBestIndex sets the SQLITE_INDEX_SCAN_UNIQUE flag, then |
| 235 |
SQLite also assumes that if a call to the xUpdate() method is made as |
| 236 |
part of the same statement to delete or update a virtual table row and the |
| 237 |
implementation returns SQLITE_CONSTRAINT, then there is no need to rollback |
| 238 |
any database changes. In other words, if the xUpdate() returns |
| 239 |
SQLITE_CONSTRAINT, the database contents must be exactly as they were |
| 240 |
before xUpdate was called. By contrast, if SQLITE_INDEX_SCAN_UNIQUE is not |
| 241 |
set and xUpdate returns SQLITE_CONSTRAINT, any database changes made by |
| 242 |
the xUpdate method are automatically rolled back by SQLite.</p> |
| 243 |
|
| 244 |
<p>IMPORTANT: The estimatedRows field was added to the sqlite3_index_info |
| 245 |
structure for SQLite <a href="../releaselog/3_8_2.html">version 3.8.2</a> (2013-12-06). |
| 246 |
If a virtual table extension is |
| 247 |
used with an SQLite version earlier than 3.8.2, the results of attempting |
| 248 |
to read or write the estimatedRows field are undefined (but are likely |
| 249 |
to include crashing the application). The estimatedRows field should |
| 250 |
therefore only be used if <a href="../c3ref/libversion.html">sqlite3_libversion_number()</a> returns a |
| 251 |
value greater than or equal to 3008002. Similarly, the idxFlags field |
| 252 |
was added for <a href="../releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14). |
| 253 |
It may therefore only be used if |
| 254 |
sqlite3_libversion_number() returns a value greater than or equal to |
| 255 |
3009000. |
| 256 |
</p><p>3 Methods using this object: |
| 257 |
<a href="../c3ref/vtab_collation.html">sqlite3_vtab_collation()</a>, |
| 258 |
<a href="../c3ref/vtab_distinct.html">sqlite3_vtab_distinct()</a>, |
| 259 |
<a href="../c3ref/vtab_rhs_value.html">sqlite3_vtab_rhs_value()</a></p> |
| 260 |
<p>See also lists of |
| 261 |
<a href="../c3ref/objlist.html">Objects</a>, |
| 262 |
<a href="../c3ref/constlist.html">Constants</a>, and |
| 263 |
<a href="../c3ref/funclist.html">Functions</a>.</p> |
| 264 |
|