Обсуждение: Index on nullable column
Hi, Is an index on a nullable column useful for retrieving rows having that column null? SELECT PlayerID FROM PlayerLoginSession WHERE EndTime IS NULL; Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
"Daniel Caune" <daniel.caune@ubisoft.com> writes: > Is an index on a nullable column useful for retrieving rows having that > column null? Nope, because IS NULL isn't an indexable operator. You can make an end-run around that with a partial index, eg create index fooi on foo(f1) where f1 is null This can be used to satisfy queries using "where f1 is null", but it's not any good for any other purpose. If you often do "where f1 is null and something-about-f2", you might get better mileage with create index fooi on foo(f2) where f1 is null but it's still a very specialized index. regards, tom lane
> > Is an index on a nullable column useful for retrieving rows having that > > column null? > > Nope, because IS NULL isn't an indexable operator. > > You can make an end-run around that with a partial index, eg > > create index fooi on foo(f1) where f1 is null > > This can be used to satisfy queries using "where f1 is null", but it's > not any good for any other purpose. > > If you often do "where f1 is null and something-about-f2", > you might get better mileage with > > create index fooi on foo(f2) where f1 is null > > but it's still a very specialized index. > Thanks Tom. I will use such an index even if it's very specialized; OLAP world is somewhat full of specialized index anyway...:-) -- Daniel