Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Luis.

Asked: November 27, 2014 - 7:09 pm UTC

Last updated: November 28, 2014 - 11:56 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Tom,

Glad to be able to post a question. Thank you. My questions is How can I improve the efficiency of the following view. This will help not only on this view but similar likes ones throughout the application. Any points will be appreciated. t

Thanks Lou

------------------


SELECT FINANCIAL.FINID,
TO_CHAR (PRIMARY_CLIENT_ID) ID,
PRIMARY_ID_TYPE ID_TYPE,
FINANCIAL.CLIENT_SEGMENT,
(CASE
WHEN FINANCIAL.CLIENT_SEGMENT = 'Individual'
THEN
DECODE (LAST_NAME, NULL, '', LAST_NAME || ', ')
|| FINANCIAL.FIRST_NAME
|| ' '
|| FINANCIAL.MIDDLE_NAME
ELSE
FINANCIAL.LEGAL_NAME
END)
LEGAL_NAME,
FINANCIAL.LAST_NAME,
FINANCIAL.FIRST_NAME,
FINANCIAL.MIDDLE_NAME,
FINANCIAL.CLIENT_TYPE,
FINANCIAL.CLIENT_DOB,
FINANCIAL.DOMICILE_CNTRY_CODE,
DC.CNTRY_NAME DOMICILE_CNTRY_NAME,
FINANCIAL.CITI_SERVICE_CNTRY_CODE,
CC.CNTRY_NAME CITI_SERVICE_CNTRY_NAME,
CC.REGION CITI_SERVICE_REGION,
FINANCIAL.NEXT_PERODIC_REVIEW_DT,
FINANCIAL.RISK_RATING,
FINANCIAL.STATUS,
FINANCIAL.EMPLOYEE_FLAG,
FINANCIAL.EXCLUSION_FLAG,
NVL ( (SELECT 'Y'
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1),
'N')
ACTIVE_WORK_ITEM_IND,
FINANCIAL.COMPONENT,
OS.BRANCH BRANCH,
OS.PRIM_OWNER_SOEID PRIMARY_OWNER,
OS.PRIM_OWNER_BUSINESS_UNIT BUSINESS_UNIT,
(SELECT TASK_ROLE
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1)
ROLE_CODE,
CC.CNTRY_DP_SCENARIO CITI_SERVICE_CNTRY_DP_SCENARIO,
FINANCIAL.LST_RENEW_TRIG_DT,
FINANCIAL.MANAGED_CNTRY_CODE,
FINANCIAL.GFPID,
FINANCIAL.PARENT_CUST_NAME,
FINANCIAL.PARENT_DOMICILECOUNTRYCODE
FROM MAINCLIENT FINANCIAL,
FINAGBL.ISO_COUNTRY_LKUP DC,
FINAGBL.ISO_COUNTRY_LKUP CC,
CORE OS
WHERE FINANCIAL.DOMICILE_CNTRY_CODE = DC.CNTRY_CODE(+)
AND FINANCIAL.PRIMARY_CLIENT_ID IS NOT NULL
AND FINANCIAL.CITI_SERVICE_CNTRY_CODE = CC.CNTRY_CODE(+)
AND OS.FINID(+) = FINANCIAL.FINID
AND OS.COMPONENT(+) = FINANCIAL.COMPONENT
AND FINANCIAL.COMPONENT = 'ACTIVE'
UNION ALL
SELECT FINANCIAL.FINID,
TO_CHAR (PRIMARY_CLIENT_ID) ID,
PRIMARY_ID_TYPE ID_TYPE,
FINANCIAL.CLIENT_SEGMENT,
(CASE
WHEN FINANCIAL.CLIENT_SEGMENT = 'Individual'
THEN
DECODE (LAST_NAME, NULL, '', LAST_NAME || ', ')
|| FINANCIAL.FIRST_NAME
|| ' '
|| FINANCIAL.MIDDLE_NAME
ELSE
FINANCIAL.LEGAL_NAME
END)
LEGAL_NAME,
FINANCIAL.LAST_NAME,
FINANCIAL.FIRST_NAME,
FINANCIAL.MIDDLE_NAME,
FINANCIAL.CLIENT_TYPE,
FINANCIAL.CLIENT_DOB,
FINANCIAL.DOMICILE_CNTRY_CODE,
DC.CNTRY_NAME DOMICILE_CNTRY_NAME,
FINANCIAL.CITI_SERVICE_CNTRY_CODE,
CC.CNTRY_NAME CITI_SERVICE_CNTRY_NAME,
CC.REGION CITI_SERVICE_REGION,
FINANCIAL.NEXT_PERODIC_REVIEW_DT,
FINANCIAL.RISK_RATING,
FINANCIAL.STATUS,
FINANCIAL.EMPLOYEE_FLAG,
FINANCIAL.EXCLUSION_FLAG,
NVL ( (SELECT 'Y'
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1),
'N')
ACTIVE_WORK_ITEM_IND,
FINANCIAL.COMPONENT,
OS.BRANCH BRANCH,
OS.PRIM_OWNER_SOEID PRIMARY_OWNER,
OS.PRIM_OWNER_BUSINESS_UNIT BUSINESS_UNIT,
(SELECT TASK_ROLE
FROM FINAGBL.GLOBAL_TASK
WHERE COMPONENT_ID = FINANCIAL.FINID
AND WORK_ITEM_STATUS IN ('Created',
'Ready',
'Reserved',
'InProgress',
'Suspended')
AND ROWNUM <= 1)
ROLE_CODE,
CC.CNTRY_DP_SCENARIO CITI_SERVICE_CNTRY_DP_SCENARIO,
FINANCIAL.LST_RENEW_TRIG_DT,
FINANCIAL.MANAGED_CNTRY_CODE,
FINANCIAL.GFPID,
FINANCIAL.PARENT_CUST_NAME,
FINANCIAL.PARENT_DOMICILECOUNTRYCODE
FROM MAINCLIENT FINANCIAL,
FINAGBL.ISO_COUNTRY_LKUP DC,
FINAGBL.ISO_COUNTRY_LKUP CC,
CORE OS
WHERE FINANCIAL.DOMICILE_CNTRY_CODE = DC.CNTRY_CODE(+)
AND FINANCIAL.PRIMARY_CLIENT_ID IS NOT NULL
AND FINANCIAL.CITI_SERVICE_CNTRY_CODE = CC.CNTRY_CODE(+)
AND OS.FINID(+) = FINANCIAL.FINID
AND OS.COMPONENT(+) = FINANCIAL.COMPONENT
AND FINANCIAL.COMPONENT = 'RUNNING'
AND NOT EXISTS
(SELECT 1
FROM MAINCLIENT ACT
WHERE ACT.FINID = FINANCIAL.FINID
AND ACT.COMPONENT = 'ACTIVE');


and Tom said...

this is not answerable.

I don't know your schema.
I don't know your constraints.
I don't know what question you are trying to even answer.

I do not look at things like this - I am not here to take a huge query and "tune it for you".

I just happened to have written about this just this month actually:

http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html



Is this answer out of date? If it is, please let us know via a Comment