Home » RDBMS Server » Performance Tuning » Encountering issues with join condition in stored procedure (Oracle 11.2)
Encountering issues with join condition in stored procedure [message #670288] Fri, 22 June 2018 23:45 Go to previous message
senmng
Messages: 22
Registered: April 2018
Junior Member
Hi - Here am trying to insert a target table using this stored procedure but am getting incorrect values for two columns-

st_cnt.COUNT_FILES,st_cnt.COUNT_SCANS
where we are getting values for these columns from the join st_cnt where the query results are grouped by PROJ,SOL,SCAN_YEAR,SCAN_MONTH columns.
When i tried to run as a individual query(from st_cnt) ,we are getting the expected output but while running as whole we are getting huge row count.
INSERT INTO TGT_TABLE

SELECT table1.DA_SEC,

table1.DA_REG,

table1.PROJ,

table1.SOL,

table1.SCAN_YEAR_MON,

to_char(TO_DATE(table1.SCAN_YEAR_MON,'YYYY-MM'),'YYYY'),

to_char(TO_DATE(table1.SCAN_YEAR_MON,'YYYY-MM'),'MM'),

COUNT(DISTINCT table1.ISSUE_ID),

COUNT(DISTINCT table1.USERNAME),

COUNT(DISTINCT table1.PATH),

st_cnt.COUNT_FILES,

st_cnt.COUNT_SCANS,

sum(unq_Pro.HIGH_CNT),

sum(unq_Pro.MEDIUM_CNT),

sum(unq_Pro.LOW_CNT),

sum(SUPPR_HIGH) - sum(UNSUPPR_HIGH),

sum(SUPPR_MEDIUM) - sum(UNSUPPR_MEDIUM),

sum(SUPPR_LOW) - sum(UNSUPPR_LOW),

CURRENT_DATE

FROM table1

JOIN

(SELECT PROJ,SOL,

SUM(CASE WHEN ISSUE_IMP = 'high' THEN 1 ELSE 0 END) SUPPR_HIGH,

SUM(CASE WHEN ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) SUPPR_MEDIUM,

SUM(CASE WHEN ISSUE_IMP = 'low' THEN 1 ELSE 0 END) SUPPR_LOW

from table1

LEFT OUTER JOIN

SUPPR_ISSUE@DATABASE_LINK1

ON table1.ISSUE_ID = SUPPR_ISSUE.ISSUE_ID

GROUP BY PROJ,SOL

) SUPPR

ON table1.PROJ = SUPPR.PROJ

JOIN

(SELECT PROJ,SOL,

SUM(CASE WHEN ISSUE_IMP = 'high' THEN 1 ELSE 0 END) UNSUPPR_HIGH,

SUM(CASE WHEN ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) UNSUPPR_MEDIUM,

SUM(CASE WHEN ISSUE_IMP = 'low' THEN 1 ELSE 0 END) UNSUPPR_LOW

FROM table1

LEFT OUTER JOIN

UNSUPPR_ISSUE@DATABASE_LINK1

ON table1.ISSUE_ID = UNSUPPR_ISSUE.ISSUE_ID

GROUP BY PROJ,SOL

) UNSUPPR

ON table1.PROJ = UNSUPPR.PROJ

) t1

JOIN

(SELECT PROJ,SOL,SCAN_YEAR,SCAN_MONTH,COUNT(DISTINCT PATH) COUNT_FILES,COUNT(DISTINCT SCAN_ID) COUNT_SCANS

FROM table2

GROUP BY PROJ,SOL,SCAN_YEAR,SCAN_MONTH

) st_cnt

ON st_cnt.PROJ = table1.PROJ --AND st_cnt.SOL = table1.SOL

(SELECT

unq_iss.PROJ,

unq_iss.SOL,

unq_iss.SCAN_YEAR_MON,

sum(CASE WHEN unq_iss.ISSUE_IMP = 'high' THEN 1 ELSE 0 END) HIGH_CNT,

sum(CASE WHEN unq_iss.ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) MEDIUM_CNT,

sum(CASE WHEN unq_iss.ISSUE_IMP = 'low' THEN 1 ELSE 0 END) LOW_CNT

FROM (SELECT DISTINCT

table1.ISSUE_ID,

table1.ISSUE_IMP,

table1.PROJ,

table1.SOL,

table1.SCAN_YEAR_MON

FROM table1

) unq_iss

GROUP BY unq_iss.PROJ, unq_iss.SOL, unq_iss.SCAN_YEAR_MON

) unq_Pro

ON unq_Pro.PROJ = t1.PROJ

AND unq_Pro.SCAN_YEAR_MON = t1.SCAN_YEAR_MON

GROUP BY table1.DA_SEC,table1.DA_REG,table1.PROJ,table1.SOL,table1.SCAN_YEAR_MON, st_cnt.COUNT_FILES,st_cnt.COUNT_SCAN;
 
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: join is slow
Next Topic: Oracle Text index column when searching multiple tables
Goto Forum:
  


Current Time: Tue Jan 28 18:07:42 CST 2020