CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Michael Summons
Michael Summons

Reputation: 1

Oracle Query is timing out

I'm trying to write an Oracle query to join data from 4 different tables. The code is below:

SELECT 
PROJ.PRJ_NO, PROJ.PRJ_NAME, PROJ.PRJ_BEG_DATE, PROJ.PRJ_END_DATE, PORT.TIER1_NAME, PORT.TIER2_NAME, PORT.TIER3_NAME, MAX(A.FIS_WK_END_DATE) AS "FISCAL_WEEK", SUM(A.ABDOL) AS "AAB_DOL", SUM(A.VHDOL) AS "AVH_DOL", SUM(A.ADOL) AS "AA_DOL", SUM(A.DCDOL) AS "ADC_DOL", SUM(A.DCGADOL) AS "ADC_GA_DOL", SUM(A.COM) AS "AM_DOL", SUM(A.FE) AS "AFE_DOL", SUM(A.IE) AS "AIE_DOL", SUM(A.OTHER) AS "AR_DOL", SUM(A.MTSFT) AS "AS_FT", SUM(A.MTSST) AS "AS_ST", SUM(A.ACTST) AS "AL_ST", SUM(A.ACTFT) AS "ALL_FT", MAX(P.SNAPSHOT_DATE) as "SNAP_DATE", P.FINSCN_TYPE, SUM(P.ABDOL) AS "PAB_DOL", SUM(P.VHDOL) AS "PVH_DOL", SUM(P.DCDOL) AS "PDC_DOL", SUM(P.TCI_DOL) AS "PCI_GA_DOL", SUM(P.GADOL) AS "PN_GA_DOL", SUM(P.COM) AS "PN_COM", SUM(P.FEE) AS "PN_FEE", SUM(P.D_IE) AS "PN_MOIE", SUM(P.OTHER) AS "PN_OTHER"

FROM PROJ_TASK_VW PROJ
LEFT JOIN PORTFOLIO_VW PORT
ON PROJ.TASKNO = PORT.TASKNO
LEFT JOIN ACTUAL_VW A
ON PROJ.TASKNO = A.CURR_TASKNO
LEFT JOIN BUDG_DOLL_VW P
ON PROJ.TASKNO = P.CURR_TASKNO

WHERE TO_CHAR(PROJ.PRJ_END_DATE, 'YYYY-MM-DD') > '2018-10-01'
AND PROJ.P_FLAG = 'N'
AND (PROJ.P_TYPE LIKE 'D-%' OR PROJ.P_TYPE LIKE '%MR%' OR PROJ.P_TYPE       LIKE '%ID%')
AND (SUBSTR(PROJ.PRJ_NO,3,3) != 'BP' AND SUBSTR(PROJ.PRJ_NO,3,3) != 'PJ')
AND (P.FINSCN_TYPE = 'SR' OR P.FINSCN_TYPE = 'BUG')
AND (A.ABDOL + A.VHDOL + A.ADOL + A.DCDOL + A.DCGADOL + A.COM + 
A.FE + A.IE + A.OTHER) <> 0

GROUP BY
PROJ.PRJ_NO,
PROJ.PRJ_NAME,
PROJ.PRJ_BEG_DATE,
PROJ.PRJ_END_DATE,
PORT.TIER1_NAME,
PORT.TIER2_NAME,
PORT.TIER3_NAME,
P.FINSCN_TYPE

My overall intent is to bring all of the select fields into a single table using left joins (using table "PROJ" as the parent table and the remaining tables providing child data based on the data returned from the "PROJ" table. When the query is ran it times out after about 30mins. Is there a better way to write this query to where I can build the table I need without timing out???

Upvotes: 0

Views: 60

Answers (1)

eaolson
eaolson

Reputation: 15094

First, there's no way to answer this question without an execution plan. What columns do you have indexed? But here are some things I noticed.

WHERE TO_CHAR(PROJ.PRJ_END_DATE, 'YYYY-MM-DD') > '2018-10-01'

Your column is a date, so you should be comparing to a date, rather than converting to a VARCHAR2 and doing an inequality on strings.

AND (PROJ.P_TYPE LIKE 'D-%' OR PROJ.P_TYPE LIKE '%MR%' OR PROJ.P_TYPE LIKE '%ID%')

I'm not sure, but these will likely not be very performant because of the wildcards. Indexes might make these better, but I never remember how wildcard searches work with indexes.

AND (SUBSTR(PROJ.PRJ_NO,3,3) != 'BP' AND SUBSTR(PROJ.PRJ_NO,3,3) != 'PJ')

These do nothing since your two SUBSTRs return strings of 3 characters long and you are comparing them to 2 character long strings.

AND (A.ABDOL + A.VHDOL + A.ADOL + A.DCDOL + A.DCGADOL + A.COM + A.FE + A.IE + A.OTHER) <> 0

Do you actually care about the sum here, or are you just checking that one or more of these values is non-zero. If these values are always > 0, then you're better off replacing this with:

AND ( a.ABDOL > 0 OR A.VHDOL > 0 ...

Upvotes: 1

Related Questions