DSXchange

Hi,
my production database having fn_hash function.
PROMPT FUNCTION fn_hash
CREATE OR REPLACE FUNCTION fn_hash(in_str clob)
RETURN VARCHAR2
IS
BEGIN
return DBMS_CRYPTO.HASH (in_str, DBMS_CRYPTO.HASH_SH1);
END;
/

My ETL job calling this function to find delta records.

oracleocistage-->lookupwith hashfile(previous)---stgoracleocistage
source query is like this
select BILLING_ACCT_ID,ACCT_ID,ID_DOC_NBR,FN_HASH(BILLING_ACCT_ID|| '|' ||ACCT_ID|| '|' ||ID_DOC_NBR) HSH FROM TABLE A

join condition:hsh-hsh(lookuphashfile)

source contains 10million records..

but job taking 4-5 hrs some times to finish.

please let me know any reason for this..

All sorts of reasons for it to run slowly - external calls, conversions to datatype "clob", joins on non-indexed columns.

Since you have one source using CDC might not be possible, but DataStage has a CRC32 stage for you to make your own hash, and you can then use in your processing.