How to re-write the query without using DISTINCT function in Oracle 12c

Question

Without using DISTINCT function , how to achieve the same result.
TAB_C = 2947109424 rows, act = 43460 rows

    SELECT tc.email_addr_id 
                                 , COUNT(DISTINCT tc.m_act_id) AS num_12mons 
                                 , COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')  
                                     > (ROUND(sysdate, 'DD') - 90) 
                                     THEN tc.m_act_id ELSE NULL END) AS num_3mons 
                                  , COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')  
                                      > (ROUND(sysdate, 'DD') - 180) 
                                      THEN tc.m_act_id ELSE NULL END) AS num_6mons 
                                  , COUNT(DISTINCT CASE WHEN ROUND(tc.outbound_date, 'DD')  
                                      > (ROUND(sysdate, 'DD') - 270) 
                                      THEN tc.m_act_id ELSE NULL END) AS num_9mons 
                             FROM Tab_C tc     
                             INNER JOIN act a 
                             ON tc.act_id = a.act_id 
                             where a.channel_code IN ('FM','RM') 
                                 AND ROUND(tc.outbound_date, 'DD') > (ROUND(sysdate, 'DD') - 365) 
                             GROUP BY tc.email_addr_id

Answer 1

NULLIF is supposed to be a lot quicker than CASE statements, and you might find that it's the CASE that's causing the slow rather than the distinct ... but it's all due to your indexing. NULLIF obviously only works based on finding the correct value, rather than excluding the "wrong" values, so you have to reverse the search.

this might work better, but you'll need to double-check the results.

select email_addr_id,
    count(m_act_id) AS num_12mons,
    COUNT(nullif(sign(outbound_date +90 - ROUND(sysdate, 'DD')), -1))) AS num_3mons,
    COUNT(nullif(sign(outbound_date +180 - ROUND(sysdate, 'DD')), -1))) AS num_6mons,
    COUNT(nullif(sign(outbound_date +270 - ROUND(sysdate, 'DD')), -1))) AS num_9mons
from (
select distinct tc.email_addr_id, tc.m_act_id, round(tc.outbound_date, 'DD') as outbound_date
  from Tab_C tc     
  INNER JOIN act a ON tc.act_id = a.act_id 
  where a.channel_code IN ('FM','RM') 
    AND ROUND(tc.outbound_date, 'DD') > (ROUND(sysdate, 'DD') - 365))
group by email_addr_id;