Home » RDBMS Server » Performance Tuning » decode
decode [message #229105] Thu, 05 April 2007 07:19 Go to next message
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 #229144 is a reply to message #229105] Thu, 05 April 2007 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just not a performances point of view but if you have many decodes use CASE instead, it is surely easier to read the query.

Regards
Michel
Re: decode [message #229493 is a reply to message #229144] Mon, 09 April 2007 00:06 Go to previous messageGo to next message
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 #229508 is a reply to message #229493] Mon, 09 April 2007 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove all functions you can in your queries and change them to an equivalent of these functions.
SQL optimizer can't optimize function calls. It calls the function for every row and maybe several times for each row.

Regards
Michel
Re: decode [message #229510 is a reply to message #229508] Mon, 09 April 2007 01:16 Go to previous messageGo to next message
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 #229518 is a reply to message #229510] Mon, 09 April 2007 01:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Which type of equivalent u r talking about can u tell me please
i can not get u.


Please Don't use IM SPEAK !!!


Regards
Taj
Re: decode [message #229520 is a reply to message #229518] Mon, 09 April 2007 01:30 Go to previous messageGo to next message
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.
Re: decode [message #229526 is a reply to message #229520] Mon, 09 April 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Function is code.
Code may be put inline.

Simple case: if function returns "param1"+"param2", don't call the function and put in the query "param1+param2".

Regards
Michel
Re: decode [message #229527 is a reply to message #229520] Mon, 09 April 2007 01:39 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

but tell me what did i ask to u?

Sorry but again you did some mistake.
and also please use CODE tags with your query.


I think you should go through below link.
http://www.orafaq.com/forum/t/59964/93410/


Regards
Taj
Re: decode [message #229546 is a reply to message #229526] Mon, 09 April 2007 02:06 Go to previous messageGo to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
how ???
can u give one example from my query?
Re: decode [message #229556 is a reply to message #229546] Mon, 09 April 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No I can't as I don't what's inside your functions.

Regards
Michel
Re: decode [message #229842 is a reply to message #229556] Tue, 10 April 2007 02:50 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Closed. Continue in your new thread with the same unreadable code.

http://www.orafaq.com/forum/fa/448/0/

MHE
Previous Topic: about materialized views
Next Topic: How can I do like this?
Goto Forum:
  


Current Time: Fri Dec 09 11:45:22 CST 2016

Total time taken to generate the page: 0.14229 seconds