GROUP BY with SUM takes too long

[Kincaco]

In MS Access, I can run this SQL in about 9 minutes.&nbsp;&nbsp;I am trying to convert it to an Oracle view so that users can link to the results instead of having to export it from Access and import it into other applications that don't support SQL.&nbsp;&nbsp;I am having difficulty with performance issues without any GROUP BY.&nbsp;&nbsp;With the GROUP BY I run out of space in TEMP.&nbsp;&nbsp;Any help would be appreciated.<br><br>MS Access SQL:<br>SELECT DISTINCTROW <br>CSIOWNER_PLGR.ORG_ID, <br>CSIOWNER_PLGR.PROJ_ELEM_ID, <br>CSIOWNER_CEPM.COST_ELEM_DESC, <br>CSIOWNER_PLGR.COST_ELEM, <br>CSIOWNER_PLGR.PRD, <br>Sum(IIf([SUM_UNIT]=&quot;C&quot;,[PRD_ACT],0)) AS PRDACT, <br>CSIOWNER_PLGR.SUM_UNIT, <br>Sum(IIf([SUM_UNIT]=&quot;R&quot;,[PRD_ACT],0)) AS PRD_HRS<br>FROM CSIOWNER_PLGR INNER JOIN CSIOWNER_CEPM <br>ON (CSIOWNER_PLGR.COST_ELEM = CSIOWNER_CEPM.COST_ELEM) <br>AND (CSIOWNER_PLGR.ORG_ID = CSIOWNER_CEPM.ORG_ID)<br>GROUP BY CSIOWNER_PLGR.ORG_ID, <br>CSIOWNER_PLGR.PROJ_ELEM_ID, <br>CSIOWNER_CEPM.COST_ELEM_DESC, <br>CSIOWNER_PLGR.COST_ELEM, <br>CSIOWNER_PLGR.PRD, <br>CSIOWNER_PLGR.SUM_UNIT<br>HAVING (((CSIOWNER_PLGR.ORG_ID) Like [FORMS]![FLAUNCHER]![CO]) <br>AND ((CSIOWNER_PLGR.COST_ELEM)&lt;&gt;&quot;L738&quot)<br>ORDER BY CSIOWNER_PLGR.ORG_ID, <br>CSIOWNER_PLGR.PROJ_ELEM_ID, <br>CSIOWNER_PLGR.COST_ELEM;<br><br>The Oracle SQL runs out of TEMP space:<br>SELECT DISTINCT <br>PLGR.ORG_ID, <br>PROJ_ELEM_ID, <br>PLGR.COST_ELEM, <br>PRD, <br>SUM_UNIT,<br>CEPM.COST_ELEM_DESC,<br>SUM(DECODE(SUM_UNIT,'C',PRD_ACT,0)) AS PRDACT,<br>SUM(DECODE(SUM_UNIT,'R',PRD_ACT,0)) AS PRD_HRS<br>FROM PLGR, CEPM<br>GROUP BY PLGR.ORG_ID, <br>PLGR.PROJ_ELEM_ID, <br>CEPM.COST_ELEM_DESC, <br>PLGR.COST_ELEM, <br>PLGR.PRD, <br>PLGR.SUM_UNIT, <br>CEPM.ORG_ID, <br>CEPM.COST_ELEM<br>HAVING CEPM.COST_ELEM = PLGR.COST_ELEM <br>AND CEPM.ORG_ID = PLGR.ORG_ID<br>AND PRD BETWEEN 9901 AND 9912;<br><br>It doesn't matter if I use a date range or not (9901 to 9912).&nbsp;&nbsp;Any help would be appreciated.<br><br>Thanks,<br>Connie<br><br>

 

[MalcolmW]

Your join (or lack of it) is the problem - putting it in the WHERE clause rather than the HAVING clause is better, ideally it should be in the FROM clause<br>Your HAVING clause doesn't get evaluated until after all the summaries are calculated.&nbsp;&nbsp;Your temp file is filled up with the number of rows in PLGR times the number in CEPM.&nbsp;&nbsp;Then all the rows that don't meet the HAVING criteria are discarded.&nbsp;&nbsp;<br>...better...<br>FROM PLGR, CEPM<br>WHERE CEPM.COST_ELEM = PLGR.COST_ELEM<br>AND CEPM.ORG_ID = PLGR.ORG_ID<br>AND PRD BETWEEN 9901 AND 9912;<br>...<br>or<br>...best...<br>FROM PLGR INNER JOIN <br>CEPM ON CEPM.COST_ELEM = PLGR.COST_ELEM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND CEPM.ORG_ID = PLGR.ORG_ID<br>WHERE PRD BETWEEN 9901 AND 9912;<br>... <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>