46:
Suppose you have a transaction table where entries start out with STATUS = 'A' (active), and then may pass through other statuses ('P' for pending, 'W' for "being worked on") before reaching a final status, 'F', at which point it is no longer likely to be processed again.
111:
This index would not bother storing any of the millions of rows that have reached "final" status, 'F', and would allow queries looking for transactions that still "need work" to efficiently search via this index.
210:
It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following:
115:
Similarly, a partial index can be used to index only those rows where a column is not null, which will be of benefit when the column usually is null.
17:
403:
43:
This allows the index to remain small, even though the table may be rather large, and have extreme selectivity.
325:
305:
262:
8:
408:
369:
311:
37:
29:
388:
33:
397:
164:
This index would allow the following query to read only the updated tuples:
349:
273:
32:
which has some condition applied to it so that it includes a subset of
285:
307:
SQL Server 2008 Documentation: Filtered Index Design
Guidelines
279:
50:
In
PostgreSQL, a useful partial index might be defined as:
326:"PostgreSQL Documentation: Release Notes: Release 7.2"
395:
337:Enable partial indexes (Martijn van Oosterhout)
282:supports partial indexes since version 3.8.0.
276:since version 7.2, released in February 2002.
288:supports partial indexes since version 3.2.
272:Partial indexes have been supported in
396:
13:
14:
420:
382:
265:, this type of index is called a
362:
342:
318:
298:
1:
291:
389:The Case For Partial Indexes
7:
404:Database management systems
10:
425:
371:MongoDB V302 Release Notes
256:
212:
166:
117:
52:
126:partial_object_update
350:"Partial Indexes"
312:Microsoft TechNet
416:
376:
375:
366:
360:
359:
357:
356:
346:
340:
339:
334:
333:
322:
316:
315:
302:
252:
249:
246:
243:
240:
237:
234:
231:
228:
225:
222:
219:
216:
206:
203:
200:
197:
194:
191:
188:
185:
182:
179:
176:
173:
170:
160:
157:
154:
151:
148:
145:
142:
139:
136:
133:
130:
127:
124:
121:
107:
104:
101:
98:
95:
92:
89:
86:
83:
80:
77:
74:
71:
68:
65:
62:
59:
56:
24:, also known as
424:
423:
419:
418:
417:
415:
414:
413:
394:
393:
385:
380:
379:
368:
367:
363:
354:
352:
348:
347:
343:
331:
329:
324:
323:
319:
304:
303:
299:
294:
259:
254:
253:
250:
247:
244:
241:
238:
235:
232:
229:
226:
223:
220:
217:
214:
208:
207:
204:
201:
198:
195:
192:
189:
186:
183:
180:
177:
174:
171:
168:
162:
161:
158:
155:
152:
149:
146:
143:
140:
137:
134:
131:
128:
125:
122:
119:
109:
108:
105:
102:
99:
96:
93:
90:
87:
84:
81:
78:
75:
72:
69:
66:
63:
60:
57:
54:
12:
11:
5:
422:
412:
411:
406:
392:
391:
384:
383:External links
381:
378:
377:
361:
341:
317:
296:
295:
293:
290:
267:filtered index
258:
255:
221:partial_salary
213:
167:
118:
61:partial_status
53:
26:filtered index
9:
6:
4:
3:
2:
421:
410:
407:
405:
402:
401:
399:
390:
387:
386:
373:
372:
365:
351:
345:
338:
327:
321:
313:
309:
308:
301:
297:
289:
287:
283:
281:
277:
275:
270:
268:
264:
211:
165:
116:
113:
51:
48:
44:
41:
39:
35:
31:
27:
23:
22:partial index
19:
370:
364:
353:. Retrieved
344:
336:
330:. Retrieved
328:. PostgreSQL
320:
306:
300:
284:
278:
271:
266:
260:
209:
178:object_table
163:
132:object_table
114:
110:
49:
45:
42:
25:
21:
15:
103:'W'
97:'P'
91:'A'
409:PostgreSQL
398:Categories
355:2014-02-04
332:2009-10-09
292:References
274:PostgreSQL
263:SQL Server
202:updated_on
184:updated_on
147:updated_on
138:updated_on
67:txn_table
18:databases
227:employee
286:MongoDB
257:Support
36:in the
280:SQLite
242:salary
215:create
169:select
120:create
82:status
73:status
55:create
28:is an
239:where
218:index
196:order
181:where
144:where
123:index
79:where
58:index
38:table
30:index
248:2100
245:>
193:null
175:from
156:null
34:rows
20:, a
261:In
233:age
190:not
153:not
16:In
400::
335:.
310:.
269:.
224:on
199:by
187:is
150:is
129:on
106:);
85:in
64:on
40:.
374:.
358:.
314:.
251:;
236:)
230:(
205:;
172:*
159:;
141:)
135:(
100:,
94:,
88:(
76:)
70:(
Text is available under the Creative Commons Attribution-ShareAlike License. Additional terms may apply.