Home » RDBMS Server » Performance Tuning » Please help me to improve the performance of this query. (Oracle 11G)
Please help me to improve the performance of this query. [message #589534] |
Mon, 08 July 2013 06:37 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi All,
This is my main query as of now it's not returning any rows.
It's taking 1 minute to display the result for no rows.
The cost of the query is 617483.
In this query V_QUALITY_CONTROL_REPORT_DATA is a view.
SELECT FAX_ID,
FAX_OFFER_ID,
HOLD_TYPE,
ACTION_OWNER,
ORDER_NUMBER,
OMEGA_ORDER_NUMBER,
COUNTRY_NAME,
CUSTOMER_PREFIX,
DEFAULT_COUNTRY_CODE AS COUNTRY_CODE,
PO_NUMBER,
OFFER_NUMBER,
REVENUE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
SALESPERSON_NAME,
SALESPERSON_EMAIL,
PAYMENT_TYPE,
AGED_DAYS,
RESULT_OF_ORDER_DATA_CALL,
ORDER_SEGMENT,
ORDER_CREATOR,
ORDER_CREATOR_EMAIL,
CUSTOMER_BASE,
OMEGA_CUSTOMER_NUMBER,
CUSTOMER_BILL,
LAST_TRANSITION_DATE,
LAST_TRANSITION_CHANGED_BY,
LAST_TRANSITION_WORKGROUP_FROM,
LAST_TRANSITION_WORKGROUP_TO,
LAST_TRANSITION_REASON,
LAST_TRANSITION_COMMENT,
TOTAL_SELLING_PRICE,
CURRENCY_CODE
FROM ORL.V_QUALITY_CONTROL_REPORT_DATA
WHERE AGED_DAYS < 8;
--V_QUALITY_CONTROL_REPORT_DATA
The below query is the definition of the view.
This query is returning 2162761 records.
The cost of the query is 809487.
In this query FAX_LAST_TRANSITION_V is a view.
SELECT FH.ID AS FAX_ID,
FOD.ID AS FAX_OFFER_ID,
'ORL Rejection' AS HOLD_TYPE,
'Sales' AS ACTION_OWNER,
FOD.ORDER_NUMBER,
FOD.OMEGA_ORDER_NUMBER,
BU_MAPPING.COUNTRY_NAME,
BU_MAPPING.CUSTOMER_PREFIX,
BU_MAPPING.DEFAULT_COUNTRY_CODE,
FOD.PURCHASE_ORDER_NUMBER AS PO_NUMBER,
FOD.OFFER_NUMBER,
FOD.VALUE AS REVENUE,
FOD.CREATED_BY,
FOD.CREATION_DATE,
FH.CUSTOMER_NAME,
FH.CUSTOMER_NUMBER,
SPV.salesperson_name,
SPV.email AS SALESPERSON_EMAIL,
FH.PAYMENT_TYPE,
ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_DATA (BU_MAPPING.BU_ID,
FOD.ORDER_NUMBER)
AS RESULT_OF_ORDER_DATA_CALL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_SEGMENT ()
AS ORDER_SEGMENT,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR ()
AS ORDER_CREATOR,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR_EMAIL ()
AS ORDER_CREATOR_EMAIL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BASE ()
AS CUSTOMER_BASE,
REPLACE (
ORL.ORL_QUALITY_CONTROL_HELPER.GET_OMEGA_CUSTOMER_NUMBER (),
BU_MAPPING.CUSTOMER_PREFIX
)
AS OMEGA_CUSTOMER_NUMBER,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BILL ()
AS CUSTOMER_BILL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_TOTAL_SELLING_PRICE ()
AS TOTAL_SELLING_PRICE,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CURRENCY_CODE ()
AS CURRENCY_CODE,
LTH.CHANGED_DATE AS LAST_TRANSITION_DATE,
LTH.CHANGED_BY AS LAST_TRANSITION_CHANGED_BY,
LTH.FROM_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_FROM,
LTH.TO_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_TO,
LTH.REASON_DESCRIPTION AS LAST_TRANSITION_REASON,
LTH.COMMENTS AS LAST_TRANSITION_COMMENT
FROM ORL.FAX_OFFER_DETAIL FOD
INNER JOIN
ORL.FAX_HEADER FH
ON FH.ID = FOD.FAX_ID
INNER JOIN
ORL.FAX_SOURCE FS
INNER JOIN
APPS_GLOBAL.GLOBAL_BU_MAPPING BU_MAPPING
ON BU_MAPPING.BU_ID = FS.BUID
ON FS.ID = FH.FAX_SOURCE
LEFT OUTER JOIN
ORL.FAX_LAST_TRANSITION_V LTH
ON LTH.FAX_ID = FH.ID
LEFT OUTER JOIN
ORL.SALESPERSON_V SPV
ON SPV.salesperson_id = FH.SALES_PERSON;
--FAX_LAST_TRANSITION_V
The below query is the definition of the view.
This query is returning 2377476 records.
The cost of the query is 69614.
SELECT FH.ID AS FAX_ID,
FROMW.ID AS FROM_WORKGROUP_ID,
FROMW.NAME AS FROM_WORKGROUP_NAME,
FROMW.DESCRIPTION AS FROM_WORKGROUP_DESCRIPTION,
TOW.ID AS TO_WORKGROUP_ID,
TOW.NAME AS TO_WORKGROUP_NAME,
TOW.DESCRIPTION AS TO_WORKGROUP_DESCRIPTION,
WTR.ID AS REASON_ID,
WTR.REASON AS REASON_DESCRIPTION,
FTH.CHANGED_BY,
FTH.CHANGED_DATE,
FTH.COMMENTS,
FTH.IMPERSONATED_BY
FROM ORL.FAX_HEADER FH
INNER JOIN
ORL.FAX_TRANSITION_HISTORY FTH
ON FH.LAST_TRANSITION_ID = FTH.ID
INNER JOIN
ORL.WORKGROUP_TRANSITION_REASON WTR
ON WTR.ID = FTH.TRANSITION_REASON_ID
INNER JOIN
ORL.WORKGROUP_TRANSITION WT
ON WTR.WORKGROUP_TRANSITION_ID = WT.ID
INNER JOIN
ORL.WORKGROUP FROMW
ON WT.CURRENT_WORKGROUP_ID = FROMW.ID
INNER JOIN
ORL.WORKGROUP TOW
ON WT.NEXT_WORKGROUP_ID = TOW.ID;
Total number of records in each table and view.
SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL;--3210202
SELECT COUNT(*) FROM ORL.FAX_HEADER;--2423269
SELECT COUNT(*) FROM ORL.FAX_SOURCE;--2368
SELECT COUNT(*) FROM GLOBAL_BU_MAPPING;--9
SELECT COUNT(*) FROM ORL.FAX_LAST_TRANSITION_V;--2377476
SELECT COUNT(*) FROM ORL.SALESPERSON_V;--24639
SELECT COUNT(*) FROM ORL.FAX_TRANSITION_HISTORY--3019203
SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION_REASON--10754
SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION--6193
SELECT COUNT(*) FROM ORL.WORKGROUP--1388
SELECT COUNT(*) FROM ORL.V_QUALITY_CONTROL_REPORT_DATA--2162761
Please help how to improve the performance of this query.
Thanks in advance.
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #589921 is a reply to message #589581] |
Fri, 12 July 2013 01:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Keven ,
Thanks for your response.
I am posting the indexes on all the tables being used in this query.
Indexes on FAX_HEADER table.
CREATE BITMAP INDEX APPS_GLOBAL.BITMAPINDX_WORKGROUP_ID_RAM ON ORL.FAX_HEADER(WORKGROUP_ID)
CREATE INDEX ORL.FAX_HEADER_FS_IDX ON ORL.FAX_HEADER(FAX_SOURCE)
CREATE INDEX ORL.FAX_HEADER_IDX2 ON ORL.FAX_HEADER(UPPER("CUSTOMER_NAME"))
CREATE INDEX ORL.FAX_HEADER_IDX4 ON ORL.FAX_HEADER(DATE_RECEIVED)
CREATE INDEX ORL.FAX_HEADER_IDX5 ON ORL.FAX_HEADER(SALES_PERSON)
CREATE INDEX ORL.FAX_HEADER_IDX6 ON ORL.FAX_HEADER(CUSTOMER_OMEGA_NUMBER)
CREATE INDEX ORL.FAX_HEADER_SUBJ_IDX2 ON ORL.FAX_HEADER( REGEXP_SUBSTR (UPPER("SUBJECT"),U'(QUOTE|DEVIS)005CD{0,}005Cd{3,}',1,1,'i',1))
CREATE INDEX ORL.IDX_FAX_HEADER_LAST_TRANSITION ON ORL.FAX_HEADER(LAST_TRANSITION_ID)
CREATE BITMAP INDEX ORL.IDX_FAX_HEADER_SPECIAL_RULE ON ORL.FAX_HEADER(IS_SPECIAL_RULE_APPLIED)
CREATE INDEX ORL.IDX_FAX_HEADER_SUBJECT ON ORL.FAX_HEADER(SUBJECT)
CREATE INDEX ORL.IDX_FAX_HEADER_UPPER_SUBJECT ON ORL.FAX_HEADER(UPPER("SUBJECT"))
CREATE UNIQUE INDEX ORL.PK_FAX_HEADER ON ORL.FAX_HEADER(ID)
Indexes on FAX_OFFER_DETAIL table.
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX1 ON ORL.FAX_OFFER_DETAIL(FAX_ID)
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX2 ON ORL.FAX_OFFER_DETAIL(SOURCE_OFFER_NO)
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX3 ON ORL.FAX_OFFER_DETAIL(VERSION_NO)
CREATE INDEX ORL.IDX_OFFER_NUMBER ON ORL.FAX_OFFER_DETAIL(OFFER_NUMBER)
CREATE INDEX ORL.IDX_OMEGA_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(OMEGA_ORDER_NUMBER)
CREATE INDEX ORL.IDX_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(ORDER_NUMBER)
CREATE INDEX ORL.IDX_PURCHASE_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(PURCHASE_ORDER_NUMBER)
CREATE UNIQUE INDEX ORL.PK_FAX_OFFER_DETAIL ON ORL.FAX_OFFER_DETAIL(ID)
Indexes on FAX_SOURCE table.
CREATE INDEX APPS_GLOBAL.BITMAPINDX_WORKFLOW_ID_RAM ON ORL.FAX_SOURCE(WORKFLOW_ID)
CREATE INDEX ORL.IDX_BUID_RAM ON ORL.FAX_SOURCE(BUID)
CREATE INDEX ORL.IDX_FAX_NUMBER_RAM ON ORL.FAX_SOURCE(UPPER("FAX_NUMBER"))
CREATE UNIQUE INDEX ORL.PK_FAX_SOURCE ON ORL.FAX_SOURCE(ID)
CREATE UNIQUE INDEX ORL.UK_BU_FAX_SOURCE_NAME ON ORL.FAX_SOURCE(BUID, FAX_NUMBER)
Indexes on FAX_TRANSITION_HISTORY table.
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX1 ON ORL.FAX_TRANSITION_HISTORY(FAX_ID, ID)
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX2 ON ORL.FAX_TRANSITION_HISTORY(TRANSITION_REASON_ID)
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX3 ON ORL.FAX_TRANSITION_HISTORY(CHANGED_DATE)
CREATE UNIQUE INDEX ORL.PK_FAX_TRANSITION_HISTORY ON ORL.FAX_TRANSITION_HISTORY(ID)
Indexes on WORKGROUP_TRANSITION_REASON table.
CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(ID)
CREATE UNIQUE INDEX ORL.UK_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(WORKGROUP_TRANSITION_ID, REASON)
CREATE INDEX ORL.WORKGROUP_TRANS_REASON_IDX1 ON ORL.WORKGROUP_TRANSITION_REASON(IS_DELETED)
Indexes on WORKGROUP_TRANSITION table.
CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION ON ORL.WORKGROUP_TRANSITION(ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX1 ON ORL.WORKGROUP_TRANSITION(CURRENT_WORKGROUP_ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX2 ON ORL.WORKGROUP_TRANSITION(NEXT_WORKGROUP_ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX3 ON ORL.WORKGROUP_TRANSITION(IS_DELETED)
Indexes on WORKGROUP table.
CREATE UNIQUE INDEX ORL.PK_WORKGROUP ON ORL.WORKGROUP(ID)
CREATE INDEX ORL.WORKGROUP_IDX1 ON ORL.WORKGROUP(BUID)
CREATE INDEX ORL.WORKGROUP_IDX2 ON ORL.WORKGROUP(IS_ACTIVE)
CREATE INDEX ORL.WORKGROUP_IDX3 ON ORL.WORKGROUP(IS_DELETED)
CREATE INDEX ORL.WORKGROUP_IDX4 ON ORL.WORKGROUP(IS_EMC)
CREATE INDEX ORL.WORKGROUP_IDX5 ON ORL.WORKGROUP(IS_BACKLOG)
CREATE INDEX ORL.WORKGROUP_IDX6 ON ORL.WORKGROUP(DESCRIPTION)
I have attached the explain plan output also.
Please help me.
Thanks.
|
|
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592187 is a reply to message #592184] |
Mon, 05 August 2013 09:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
Thanks for noticing.
I have scene explain plan individually only for SALESPERSON_V view.
It's looking good.SO that I didn't post the code for this view.
Please find the below code for SALESPERSON_V view.
Please find the attched explain plan for complete query.
SELECT DISTINCT 301,
5102,
'Ireland',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_ire.gedis_salesperson sp,
apps_ire.gedis_user_group ugr,
apps_ire.gedis_user_role ur,
apps_ire.gedis_user_link ul,
apps_ire.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 302,
202,
'United Kingdom',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_uk.gedis_salesperson sp,
apps_uk.gedis_user_group ugr,
apps_uk.gedis_user_role ur,
apps_uk.gedis_user_link ul,
apps_uk.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 323,
808,
'Germany',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_de.gedis_salesperson sp,
apps_de.gedis_user_group ugr,
apps_de.gedis_user_role ur,
apps_de.gedis_user_link ul,
apps_de.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 325,
2121,
'Netherlands',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_nl.gedis_salesperson sp,
apps_nl.gedis_user_group ugr,
apps_nl.gedis_user_role ur,
apps_nl.gedis_user_link ul,
apps_nl.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 328,
909,
'France',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_fr.gedis_salesperson sp,
apps_fr.gedis_user_group ugr,
apps_fr.gedis_user_role ur,
apps_fr.gedis_user_link ul,
apps_fr.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 329,
6161,
'Italy',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_it.gedis_salesperson sp,
apps_it.gedis_user_group ugr,
apps_it.gedis_user_role ur,
apps_it.gedis_user_link ul,
apps_it.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 332,
1212,
'Sweden',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_se.gedis_salesperson sp,
apps_se.gedis_user_group ugr,
apps_se.gedis_user_role ur,
apps_se.gedis_user_link ul,
apps_se.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL)
UNION ALL
SELECT DISTINCT 365,
592,
'Slovakia',
sp.salesperson_id,
UPPER (sp.fo_logon),
ur.role_name,
ugr.group_name,
ugr.GROUP_ID,
sp.email,
ds.department_id,
sp.sales_channel
FROM apps_sk.gedis_salesperson sp,
apps_sk.gedis_user_group ugr,
apps_sk.gedis_user_role ur,
apps_sk.gedis_user_link ul,
apps_sk.gedis_responsibility gr,
orl.department_salesperson ds
WHERE sp.salesperson_id = ul.user_id
AND ul.role_id = ur.role_id
AND ul.GROUP_ID = ugr.GROUP_ID
AND sp.responsibility_id = gr.responsibility_id
AND gr.responsibility_key LIKE 'ORL%'
AND UPPER (ur.role_name) = 'ORL_MEMBER'
AND sp.salesperson_id = ds.user_id(+)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
OR ul.start_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
OR ul.end_date_active IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
OR sp.effective_start_date IS NULL)
AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
OR sp.effective_end_date IS NULL);
Please help me.
Thanks.
-
Attachment: explian.txt
(Size: 35.90KB, Downloaded 2343 times)
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592191 is a reply to message #592187] |
Mon, 05 August 2013 09:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This is a bug,
ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS
You should not attempt to convert a date to a date, and it may be supressing index usage and causing forcing the FTS of fax_headers at operation id 156.
I think you could project fh.date_received in the view, and then replace your predicate
WHERE AGED_DAYS < 8;
with
WHERE ORL.V_QUALITY_CONTROL_REPORT_DATA.DATE_RECEIVED > sysdate -8.
--
Correction: above should read
WHERE DATE_RECEIVED > sysdate -8
sorry about that, too much copy-paste
[Updated on: Mon, 05 August 2013 09:54] Report message to a moderator
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592235 is a reply to message #592193] |
Mon, 05 August 2013 22:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
Thank you so much for your response.
The data type of the column is TIMESTAMP(6)
So that we are converting to date.
Our requirement is subtract DATE_RECEIVED from sysdate after that round the result it should be less than 8 days.
For that I have written the below logic
ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS.
As per Your suggestion WHERE DATE_RECEIVED > sysdate -8 will replace my above requirement.
In this there is no ROUND function also.
Please provide the alternative logic to above my requirement
which improves the performance of my query.
Please help me.
Your help is appreciated
Thanks.
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592246 is a reply to message #592237] |
Tue, 06 August 2013 01:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I'll try again. I think you need to re-write the predicate I pointed out in such a way that it will avoid this, 156 - filter(ROUND(SYSDATE@!-CAST(INTERNAL_FUNCTION("FH"."DATE_RECEIVED") AS DATE))<8)
which may be causing problems.
I don't say "is", only "may be".
Why don't you just try what I suggested, and see if it helps?
|
|
|
Re: Please help me to improve the performance of this query. [message #592248 is a reply to message #592246] |
Tue, 06 August 2013 01:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
Thanks for your response.
I have tried whatever you suggested.
I have tested for some scenarios it's working fine.
But in my previous logic ROUND function is used.
How that will work in your logic.
I am not able to understand.
Please explain me.
If your logic take care of ROUND that's fine.
Please help me.
Thanks.
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592266 is a reply to message #592265] |
Tue, 06 August 2013 03:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
There are still so many filter predicates with implicit data type conversion. All those operation ids which has INTERNAL_FUNCTION, are going for an implicit data type conversion. Not good in terms of performance.
Like,
filter(INTERNAL_FUNCTION("SP"."EFFECTIVE_START_DATE")<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) AND
INTERNAL_FUNCTION("SP"."EFFECTIVE_END_DATE")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
You can try to rewrite the query, such that predicate does not have to go for an implicit conversion. For example, if you convert date again into date data type, it's like inventing the wheel again, and thus implementing the implicit data type conversion.
EDIT : fixed typo errors.
[Updated on: Tue, 06 August 2013 03:21] Report message to a moderator
|
|
|
Re: Please help me to improve the performance of this query. [message #592267 is a reply to message #592265] |
Tue, 06 August 2013 03:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Well, you have followed my advice, and the predicted execution time has dropped from 01:17:17 to 00:00:02.
I think that is worth a bit more than "Thanks for your response."
As for your remaining problem with ROUND, you will need to post the new version of the code. I havee no idea if the logic is the same, or if you need to adjust it further.
|
|
|
Re: Please help me to improve the performance of this query. [message #592268 is a reply to message #592265] |
Tue, 06 August 2013 03:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
I agreed with john suggestion.
If I modified as John suggested performance got improved.
In my previous logic ROUND function is used to round the
values after doing subtraction and then comparing <8.
But my question are
will John logic works as per my above requirement?
Will it take care of ROUND scenario also?
I am not able to understand.
Please explain me.
If your logic take care of ROUND that's fine.
Please help me.
Thanks.
|
|
|
Re: Please help me to improve the performance of this query. [message #592269 is a reply to message #592268] |
Tue, 06 August 2013 03:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
I have changed the view ORL.V_QUALITY_CONTROL_REPORT_DATA as below
SELECT FH.ID AS FAX_ID,
FOD.ID AS FAX_OFFER_ID,
'ORL Rejection' AS HOLD_TYPE,
'Sales' AS ACTION_OWNER,
FOD.ORDER_NUMBER,
FOD.OMEGA_ORDER_NUMBER,
BU_MAPPING.COUNTRY_NAME,
BU_MAPPING.CUSTOMER_PREFIX,
BU_MAPPING.DEFAULT_COUNTRY_CODE,
FOD.PURCHASE_ORDER_NUMBER AS PO_NUMBER,
FOD.OFFER_NUMBER,
FOD.VALUE AS REVENUE,
FOD.CREATED_BY,
FOD.CREATION_DATE,
FH.CUSTOMER_NAME,
FH.CUSTOMER_NUMBER,
SPV.salesperson_name,
SPV.email AS SALESPERSON_EMAIL,
FH.PAYMENT_TYPE,
FH.DATE_RECEIVED,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_DATA (BU_MAPPING.BU_ID,
FOD.ORDER_NUMBER)
AS RESULT_OF_ORDER_DATA_CALL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_SEGMENT ()
AS ORDER_SEGMENT,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR ()
AS ORDER_CREATOR,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR_EMAIL ()
AS ORDER_CREATOR_EMAIL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BASE ()
AS CUSTOMER_BASE,
REPLACE (
ORL.ORL_QUALITY_CONTROL_HELPER.GET_OMEGA_CUSTOMER_NUMBER (),
BU_MAPPING.CUSTOMER_PREFIX
)
AS OMEGA_CUSTOMER_NUMBER,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BILL ()
AS CUSTOMER_BILL,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_TOTAL_SELLING_PRICE ()
AS TOTAL_SELLING_PRICE,
ORL.ORL_QUALITY_CONTROL_HELPER.GET_CURRENCY_CODE ()
AS CURRENCY_CODE,
LTH.CHANGED_DATE AS LAST_TRANSITION_DATE,
LTH.CHANGED_BY AS LAST_TRANSITION_CHANGED_BY,
LTH.FROM_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_FROM,
LTH.TO_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_TO,
LTH.REASON_DESCRIPTION AS LAST_TRANSITION_REASON,
LTH.COMMENTS AS LAST_TRANSITION_COMMENT
FROM ORL.FAX_OFFER_DETAIL FOD
INNER JOIN
ORL.FAX_HEADER FH
ON FH.ID = FOD.FAX_ID
INNER JOIN
ORL.FAX_SOURCE FS
INNER JOIN
APPS_GLOBAL.GLOBAL_BU_MAPPING BU_MAPPING
ON BU_MAPPING.BU_ID = FS.BUID
ON FS.ID = FH.FAX_SOURCE
LEFT OUTER JOIN
ORL.FAX_LAST_TRANSITION_V LTH
ON LTH.FAX_ID = FH.ID
LEFT OUTER JOIN
ORL.SALESPERSON_V SPV
ON SPV.salesperson_id = FH.SALES_PERSON;
And the new query is as below.
SELECT FAX_ID,
FAX_OFFER_ID,
HOLD_TYPE,
ACTION_OWNER,
ORDER_NUMBER,
OMEGA_ORDER_NUMBER,
COUNTRY_NAME,
CUSTOMER_PREFIX,
DEFAULT_COUNTRY_CODE AS COUNTRY_CODE,
PO_NUMBER,
OFFER_NUMBER,
REVENUE,
CREATED_BY,
CREATION_DATE,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
SALESPERSON_NAME,
SALESPERSON_EMAIL,
PAYMENT_TYPE,
DATE_RECEIVED,
RESULT_OF_ORDER_DATA_CALL,
ORDER_SEGMENT,
ORDER_CREATOR,
ORDER_CREATOR_EMAIL,
CUSTOMER_BASE,
OMEGA_CUSTOMER_NUMBER,
CUSTOMER_BILL,
LAST_TRANSITION_DATE,
LAST_TRANSITION_CHANGED_BY,
LAST_TRANSITION_WORKGROUP_FROM,
LAST_TRANSITION_WORKGROUP_TO,
LAST_TRANSITION_REASON,
LAST_TRANSITION_COMMENT,
TOTAL_SELLING_PRICE,
CURRENCY_CODE
FROM ORL.V_QUALITY_CONTROL_REPORT_DATA
WHERE DATE_RECEIVED > sysdate -8;
Please help me.
Thanks.
|
|
|
Re: Please help me to improve the performance of this query. [message #592284 is a reply to message #592269] |
Tue, 06 August 2013 05:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi,
Your logic is not working properly.
For existed logic I didn't get any records.
SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS
FROM ORL.FAX_HEADER
WHERE ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE))<8;
No records.
For your logic I got the output.
SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS,sysdate-8
FROM ORL.FAX_HEADER
WHERE DATE_RECEIVED>sysdate-8;
DATE_RECEIVED AGED_DAYS SYSDATE-8
------------------------------- ---------- ---------
29-JUL-13 11.55.42.823281 AM 8 7/29/2013 5:45:41 AM
1 row selected.
I think your logic is not working properly.
Please help me.
Thanks.
|
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592312 is a reply to message #592303] |
Tue, 06 August 2013 08:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Kevin,
Thanks for your help.
However it's not meet my requirement.
The business logic is we should consider the time also not only date.
Is there any way to implement my requirement with good performance.
And also what is the use of
change your view FAX_LAST_TRANSITION_V to include the FH.DATE_RECEIVED column as an additional column.
Please help me.
Thanks.
|
|
|
Re: Please help me to improve the performance of this query. [message #592315 is a reply to message #592312] |
Tue, 06 August 2013 08:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
First, there is an error in my post. I was using +8 and I believe it should be -8.
HOwever, please re-read my post. I believe I answerd all these questions there. You need to understand what I said.
For example
I noted that your original logic is flawed because it does not return 8 days, it returns 8.5 days. I asked you what you wanted. Do you want 8 days or 8.49999999999 days?
Regardless of what you want, the example I showed will work. You just plug in the value for days you are interested in.
The view needs to feed the original column up through the query layers so you can reference it in the expression trunc(sysdate) - 8 < trunc(cast(...)).
Your main query was this:
CURRENCY_CODE
FROM ORL.V_QUALITY_CONTROL_REPORT_DATA
WHERE AGED_DAYS < 8;
I want you to write this instead.
CURRENCY_CODE
FROM ORL.V_QUALITY_CONTROL_REPORT_DATA
WHERE TRUNC(SYSDATE) - 8 < TRUNC(CAST(DATE_RECEIVED));
You are looking for this.
09:25:06 SQL> CREATE TABLE A (A CLOB,B TIMESTAMP);
Table created.
Elapsed: 00:00:00.15
1* CREATE INDEX AI1 ON A(TRUNC(CAST(B AS DATE)))
09:25:36 SQL> /
Index created.
Elapsed: 00:00:00.09
09:27:06 SQL> EXPLain plan for select /*+ cardinality (a 1000000) */ * from a where trunc(sysdate) - 8 < trunc(cast(b as date));
Explained.
Elapsed: 00:00:00.06
09:27:42 SQL> @showplan9i
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 1921M| 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| A | 1000K| 1921M| 1 |
|* 2 | INDEX RANGE SCAN | AI1 | 9000 | | 2 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRUNC(CAST("A"."B" AS DATE))>TRUNC(SYSDATE@!)-8 AND
TRUNC(CAST("A"."B" AS DATE)) IS NOT NULL)
Note: cpu costing is off
16 rows selected.
Please re-read my prior post and try to understand what it is doing.
Kevin
[Updated on: Tue, 06 August 2013 08:32] Report message to a moderator
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592320 is a reply to message #592316] |
Tue, 06 August 2013 09:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Kevin,
Thanks for your detailed explanation.
For the below query the existed logic AGED_DAYS and your proposed logic PROPOSED_DAYS
is showing different result.If I use your logic the out will be different.
Business will not agree for this.
Is there any way to implement my existed logic in different way.
Else what we can do to improve the performance of the query.
SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS,(SYSDATE - CAST (DATE_RECEIVED AS DATE))ACTUAL_AGEDS_DAYS,
trunc(sysdate)-trunc(cast(date_received as date)) PROPOSED_DAYS
FROM ORL.FAX_HEADER
WHERE ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE))=1137
DATE_RECEIVED AGED_DAYS ACTUAL_AGEDS_DAYS PROPOSED_DAYS
------------------------------- ---------- ----------------- -------------
25-JUN-10 09.41.10.909532 PM 1137 1137.48088 1138
25-JUN-10 09.45.25.723175 PM 1137 1137.47793 1138
2 rows selected.
Please help me.
Thanks.
|
|
|
Re: Please help me to improve the performance of this query. [message #592340 is a reply to message #592320] |
Tue, 06 August 2013 11:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Glad to see you are moving forward.
but there is still something you are not telling us.
DATE_RECEIVED AGED_DAYS ACTUAL_AGEDS_DAYS PROPOSED_DAYS
------------------------------- ---------- ----------------- -------------
25-JUN-10 09.41.10.909532 PM 1137 1137.48088 1138
25-JUN-10 09.45.25.723175 PM 1137 1137.47793 1138
I am glad to see that you computed "ACUTAL_AGED_DAYS"
what however is PROPOSED_DAYS? Are you telling us that you want 1137.48088 to round up to 1138? If so do you want all fractions to round up?
Kevin
[Updated on: Tue, 06 August 2013 11:01] Report message to a moderator
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592504 is a reply to message #592498] |
Fri, 09 August 2013 01:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Kevin,
Sorry for not providing update.
Your suggestion is not working for my requirement exactly.
Can we do some thing like this.
SELECT ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS
FROM orl.FAX_HEADER FH
WHERE FH.DATE_RECEIVED>sysdate-8
AND ROUND(SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))<8;
We have index on DATE_RECEIVED column.
Will it be the correct logic for my requirement?
Please confirm.
Please provide any solution for problem.
Thanks.
|
|
|
Re: Please help me to improve the performance of this query. [message #592506 is a reply to message #592504] |
Fri, 09 August 2013 02:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Hello again - I'm confused about what issue you are (still) facing. As I understand it, I solved your performance problem by adjusting that predicate so that it could use the existing index on fh.date_received, but you couldn't make the final adjustment to get whole days. Then Kevin gave you a function based index that would make your old predicate an indexable condition.
Did Kevin's solution solve the performance problem?
And can you explain, again, what your AGED_DAYS algorithm actually is? Like this, "if the date-time right now is X, then AGED_DAYS > 8 would include Y but not Z" ?
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592510 is a reply to message #592509] |
Fri, 09 August 2013 03:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Let us assume
date_received is greater than sysdate -8
And the value of SYSDATE - CAST (FH.DATE_RECEIVED AS DATE) is 8.75
If we do ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) the value is 9
As per this logic date_received is greater than sysdate -8 that record come in the output.
But as per the existed logic ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))<8
That record should not come in the output.
This is what the last line is doing.
Please confirm my logic is correct or not compared to existed logic.
Thanks .
|
|
|
|
|
Re: Please help me to improve the performance of this query. [message #592697 is a reply to message #592523] |
Sun, 11 August 2013 04:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to previous message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Kevin,
Thanks for your explanation.
As per your comments
There are still so many filter predicates with implicit data type conversion.
All those operation ids which has INTERNAL_FUNCTION, are going for an implicit data type conversion.
Not good in terms of performance.
Like,
filter(INTERNAL_FUNCTION("SP"."EFFECTIVE_START_DATE")<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) AND
INTERNAL_FUNCTION("SP"."EFFECTIVE_END_DATE")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))
You can try to rewrite the query, such that predicate does not have to go for an implicit conversion.
For example, if you convert date again into date data type, it's like inventing the wheel again, and thus implementing the implicit data type conversion.
Can you please provide alternative for this.
To make the SQL query optimal.
Thanks.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:56:09 CDT 2024
|