Indexing: noDbIndex="true" and after that a dbindex?

[ogonzalesdiaz]

Hi, I'm checking some schemas defined by another user and I see: noDbIndex="true" and then <dbindex name="CODLOTEOFERTA">

Is this correct? It is not like: dont do indexing and then asking to do an indexing?

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revIntegrity="normal" revLabel="Recipient Lead Link" revLink="LEAD"
target="nms:recipient" type="link">
     <join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

<dbindex name="CODLOTEOFERTA">
   <keyfield xpath="@CODLOTEOFERTA"/>
</dbindex>

[Parvesh_Parmar]

Hello @ogonzalesdiaz ,

I noticed that you didn't share the entire extended  schema, so I've made some assumptions based on the information available.

Please check following points:

1. I assume your schema includes an attribute named "CODLOTEOFERTA." To create an index for this attribute, you can use the following code:

<dbindex name="CODLOTEOFERTA">
<keyfield xpath="@CODLOTEOFERTA"/>
</dbindex>

This code snippet creates an index named "CODLOTEOFERTA" on the table for the attribute "@CODLOTEOFERTA."

2. The code snippet you provided appears to set up a link with a recipient ID:

<element integrity="normal" label="Recipient" name="recipient"
revIntegrity="normal" revLabel="Recipient Lead Link" revLink="LEAD"
target="nms:recipient" type="link">
<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

Adobe Campaign automatically creates an index on foreign keys. In this case, "@RECIPIENTID" is the foreign key, and Adobe Campaign will create an index for it.

If you want Adobe Campaign not to create an index on this foreign key, you need to add the attribute "noDbIndex" with a value of "true" like this:

<element integrity="normal" label="Recipient" name="recipient" noDbIndex="true"
revIntegrity="normal" revLabel="Recipient Lead Link" revLink="LEAD"
target="nms:recipient" type="link">
<join xpath-dst="@id" xpath-src="@RECIPIENTID"/>
</element>

These two code snippets are independent of each other, and I don't see any issues with them.

Hope it will help you.

Kind regards,
Parvesh

[ogonzalesdiaz]

Hi Parvesh, I've added the full schema. 

1.- Does idexes on Foreign Keys slow performance? 

I'm trying to understand why the person who wrote the XML used: noDbIndex="true".

[_Manoj_Kumar_ · Accepted solution]

Hello @ogonzalesdiaz 

All keys are indexed by default, and the person who created this schema didn't want to create an index on the foreign key.

That is why that person added noDbindex="true"

[LakshmiPravallika]

Hi @ogonzalesdiaz ,

The indexes in your use case are defined as per your requirements, There is no issue in that.

On the Primary key, the unique index was removed as they dont require to fetch huge data based on that Primary key ie, they may use the other index which is defined other than the Primary key to fetch huge data.

Also looks like the Insertion of Data into this schema is huge, so thats why they have removed the index from Primary key (but the default behaviour would be adding the Index on primary key).

So, the use case would be huge data would be inserted, and they want insertion faster and fetching of data would be on the other indexed column,

Please find the Link to be referred for performance of indexes.

https://stackify.com/postgresql-performance-tutorial/

Regards,

Pravallika.