Hibernate version:
1.2 (latest)
Name and version of the database you are using:
Sql Server 2005


I want to cluster on a many-to-one foreign key index of some object.

I want to define this in my mapping, so the SchemaExport tool will generate the correct schema.

If you cluster the foreign key index, then the children objects will be physically arranged on the disk with respect to their common parent.

The result?
Dramatically less disk access work when loading the children of any parent object, because the child rows are physically adjacent with respect to the parent.

Does anyone know how this can be defined in the mappings?

IMHO, this isn't NHibernate's job. Whatever tool you're using to export the schema should provide this option. If you're child foreign keys are on the primary key column of the parent table then they're already clustered by default.

If you want to do the opposite and cluster the fk columns of the child table then you have a problem. What happens if the child table has more than 1 fk defined?

I am using the SchemaExport tool (NHibernate.Tool.hbm2ddl.SchemaExport)


Actually, in sql server 2005 the primary key is always clustered by default. I am looking at a generated schema, and the clustered index of the child table is the child's primary key, NOT the child's foreign key. This means the children are physically arranged on the disk by the time/order they are created, NOT by their parent.

The result is, when you have a parent and you need to read the children, the children are physically scattered about the disk, and NOT physically grouped together by their parent.



you mean, what if a child row has numerous many-to-one associations? good question! because you can only have one clustered index in a table. I suppose I would like to explicitly specify a specific foreign key index to cluster, and the default behavior should be clustering on the PK.

Have a look at <database-object>. You should be able to utilize that to at least alter the clustering from the PK to on of the FK.

thank you karlchu.
that was exactly what I was looking for!

for future reference, it may be nice to add another parameter to the many-to-one association mapping like

<many-to-one name="x" column="z" class="foo" clustered-index="true"/>

or perhaps something like

<column name="Name" sql-type="varchar(100)" not-null="true" unique="true" clusteredIndex="IX_Foo" />

just my 2 cents

That's a good suggestion. If you wouldn't mind, please create a feature request on JIRA and reference this discussion thread. Thanks.