The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.
Example of simple inline views:
Select t1.emp_no, t1.emp_name, t2.dept_namefrom (Select emp_no, dept_no, emp_name from emp) t1, -- Inline view 1 (Select dept_no, dept_name from dept) t2 -- Inline view 2where t1.dept_no = t2.dept_no;If the SQL is complex and uses inline views, then it takes longer time to execute. For example the following SQL – which is a bit complex one, took around 530 seconds to execute.
SELECT fbal.financial_year, fbal.org_unit_code, acc.account_code_n106, acc.PRODUCT_CODE, SUM(fbal.balance_jul), SUM(fbal.balance_aug),SUM(fbal.balance_sep), SUM(fbal.balance_oct), SUM(fbal.balance_nov),SUM(fbal.balance_dec), SUM(fbal.balance_jan), SUM(fbal.balance_feb),SUM(fbal.balance_mar), SUM(fbal.balance_apr), SUM(fbal.balance_may),SUM(fbal.balance_jun)FROM FIN_MONTH_BALANCE fbal, /* Inline view START */(SELECT oa.financial_year, account_code_n106, nvl(ods.product_code, oa.product_code) PRODUCT_CODE FROM ACCOUNTS_DET OA, PRODUCT_TYPES_SYNONYM ODS WHERE oa.product_code = ods.parent_product_code(+) AND oa. process_key = (SELECT MAX(PROCESS_KEY) FROM ACCOUNTS_DET WHERE financial_year = 2010)) acc /* Inline view END */,ORG_UNIT_CODE_SYNONYM orgsynWHERE fbal.account_code_n106 = acc.account_code_n106 and fbal.financial_year = 2010 AND fbal.process_key = 2338051 AND acc.account_code_n106 in ( SELECT drlm.report_line FROM REPORT_LN_DIMENSION drlm, CUBE_REPLN_REFERENCE ocrr WHERE drlm.report_line_type ='A' AND CUBE_NAME='Mse' AND drlm.master_structure_code = ocrr.MASTER_STRUCTURE_CODE)AND orgsyn.from_year_org (+) = fbal.org_unit_codeAND orgsyn.from_year (+) = 2010AND orgsyn.to_year (+) = 2010GROUP BY fbal.financial_year, fbal.org_unit_code, acc.account_code_n106, acc.PRODUCT_CODE;The Explain Plan for the SQL is as follows:
------------------------------------------------------------------------------------------OPERATION OBJECT_OWNER OBJECT_NAME COST CARDIN. BYTES------------------------------------------------------------------------------------------SELECT STATEMENT 10514 198772 35580188 SORT 7637 105829 16720982 HASH JOIN 6386 105829 16720982 HASH JOIN 6183 93524 13654504 HASH JOIN 1471 14218 696682 VIEW CDW_ODS 1121 69806 2233792 COUNT HASH JOIN 1121 69806 3699718 TABLE ACCESSCDW_ODS ACCOUNTS_DET 1085 69806 1884762 SORT 1 10 TABLE ACCECDW_ODS ACCOUNTS_DET 1085 89750 897500 TABLE ACCESSCDW_ODS PRODUCT_TYPES_SYNONY 2 164 4264 VIEW SYS VW_NSO_1 306 14218 241706 HASH JOIN 306 14218 625592 TABLE ACCESS CDW_ODS CUBE_REPLN_REFERENCE 2 2 38 TABLE ACCESS CDW_ODS REPORT_LN_DIMENSION 303 104034 2600850 PARTITION RANGE TABLE ACCESS CDW_ODS FIN_MONTH_BALANCE 4452 459175 44539975 INDEX CDW_ODS INDEX1 20 5920 71040------------------------------------------------------------------------------------------Without trying to re-write the SQL logic or doing anything else to improve the performance, I just added “ROWNUM” as one of the columns in the inline view. So, the modified SQL is as follows:
SELECT fbal.financial_year, fbal.org_unit_code, acc.account_code_n106, acc.PRODUCT_CODE, SUM(fbal.balance_jul), SUM(fbal.balance_aug),SUM(fbal.balance_sep), SUM(fbal.balance_oct), SUM(fbal.balance_nov),SUM(fbal.balance_dec), SUM(fbal.balance_jan), SUM(fbal.balance_feb),SUM(fbal.balance_mar), SUM(fbal.balance_apr), SUM(fbal.balance_may),SUM(fbal.balance_jun)FROM FIN_MONTH_BALANCE fbal, /* Inline view START */(SELECT ROWNUM, oa.financial_year, account_code_n106, nvl(ods.product_code, oa.product_code) PRODUCT_CODE FROM ACCOUNTS_DET OA, PRODUCT_TYPES_SYNONYM ODS WHERE oa.product_code = ods.parent_product_code(+) AND oa. process_key = (SELECT MAX(PROCESS_KEY) FROM ACCOUNTS_DET WHERE financial_year = 2010)) acc /* Inline view END */,ORG_UNIT_CODE_SYNONYM orgsynWHERE fbal.account_code_n106 = acc.account_code_n106 and fbal.financial_year = 2010 AND fbal.process_key = 2338051 AND acc.account_code_n106 in ( SELECT drlm.report_line FROM REPORT_LN_DIMENSION drlm, CUBE_REPLN_REFERENCE ocrr WHERE drlm.report_line_type ='A' AND CUBE_NAME='Mse' AND drlm.master_structure_code = ocrr.MASTER_STRUCTURE_CODE)AND orgsyn.from_year_org (+) = fbal.org_unit_codeAND orgsyn.from_year (+) = 2010AND orgsyn.to_year (+) = 2010GROUP BY fbal.financial_year, fbal.org_unit_code, acc.account_code_n106, acc.PRODUCT_CODE;Now the SQL got executed in 105 seconds – so it ran 5 times faster!!
The Explain Plan for this SQL is as follows:
------------------------------------------------------------------------------------------OPERATION OBJECT_OWNER OBJECT_NAME COST CARDIN. BYTES------------------------------------------------------------------------------------------SELECT STATEMENT 7637 105829 16720982 SORT 7637 105829 16720982 HASH JOIN 6386 105829 16720982 HASH JOIN 6183 93524 13654504 HASH JOIN 1471 14218 696682 VIEW CDW_ODS 1121 69806 2233792 COUNT HASH JOIN 1121 69806 3699718 TABLE ACCESSCDW_ODS ACCOUNTS_DET 1085 69806 1884762 SORT 1 10 TABLE ACCECDW_ODS ACCOUNTS_DET 1085 89750 897500 TABLE ACCESSCDW_ODS PRODUCT_TYPES_SYNONY 2 164 4264 VIEW SYS VW_NSO_1 306 14218 241706 HASH JOIN 306 14218 625592 TABLE ACCESS CDW_ODS CUBE_REPLN_REFERENCE 2 2 38 TABLE ACCESS CDW_ODS REPORT_LN_DIMENSION 303 104034 2600850 PARTITION RANGE TABLE ACCESS CDW_ODS FIN_MONTH_BALANCE 4452 459175 44539975 INDEX CDW_ODS INDEX1 20 5920 71040------------------------------------------------------------------------------------------Adding “ROWNUM” in inline views materializes the same improving the performance and IO many folds.
No comments:
Post a Comment