These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: I want to define a clustered index in my mappings
PostPosted: Wed Nov 21, 2007 10:35 am 
Beginner
Beginner

Joined: Tue Sep 04, 2007 12:36 pm
Posts: 23
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?

_________________
^^If this post helped you, make sure to rate it Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 21, 2007 11:48 am 
Beginner
Beginner

Joined: Wed Jul 19, 2006 8:24 am
Posts: 35
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?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 21, 2007 1:20 pm 
Beginner
Beginner

Joined: Tue Sep 04, 2007 12:36 pm
Posts: 23
peterlemonjello wrote:
IMHO, this isn't NHibernate's job. Whatever tool you're using to export the schema should provide this option.



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

peterlemonjello wrote:
If you're child foreign keys are on the primary key column of the parent table then they're already clustered by default.

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.

peterlemonjello wrote:
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?


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.

_________________
^^If this post helped you, make sure to rate it Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 21, 2007 4:59 pm 
Hibernate Team
Hibernate Team

Joined: Tue Jun 13, 2006 11:29 pm
Posts: 315
Location: Calgary, Alberta, Canada
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.

_________________
Karl Chu


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 26, 2007 9:52 am 
Beginner
Beginner

Joined: Tue Sep 04, 2007 12:36 pm
Posts: 23
karlchu wrote:
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

_________________
^^If this post helped you, make sure to rate it Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 26, 2007 10:38 am 
Hibernate Team
Hibernate Team

Joined: Tue Jun 13, 2006 11:29 pm
Posts: 315
Location: Calgary, Alberta, Canada
That's a good suggestion. If you wouldn't mind, please create a feature request on JIRA and reference this discussion thread. Thanks.

_________________
Karl Chu


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.