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..
server job running slow
Moderators: chulett, rschirm, roy
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>