Creating filtered SQL indexes via Hibernate

wabrit · **Joined:** Mon Feb 20, 2006 9:23 am **Posts:** 9

Is it possible to use Hibernate annotations (or configuration XML) to generate the SQL statement necessary to create a filtered index e.g.

Code:

CREATE INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;

It's not obviously available given the properties available on org.hibernate.annotations.Index, but there may be something I'm missing.

mmerder · **Posted:** Tue Nov 10, 2009 5:08 am

No, I don't think this statement could be generated. The only place I know, where to do stuff like that would be the import.sql file.

jesse_sweetland · **Posted:** Tue Jan 12, 2010 3:42 pm

This could be done, but it would require a couple of code changes that are pure concessions to SQL Server (and maybe Sybase, but I don't know if this is a function of Transact-SQL or just Microsoft's version of it).

First, the Dialect interface would need a new boolean isFilteredIndexSupported() method. Second, a new SqlServer2008Dialect would need to be created that either extends or duplicates one of the other SqlServer dialects. Third, all dialects except SqlServer2008Dialect would need to implement isFilteredIndexSupported() to return false. Finally, the sqlConstraintString methods in the UniqueKey class would have to be modified to check the isFilteredIndexSupported() method of the Dialect and append a " where column1 is not null [and column2 is not null ... and columnN is not null]" for all Columns 1..N where isNullable() is true.

I'm in a position right now where I could really use this, and I may end up submitting a patch for it, but there are three things working against me. First, it requires a change to the base Dialect that is a concession to a single DB vendor. Second, we have to run supported versions of this stuff and the odds getting this accepted, merged, and released in time for my use is slim. Third, the documentation clearly states that this stuff is for development use and funky indexes are better managed outside of HBM2DDL (and I can't really argue with that).

davidl2 · **Posted:** Tue Jan 12, 2010 4:47 pm

Postgres also supports indexes over a subset of the rows in a table (see Postgres 8.3/8.4 manual section 11.8 "Partial Indexes", but they've been supported since version 7.4 or earlier).