How to create db index on relation with deployment table via items.xml

[Former Member]

How to create db index on relation with deployment table via items.xml

[mansurarisoy · Accepted solution]

For one-to-many relations you can add index to the itemtype of the many-part of the relation. For example for ProductProductOrderLimitRelation where Product is many-pnd ProductOrderLimit is one-part of the relation, to add an index on p_productOrderLimit column of the table you can use the following code snippet.

 <itemtype code="Product" autocreate="false" generate="false">
     <indexes>
         <index name="productOrderLimitIDX">       
             <key attribute="productOrderLimit"/>
         </index>
     </indexes>
 </itemtype>

For the many-to-many relations, things are a little different. You need to create a extensionname-advanced-deployment.xml as defined in the following link:

https://help.hybris.com/6.7.0/hcd/8c6254f086691014b095a08a61d1efed.html

Then, you can define the indexes in this file. You can investigate core-advanced-deployment.xml file to learn how to accomplish this. I had to delete deployment tag of my custom relation and define the deployment in this advanced-deployment.xml. Also I added deployment property for the relation with the following convention de.hybris.platform.persistence.link.extensionname_ExampleManyToManyRelation

 <relation code="ExampleManyToManyRelation" autocreate="true" generate="true" localized="false"
           deployment="de.hybris.platform.persistence.link.extensionname_ExampleManyToManyRelation">
     <!--Following deployment tag is commented out to be able to add indexes to this table in extensionname-advanced-deployment.xml -->
     <!--<deployment table="ExampleManyToManyRel" typecode="12000"/>-->
     <sourceElement qualifier="exampleSource" type="Product" cardinality="many" collectiontype="set"/>
     <targetElement qualifier="exampleTarget" type="Product" cardinality="many" collectiontype="set"/>
 </relation>

And I added indexes in extensionname-advanced-deployment.xml as follows:

 <model name="hybris" description="...">
     <package name="de.hybris.jakarta" description="the base package for all hybris beans">
         <package name="de.hybris.platform.persistence" description="all entity beans">
             <package name="de.hybris.platform.persistence.link">
                 <!-- link deployments MUST be placed in package "de.hybris.platform.persistence.link" due to a webshere bug -->
                 <bean name="extensionname_ExampleManyToManyRelation"  generic="true" abstract="false" type="Entity" typecode="12000" reentrant="True" cmp-type="Container" cacheFinders="true">
                     <extends target="Link"/>
                     <bean-mapping persistence-name="ExampleManyToManyRel">
                         <index name="qualSourceTargetIDX">
                             <index-key attribute="qualifierInternal" />
                             <index-key attribute="sourcePKInternal" />
                             <index-key attribute="targetPKInternal" />
                         </index>
                     </bean-mapping>
                 </bean>
             </package>
         </package>
     </package>
 </model>

Edit: I found a better way to define indexes for many-to-many relations in Hybris wiki suggested by in the following link: https://wiki.hybris.com/display/forum/creating+composite+key+in+many+to+many+relation

It works and its better than what I suggested before, so I recommend using this method:

 <items>
    <relations>
       <relation code="MyRel" autocreate="true">
          <!-- important: define own deployment for your relation  !!! -->
          <deployment .../>
          ...
       </relation>
    <itemtypes>
       <itemtype code="MyRel"
                 autocreate="false"
                 generate="false">
          <indexes>
             <index name="myRelIdx" unique="true">
                <key attribute="source"/>
                <key attribute="target"/>
                <!-- if it's localized: include language -->
                <key attribute="language"/>
             </index>
          </indexes>
       </itemtype>
    </itemtypes>
 </items>

Hope this helps,
Mansur

[Former Member]

I am also having the same question. Can someone help me out in creating an Index on a relation.

[Former Member]

This is how our relation is declared. Unfortunately the <Indexes> Element is not working inside.

     <relation code="Product2XXXXXXXXX" generate="true"
         localized="false" autocreate="true">
         <deployment table="XXXXXXXX" typecode="12162"  />
         <sourceElement qualifier="xxxxs" type="XXXX"
             cardinality="many">
             <modifiers read="true" write="true" search="true"
                 optional="true" />
         </sourceElement>
         <targetElement qualifier="products" type="Product"
             cardinality="many">
             <modifiers read="true" write="true" search="true"
                 optional="true" />
         </targetElement>
     </relation>

[Former Member]

I'm also interested in this topic.

[Former Member]

relations automatically will create indexes

[Former Member]

@Brendan In response to your reply "relations automatically will create indexes" I can verify that there indeed is 4 created indexes on the cat2catrel table; on linkosurce, linktarget, qualifier and rseqnr and seqnr.

But, the question remains - if one have the need for additional indexes - where can/shall they be specified int items.xml (or other file for that matter)?

We have found the need to create an additional index in the cat2catrel table on the following columns : TypePkString , TargetPK , PK , SourcePK

Kind regards Peter

[Former Member]

Hi Kumar,

How do you declare your many-many relation ?

Br

[Former Member]

I'm facing the same issue, it appears to me that the only element that supports <indexes>and <index> elements is the the <itemtype> element.

The relationship in question is the Category2Category which is supplied by the platform