The following query is taking around 30 hrs to retrieve 16 million records. ETL guys say it used to take 14 hrs to retrieve 14 million records. All indexes are created for the join predicates but still, I am not sure.
SELECT
...
FROM
OTD
INNER JOIN COM ON COM.TX_ID = OTD.TX_ID
INNER JOIN MST ON MST.TX_ID = OTD.TX_ID
INNER JOIN CON ON CON.TX_ID = OTD.TX_ID
INNER JOIN PER ON PER.TX_ID = OTD.TX_ID
INNER JOIN OCD ON OCD.TX_ID = OTD.TX_ID
LEFT OUTER JOIN MTC ON MTC.COUNTRY_ID = CON.COUNTRY_NAME
The above query runs as a part of ETL job. I was wondering if anyone can help me here. One thing I am concerned about the query is the outer join happening on varchar. Is it ok? Let me know if any suggestions to improve the above query's performance.
