Performance tuning question

Teradata Applications
Enthusiast

Performance tuning question

SELECT
TRAN_ID.SRC_TRAN_ID,
TRAN_ID.SRC_SYS_ID,
TRAN_ID. SRC_TRAN_DT,
TRAN_ID.TRAN_ID,
TRAN_ID.ROW_STAT_CD
FROM
RD_GBS_WIR RD
INNER JOIN
TGT_TRAN_ID TRAN_ID
ON
RD.SRC_TRAN_ID =TRAN_ID.SRC_TRAN_ID
WHERE
TRAN_ID.ROW_STAT_CD=‘A’
AND TRAN_ID.SRC_SYS_ID=‘SOR1’
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.

3 REPLIES
Enthusiast

Re: Performance tuning question

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.

Enthusiast

Re: Performance tuning question

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.

Highlighted
Teradata Employee

Re: Performance tuning question

Have you considered an index on (SRC_SYS_ID, SRC_TRAN_ID)? Maybe a single-table join index that includes ROWID?