Unique Index and Primary Key Oracle

[guilherme-matte]

Hello Team!

I have a Replication task from Oracle to Azure SQL DB which all tables have an Unique Index in the Source.

Regarding the Unique Index, does it perform worse than a Primary Key whenever using CDC?

Should i look forward to get those tables with a PK or there isn't much difference performance wise?

I've notice some big difference between some tasks performance, one mainly with PK and this other one with Unique Index, but I'm unsure wether this is could be the main factor or not.

Cheers!

[Michael_Litz]

Hi @guilherme-matte 

I don't believe there is much difference between the PK or UK on a table from the performance side of things - they will both use an indexed read on the target and should be similar speed.

Any other performance differences are most likely due to something else.

Thanks,
Michael

[Dana_Baldwin]

Hi @guilherme-matte 

Please check with your target Oracle DBA. It could be that the indexes on the poorly performing tables are fragmented or the table statistics need to be refreshed.

Thanks,

Dana

[Heinvandenheuvel · Accepted solution]

>> Regarding the Unique Index, does it perform worse than a Primary Key whenever using CDC?

Yes, it has the potential to perform significantly worse. The degree of issues will depend on that (here Aure SQL, which I never studied for this purpose), and the number of rows to be affeted.

See my comments earlier today for : https://community.qlik.com/t5/Qlik-Replicate/Failed-to-start-bulk-apply-transaction/td-p/2099304

>> Should i look forward to get those tables with a PK

In my opinion - YES.

>> notice some big difference between some tasks performance, one mainly with PK and this other one with Unique Index

Identify, Isolate, Measure, Explain.  Task Setting - Logging - TARGET_APPLY TRACE - can provide a lot of insight.

Hein.