Home » RDBMS Server » Performance Tuning » decode
| decode [message #229105] |
Thu, 05 April 2007 07:19  |
memory
Messages: 11
Registered: April 2007
Location: India
|
Junior Member |
|
|
can anybody tell me
if we r using many decode statement in one select clause then how performance will effect
or can we use any other fuction instead of decode.......
|
|
|
|
|
|
| Re: decode [message #229493 is a reply to message #229144] |
Mon, 09 April 2007 00:06  |
memory
Messages: 11
Registered: April 2007
Location: India
|
Junior Member |
|
|
michel
i'm sending my code
please can u tell me that where & which part i have to change for improving performance.
CREATE OR REPLACE PROCEDURE FC_ASSET_Daily_Xlation_Test(pv_batch_type_num_i IN NUMBER)
IS
TYPE gtyp_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
--
lv_counter NUMBER := 0;
gv_rowid gtyp_rowid;
--
lv_batch_min_run_dt DATE;
lv_batch_max_run_dt DATE;
--
lv_row_count NUMBER := 0;
lv_commit_size varchar2(100);-------------Datatype changed By i-flex\SD----------------------
lv_curr_val NUMBER := 0;
--
lv_daily_run_id varchar2(100);-------------Datatype changed By i-flex\SD----------------------
lv_xsl_run_id NUMBER := 0;
lv_xfmn_run_id NUMBER := 0;
--
lv_min_batch_num NUMBER := 0;
lv_max_batch_num NUMBER := 0;
--
lv_parameters VARCHAR2(100);
lv_comma1 NUMBER := 0;
lv_comma2 NUMBER := 0;
lv_comma3 NUMBER := 0;
lv_comma4 NUMBER := 0;
lv_comma5 NUMBER := 0;
--
lv_status BOOLEAN;
--
lv_product_batch BCL_log.product_batch%TYPE;
--
lv_prog_name VARCHAR2(50) := 'FC_ASSET_Daily_Xlation.spr';
--
Parameters_Not_Found EXCEPTION;
Remove_Dup_Failed EXCEPTION;
lv_return_status NUMBER(10) := 0;
BEGIN
-- Parameters for this program will be taken from a function called get_xsl_parameters
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;
Common.Get_Product_batch(pv_batch_type_num_i, lv_product_batch); -- Get product batch name E.g.: FC_DAILY.
Common.get_xsl_parameters(pv_batch_type_num_i, lv_parameters);
IF (lv_parameters = 'ERROR')
THEN
RAISE Parameters_Not_found;
END IF;
lv_comma1 := INSTR(lv_parameters, ',', 1, 1);
lv_comma2 := INSTR(lv_parameters, ',', 1, 2);
lv_comma3 := INSTR(lv_parameters, ',', 1, 3);
lv_comma4 := INSTR(lv_parameters, ',', 1, 4);
lv_comma5 := INSTR(lv_parameters, ',', 1, 5);
-- Get 1st parameter
lv_daily_run_id := SUBSTR(lv_parameters, 1, (lv_comma1 - 1) );
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,lv_prog_name||' : Before Checking End dt.');
IF (Common.Chk_Daily_Run_End_Dt(lv_daily_run_id))
THEN
lv_xsl_run_id := SUBSTR(lv_parameters, (lv_comma1 + 1), (lv_comma2 - lv_comma1 - 1));
lv_xfmn_run_id := SUBSTR(lv_parameters, (lv_comma2 + 1), (lv_comma3 - lv_comma2 - 1));
lv_batch_min_run_dt := TO_DATE(SUBSTR(lv_parameters, (lv_comma3 + 1), (lv_comma4 - lv_comma3 - 1)), 'DD-MON-YYYY HH24:MI:SS');
lv_batch_max_run_dt := TO_DATE(SUBSTR(lv_parameters, (lv_comma4 + 1), (lv_comma5 - lv_comma4 - 1)), 'DD-MON-YYYY HH24:MI:SS');
lv_commit_size := SUBSTR(lv_parameters, (lv_comma5 + 1), LENGTH(lv_parameters));
Common.bcl_update_stage(pv_batch_type_num_i,NULL,0,lv_xsl_run_id,'TRANSLATION_INPROGRESS');
lv_min_batch_num := pv_batch_type_num_i||Common.Ods_Id_Seq_Next_Val;
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Min Batchnum : '||lv_min_batch_num||' : Before Insert Txn Info.');
--To get records for which only asset level information is changed.
-------------------------------------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO ODS_ASSET -------------------Append Modified by I-flex\SD\05-04-07-------------------
(ods_batch_id,
ast_asset_num,
ast_rev_num,
ast_start_dt,
ast_end_dt,
ast_extension_cd,
ast_value_basis_cd,
ast_version,
ast_comments,
ast_prod_name,
ast_prod_bu,
ast_ship_dt,
ast_inception_dt,
fc_detail_type_cd,
ast_compundwrntyfl,
ast_undmfgwrntyflg,
ast_produndwrntyfl,
ast_type_cd,
ast_pr_con,
con_con_priv_flg,
ast_pr_postn,
x_pos_type_cd,
con_relationtypecd,
ext_attrib_09,
verify_flag,
uk_flag)
--------------------------------------------------------------------
SELECT
pv_batch_type_num_i||DECODE(MOD(ROWNUM,lv_commit_size), 0, Common.ODS_Id_Seq_Next_Val, LPAD(ODS_Id_Seq.CURRVAL,6,'0')),
custmast.cod_cust_natl_id,
SUBSTR(custmast.cod_cust_natl_id,1,3),
custmast.dat_cust_open,
custmast.dat_cust_close,
DECODE(custmast.cod_cnt,NULL, NULL,Get_LOVPROD.Get_Fclov('SB_CONACT_WAY', Common_File_Func.get_lang, custmast.cod_cnt)),
DECODE(custmast.cod_card_issue_reason, NULL, NULL, Get_LOVPROD.Get_Fclov('SB_CARD_TYPE', Common_File_Func.get_lang, custmast.cod_card_issue_reason)),
Get_LOVPROD.Get_Fclov('SB_TAX_CODE', Common_File_Func.get_lang, custmast.cod_tds),
DECODE(custmast.flg_cust_memo, NULL, NULL, Get_LOVPROD.Get_Fclov('SB_SPECIAL_REMARKS', Common_File_Func.get_lang, custmast.flg_cust_memo)),
inview.fcprod,
inview.fcprodbu,
inview.dt_last_mnt,
inview.file_dt,
inview.detail_type_cd,
'N',
'N',
'N',
'Fin Account',
'Y',
'N',
'Y',
'Fin Account',
Get_LOVPROD.Get_Fclov('INS_RELATION_TO_POLICYHOLDER', Common_File_Func.get_lang, '8'),
'Y',
'Y',
'Y'
FROM cbr_custmast custmast,
------------------------------------------------------------------------------------------------------------------------------------- ---------------
(SELECT /*+ ALL_ROWS */ MAX(prodbal.dat_last_mnt) dt_last_mnt, -------------------All Rows Modified by I-flex\SD\05-04-07--------------------
MAX(prodbal.file_date) file_dt,
prodbal.cod_cust_natl_id,
Get_Lovprod.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fcprod,
Get_Lovprod.Get_Fcprodbu(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fcprodbu,
prodint.detail_type_cd
FROM cbr_prod_bal prodbal,S_PROD_INT prodint
WHERE TO_CHAR(prodbal.cod_prod) = prodint.part_num
AND prodint.detail_type_cd IN ('FC-Saving','FC-TD')
AND prodbal.cod_cust_natl_id IN( SELECT cod_cust_natl_id
FROM cbr_fw_cust_mast fwcustmast
WHERE fwcustmast.dat_last_mnt BETWEEN lv_batch_min_run_dt AND lv_batch_max_run_dt)
GROUP BY prodbal.cod_cust_natl_id, prodint.detail_type_cd,
Get_Lovprod.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd),
Get_Lovprod.Get_Fcprodbu(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd)) inview
WHERE custmast.cod_cust_natl_id = inview.cod_cust_natl_id;
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;
-- Count total number of records inserted into ODS_ASSET
lv_row_count := SQL%ROWCOUNT;
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Inserted : '||lv_row_count||' : Records.');
------------------------------------------------------------------------------------------------------------------------------------- -----------------------
INSERT /*+ APPEND */ INTO ODS_ASSET ----------------Append Modified by I-flex\SD\05\04\07-----------------
(ods_batch_id,
ast_asset_num,
ast_rev_num,
ast_start_dt,
ast_end_dt,
ast_extension_cd,
ast_value_basis_cd,
ast_version,
ast_comments,
ast_prod_name,
ast_current_bal,
ast_prod_bu,
ast_ship_dt,
ast_inception_dt,
fc_detail_type_cd,
ast_compundwrntyfl,
ast_undmfgwrntyflg,
ast_produndwrntyfl,
ast_type_cd,
ast_pr_con,
con_con_priv_flg,
ast_pr_postn,
x_pos_type_cd,
con_relationtypecd,
ext_attrib_09,
verify_flag,
uk_flag)
SELECT
pv_batch_type_num_i||DECODE(MOD(ROWNUM,lv_commit_size), 0, Common.ODS_Id_Seq_Next_Val, LPAD(ODS_Id_Seq.currval,6,'0')),
custmast.cod_cust_natl_id,
--gets branch name from account number
SUBSTR(custmast.cod_cust_natl_id,1,3),
custmast.dat_cust_open,
custmast.dat_cust_close,
DECODE(custmast.cod_cnt,NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_CONACT_WAY', Common_File_Func.get_lang, custmast.cod_cnt)),
DECODE(custmast.cod_card_issue_reason, NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_CARD_TYPE', Common_File_Func.get_lang, custmast.cod_card_issue_reason)),
Get_LOVPROD.GET_FCLOV('SB_TAX_CODE', Common_File_Func.get_lang, custmast.cod_tds),
DECODE(custmast.flg_cust_memo, NULL, NULL, Get_LOVPROD.GET_FCLOV('SB_SPECIAL_REMARKS', Common_File_Func.get_lang, custmast.flg_cust_memo)),
inview.fc_asset_name,
inview.fc_asset_bal,
Get_LOVPROD.Get_Fcprodbu(inview.fc_product, inview.fc_detail_type_cd),
inview.fc_last_bal_dt,
inview.fc_last_batch_dt,
inview.fc_detail_type_cd,
'N',
'N',
'N',
'Fin Account',
'Y',
'N',
'Y',
'Fin Account',
--gets the value from S_LST_OF_VAL;
Get_LOVPROD.GET_FCLOV('INS_RELATION_TO_POLICYHOLDER', Common_File_Func.get_lang, '8'),
'Y',
'Y',
'Y'
FROM cbr_custmast custmast,cbr_fw_cust_mast fwcustmast,
(SELECT inview1.ast_asset_num,
inview1.fc_asset_bal,
inview1.fc_product,
inview1.fc_last_bal_dt,
inview1.fc_last_batch_dt,
inview1.fc_detail_type_cd,
inview1.fc_asset_name
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
FROM (SELECT /*+ ALL_ROWS */ odshldng.ast_asset_num ast_asset_num, -----------------All Rows modified by I-flex\SD\05\04\07-------------------
/*NVL(SUM(odshldng.hld_accrued_intr),0) */999 fc_asset_bal,
DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001') fc_product,
MAX(odshldng.hld_as_of_dt) fc_last_bal_dt,
MAX(odshldng.fc_file_date) fc_last_batch_dt,
prodint.detail_type_cd fc_detail_type_cd,
Get_LOVPROD.Get_Fcprod(DECODE(prodint.detail_type_cd, 'FC-TD', 'FC002', 'FC-Saving', 'FC001'), prodint.detail_type_cd) fc_asset_name,
MAX(odshldng.verify_flag) verify_flag
FROM ODS_HLDNG odshldng,
S_PROD_INT prodint
WHERE odshldng.fc_product = prodint.part_num
AND prodint.detail_type_cd IN ('FC-Saving', 'FC-TD')
AND odshldng.ods_batch_id LIKE Common.Get_BTN(pv_batch_type_num_i, 'HOLDING')||'%'
AND odshldng.verify_flag ='Y'
GROUP BY ast_asset_num,prodint.detail_type_cd) inview1,
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------
(SELECT /*+ LEADING(custmat fwcustmast) */ /*+ Merge(inview) */ ast_asset_num ---------------'Leading' for joins modified by I-flex\SD\05\04\07-------------
FROM ODS_ASSET ast
WHERE ast.ODS_BATCH_ID LIKE pv_batch_type_num_i||'%'
AND ast.verify_flag = 'Y') inview2
WHERE inview1.ast_asset_num = inview2.AST_ASSET_NUM (+)
AND inview2.AST_ASSET_NUM IS NULL ) inview -- ******* 'inview2.AST_ASSET_NUM IS NULL' IS SAME AS "DECODE(inview2.AST_ASSET_NUM,NULL,'ACCEPT','REJECT') = 'ACCEPT' "
WHERE custmast.cod_cust_natl_id = fwcustmast.cod_cust_natl_id
AND custmast.cod_cust_natl_id = inview.ast_asset_num;
-- Count total number of records inserted into ODS_ASSET
lv_row_count := lv_row_count + SQL%ROWCOUNT;
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Inserted : '||lv_row_count||' : Records.');
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;
--
BEGIN
SELECT ODS_Id_Seq.currval
INTO lv_curr_val
FROM dual;
END;
lv_max_batch_num := pv_batch_type_num_i||LPAD(lv_curr_val, 6, '0');
-- Delete the old data from ODS for which new data is coming in this batch
Remove_Duplicates.Asset(lv_product_batch, pv_batch_type_num_i, lv_commit_size, lv_return_status);
IF lv_return_status = -1
THEN
RAISE Remove_Dup_Failed;
END IF;
Common.Reset_Ods_Stage (lv_status, pv_batch_type_num_i);
-- Call BCL_Batch_Run update function to update record in BCL_Batch_Run with end date and stage
Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,lv_row_count,lv_xsl_run_id,'TRANSLATION_COMPLETED');
-- Insert a new record in BCL_Batch_Run table for next process, in this case 'xformation started'
Common.bcl_insert_stage(lv_daily_run_id,pv_batch_type_num_i,'A',SYSDATE,'TRANSFORMATION_STARTED',lv_xfmn_run_id);
IF (lv_row_count >= 0)
THEN
-------------------------------------------------------------------------------------------------------------------------------
INSERT /*+ APPEND */ INTO bcl_ods_batch_dtl (run_id, --------------Append Modified by I-flex\SD\05\04\07------------
ods_batch_id,
xslation_end_dt,
total_no_of_rows_processed,
no_of_error_rows)
SELECT lv_xsl_run_id,ods_batch_id,SYSDATE,count(*),NULL
FROM ODS_ASSET
WHERE ods_batch_id BETWEEN lv_min_batch_num AND lv_max_batch_num
GROUP BY lv_xsl_run_id,ods_batch_id,SYSDATE;
END IF;
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT SBP_BT_RBS01;
ELSE
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,'Cannot proceed because of End_dt is Not Null.');
END IF;
EXCEPTION
WHEN Parameters_Not_Found THEN
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.RunId : '||TO_CHAR(lv_xsl_run_id)||' : Error returned in parameters for Asset Daily Translation program.');
Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');
WHEN Remove_Dup_Failed
THEN
Common.log_message (lv_product_batch,lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.Run_Id : '||TO_CHAR(lv_xsl_run_id)||' : Error returned in Remove_Duplicates.Asset');
Common.bcl_update_stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');
WHEN OTHERS THEN
Common.Log_Message (lv_product_batch, lv_prog_name||' = '||pv_batch_type_num_i,
'BCL_Batch_Run.RunId : '||TO_CHAR(lv_xsl_run_id)||' : '||SUBSTR(Sqlerrm, 1, 400));
ROLLBACK;
-- If an exception caught, update corresponding record in BCL_batch_run table with stage failed
Common.BCL_Update_Stage(pv_batch_type_num_i,SYSDATE,0,lv_xsl_run_id,'TRANSLATION_FAILED');
END FC_Asset_Daily_Xlation_Test;
/
|
|
|
|
|
|
| Re: decode [message #229510 is a reply to message #229508] |
Mon, 09 April 2007 01:16 |
memory
Messages: 11
Registered: April 2007
Location: India
|
Junior Member |
|
|
How can i change? what's the meaning of "change them to an equivalent of these functions"
Which type of equivalent u r talking about can u tell me please
i can not get u.
|
|
|
|
|
|
| Re: decode [message #229520 is a reply to message #229518] |
Mon, 09 April 2007 01:30 |
memory
Messages: 11
Registered: April 2007
Location: India
|
Junior Member |
|
|
ok!!!
but tell me what did i ask to u?
tell me how can i convert those call functions to its euivalent.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
  |
Goto Forum:
Current Time: Thu May 16 07:16:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
