I need to reduce index-sizes because of mssql limit of 900 bytes.
I have a class which has a collection declared as a set. Because of this, the primary key consists of all notnull columns including the foreign key. An index is created out of this primary key. I do not need the index to be over all these columns.
Is there a way to reduce the index size without changing the whole setup of the data structure?
Here is the current configuration of the collection inside of the surrounding class definition:
<set cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" fetch="select" lazy="true" table="mySubsetTable" batch-size="1000" name="attributes">
<key foreign-key="FK_Mothertable">
<column name="number"/>
<column name="data"/>
</key>
<composite-element class="MySubsetElement">
<property name="type" length="200" not-null="true" type="class"/>
<property name="attribute" length="2000" column="attrValue" not-null="false"/>
<property name="myboolean" type="boolean">
<column name="myboolean"/>
</property>
<property name="anotherAttribute" length="200"/>
<property name="evenAnotherAttribute" length="200" not-null="true"/>
<property name="evenOneMoreAttribute" not-null="true">
<type name="SomeClass">
<param name="enumClass">someEnumClass</param>
</type>
</property>
</composite-element>
</set>
I am currently using hibernate 3.3.1 with xdoclet annotations:
/**
* Attributes of this matchable
*
* @hibernate.set table="mySubsetTable" cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" lazy="true"
* batch-size="1000" fetch="select"
* @hibernate.key foreign-key="FK_Mothertable"
* @hibernate.key-column name="number"
* @hibernate.key-column name="data"
* @hibernate.composite-element class="MySubsetElement"
*/
public Set<MySubsetElement> getSubsetElements() { ... }
Thanks a lot for your suggestions!
(And please do not refer me to http://docs.jboss.org/hibernate/ I already found this.)
EDIT I cannot reduce the size of all properties to fit the size limits. An index consisting of the foreign keys would suffice. Also I would really like a solution which does not change the underlying datastructure as I am working on a product which is already in use.
here is how I realized Jimmy's suggestion:
<hibernate-mapping>
<class name="MyParent" ....>
...
<set cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" fetch="select" lazy="true" table="SubsetTable" batch-size="1000" name="attributes">
<key foreign-key="FK_ParentTable" not-null="true">
<column name="number"/>
<column name="data"/>
</key>
<one-to-many class="MySubset" entity-name="MySubsetentity"/>
</set>
...
</class>
<class name="MySubset" ....>
<id name="id" type="long">
<column name="id"/>
<generator class="MyIdGeneratorClass">
<param name="sequence">mySequence</param>
</generator>
</id>
<property name="type" length="200" not-null="true" type="class"/>
<property name="attribute" length="2000" column="attrValue" not-null="false"/>
<property name="myboolean" type="boolean">
<column name="myboolean"/>
</property>
<property name="anotherAttribute" length="200"/>
<property name="evenAnotherAttribute" length="200" not-null="true"/>
<property name="evenOneMoreAttribute" not-null="true">
<type name="SomeClass">
<param name="enumClass">someEnumClass</param>
</type>
</property>
</class>
</hibernate-mapping>
The important part is the not-null="true"
inside the key
tag of the Parent subset definition. This enables the Subset to remain ignorant of the parent.