SQL Server and PostgreSQL support filtered indexes / partial indexes:
CREATE INDEX idx ON tab (col) WHERE (col > 0)
They are much smaller than ordinary indexes, as they will contain only the data for which the WHERE clause yields TRUE. This can have a significant impact on INSERT / UPDATE / DELETE statements.
Currently, such an index can be emulated in Oracle using function-based indexes:
CREATE INDEX idx ON tab (CASE WHEN col > 0 THEN col END)
This works because Oracle doesn't index null values, but it's cumbersome because:
- Queries now have to use an equivalent expression to the one used in the index, i.e. they have to be aware of the exact index specification
- The index gets hairy when we want to use this technique on a composite index