Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Performance Problem
SQL Query Performance Problem [message #189934] Mon, 28 August 2006 11:43 Go to next message
satgur
Messages: 2
Registered: August 2006
Junior Member
Hi I have tp speed up this query. Please help. right now its taking more than 30 min.

SELECT
Q.CNTCT_ID
,Q.INSTNC_ID
,Q.CMPGN_RUN_DT
,Q.CMPGN_RUN_TM
,Q.CMPGN_RUN_DATE
,Q.TRKING_ID
,Q. USER_ID
,Q.ITEM_ID
,Q.CONV_TYPE_ID
,Q. ACTIVITY_DATE
,Q.AUCT_END_DT
,Q.CSTMZD_LINK_TRKING_DURTN
,LSTG.LEAF_CATEG_ID
,Q.SITE_ID
,Q.TRTMNT_TYPE_CODE
FROM
(
SELECT
CC.CNTCT_ID CNTCT_ID
,CC.INSTNC_ID INSTNC_ID
,CC.CMPGN_RUN_DT CMPGN_RUN_DT
,CC.CMPGN_RUN_TM CMPGN_RUN_TM
,CC.CMPGN_RUN_DATE CMPGN_RUN_DATE
,CC.CMPGN_SENT_DATE
,CC.TRKING_END_DATE
,CC.TRKING_ID TRKING_ID
,CC.USER_ID USER_ID
,WTCH.ITEM_ID ITEM_ID
,3 CONV_TYPE_ID
,WTCH.SRC_CRE_DATE ACTIVITY_DATE
,WTCH.AUCT_END_DT AUCT_END_DT
,CC.CSTMZD_LINK_TRKING_DURTN
CSTMZD_LINK_TRKING_DURTN
,WTCH.SITE_ID SITE_ID
,CC.TRTMNT_TYPE_CODE TRTMNT_TYPE_CODE

FROM SUMM_W CC
JOIN WTCH_TRK WTCH
ON CC.USER_ID = WTCH.WTCHR_ID
QUALIFY WTCH.SRC_CRE_DATE = MIN(WTCH.SRC_CRE_DATE)
OVER(PARTITION BY WTCH.ITEM_ID,WTCH.WTCHR_ID)
WHERE WTCH.SRC_CRE_DATE BETWEEN CC.CMPGN_SENT_DATE AND
CC.TRKING_END_DATE
) Q
,LSTG_ITEM LSTG
,MODEL_SA_MAP CFG
WHERE LSTG.ITEM_ID = Q.ITEM_ID
AND LSTG.AUCT_END_DT = Q.AUCT_END_DT
AND CFG.AUCT_TYPE_CODE = LSTG.AUCT_TYPE_CODE
AND CFG.INCLD_YN_ID = 1
AND CFG.MODEL_SA_CODE = 'CMC'
AND LSTG.AUCT_END_DT >= '${min_cmpgn_sent_dt}' ;
Re: SQL Query Performance Problem [message #189941 is a reply to message #189934] Mon, 28 August 2006 12:09 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Since I have the same exact tables, with the same xact indexes and the same exact data as you, this was an easy one.
What does your explain plan and tkprof tell you about your query?

[Updated on: Mon, 28 August 2006 12:12]

Report message to a moderator

Previous Topic: Which method uses less server resources, 'MINUS' or 'NOT IN'?
Next Topic: ORA-06532: Subscript outside of limit
Goto Forum:
  


Current Time: Thu Dec 08 04:09:01 CST 2016

Total time taken to generate the page: 0.14276 seconds