QUALIFY ROW_NUMBER () OVER (PARTITION BY TRAN_ID.SRC_TRAN_ID
ORDER BY TRAN_ID.TRAN_ID) =1
Hi Experts, recently i encountered a performance issue of above query . It is basically to verify the existance of a key column in the target table for the prupose of the Data change capturing. The target is a very huge table with partition on TRAN_DATE column. We can not use this partition condition in the query as we need to identify the key column existance in the entire target table.
Any suggestions to tune this without using parition condition on target table. This query runs multiple times in a day with different source system. If I can tune it, can save lot of resources. Thanks in advance.
May try MLPPI with Source sys Id also on your huge target table. I mean Multi level partition source sys id along with transaction date.
I guess you have limited sources and so you can use hash value of source system ids to parition instead of using hardcode values. (This way can avoid need of changing your parition expression when ever a new source introduced.
Thanks for the reply. I've a question-
I think you said to create a second level parition on source syserm id where as my major partition would remain on Transaction date. (As this is used in lot of other queries)
Will it really show performance improvement if i use only the second level parition with out using the first level of paritions in my query ?
I feel, both levels should be used to get the parition benfit. Correct me if i am wrong.