OK. Thanks for the clarification, Igor and Filip. I was misunderstanding the partial index to work, in effect, like a standard index on a virtual column based on a function that returns null for the "irrelevant" values, with the index defined to ignore nulls.
I see now from the "inclusion" test described in the paper that the partial index will not be used if the query itself does not contain the same set of conditions that were used to define the index. That makes the partial index safe, not the trouble I was envisioning. Regards Tim Romano On Fri, Aug 20, 2010 at 8:01 AM, Igor Tandetnik <[email protected]> wrote: > Tim Romano <[email protected]> wrote: > > Igor, > > Here's the example where a partial index can "hide" rows. > > > > From the wikipedia article cited by the OP: > > > > <wikipedia> > > 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: > > > > create index partial_salary on employee(age) where salary > 2100; > > > > </wikipedia> > > > > What would happen if you issued these queries? > > > > select max(age) from employee > > select avg(age) from employee > > > > Would the ages of employees earning <= 2100 be included? > > Of course. The presence or absence of an index never affect the meaning of > a query - just its performance. > > > Is the > > partial-index used under those circumstances? > > No, I don't see how it could be beneficial for these queries. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
