topic Re: Unique Index and Primary Key Oracle in Qlik Replicate

Unique Index and Primary Key Oracle

guilherme-matte — Tue, 01 Aug 2023 00:41:53 GMT

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!

Re: Unique Index and Primary Key Oracle

Michael_Litz — Tue, 01 Aug 2023 00:48:22 GMT

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

Re: Unique Index and Primary Key Oracle

Dana_Baldwin — Tue, 01 Aug 2023 16:10:31 GMT

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

Re: Unique Index and Primary Key Oracle

Heinvandenheuvel — Wed, 02 Aug 2023 14:50:57 GMT

>> 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.