Hi.

Could someone give me some tips on how to optimize this query?

select ModuleAvailability, ModuleTerms
FROM (
select decode(nvl(sum(a.SERVICE_EVOCATIONS),1),0,null,rou nd(...math computation ...) AS ModuleAvailability,
decode(nvl(sum(a.SERVICE_TERMINATIONS),1),0,null, round(...another math computation ...) AS ModuleTerms
FROM (
select MODULE_LOGIC_KEY, TIME_KEY, SERVICE_EVOCATIONS, fieldC, fieldD, fieldE FROM COUNTER_MODULE_LOG_TIME
WHERE MODULE_LOGIC_KEY IN (
select MODULE_LOGIC_KEY FROM
MODULE_LOGIC WHERE MODULE_PLAT = 'Plat3' AND (MODULE_MODULE like 'ModuleA_%' OR MODULE_MODULE like 'Module_B_%' OR MODULE_MODULE like 'MODULE_C_%' OR MODULE_MODULE like 'MODULE_E_%' OR MODULE_MODULE like 'MODULE_G_%' OR MODULE_MODULE like 'MODULE_H_%')
)
) a
RIGHT JOIN
(
select MODULE_PLAT, MODULE_MODULE, MODULE_LOGIC_KEY
FROM
MODULE_LOGIC
WHERE MODULE_PLAT = 'Plat3' AND (MODULE_MODULE like 'ModuleA_%' OR MODULE_MODULE like 'Module_B_%' OR MODULE_MODULE like 'MODULE_C_%' OR MODULE_MODULE like 'MODULE_E_%' OR MODULE_MODULE like 'MODULE_G_%' OR MODULE_MODULE like 'MODULE_H_%')
)b
ON a.MODULE_LOGIC_KEY = b.MODULE_LOGIC_KEY
AND a.TIME_KEY >= TO_CHAR((SYSDATE - (6+10) / 1440), 'yyyymmddhh24mi') AND a.TIME_KEY <= TO_CHAR((SYSDATE - 6/1440), 'yyyymmddhh24mi')
GROUP BY SUBSTR(MODULE_MODULE,1,INSTR(MODULE_MODULE,' ')-2)
ORDER BY SUBSTR(MODULE_MODULE,1,INSTR(MODULE_MODULE,' ')-2)
);

I'm running 10.2.0.4.0 and using the RIGHT JOIN given that the b JOIN (only keeps the Modules names, ids, and other info) has far less results than a JOIN (which keeps the metrics historic of each module )

I've a Index over MODULE_PLAT, MODULE_MODULE collums and a PK over MODULE_LOGIC_KEY.

Any help would be greatly appreciated , I'll happily share more Table info if needed