How to tune a query with a join over a DBLink. It takes longer amount of time to get the result.
Posted on 2011-03-03
I have a typical problem at hand.
R.ACCT_NO = A.ACCT_NO
A.PRODUCT_CLASS = 'SMAS' AND
A.MANOX = 'FA'
We did some modification using Hint and got it explained, the result was better. Below is the query.
SELECT /*+ ORDERED */
FROM SHODS01.BETA_CONTROL_DLY@HODS01 B
JOIN SHODS01.BETA_ACT_DLY@HODS01 C
ON c.BATCH_DTE_CYMD = b.BATCH_DTE_CYMD
AND c.PRODUCT_CLASS = 'SMAS'
AND c.MANOX = 'FA'
JOIN SHODS01.BETA_RAD_DLY@HODS01 A
ON b.BATCH_DTE_CYMD = a.BATCH_DTE_CYMD AND c.acct_no = a.acct_no
WHERE b.TABLE_NME IN ('BETA_RAD_DLY','BETA_ACT_DLY')
and b.LAST_PROC_BCH_IND = 'Y';
As stated this query runs over a DBLink and takes a long time. Do we need to use hint..something like.../*+ordered*/ or /*+use_hash*/ to get the good result? Or is there any way we can improve this query? Its sort of urgent.