Select with UNION takes for ever to run

1

I have the below SQL query which takes very long to run, but runs fast in TEST (Same data as PROD).
Our DBA added some index into the select statement to enhance performance, but still takes very long to finish.
So I am hoping someone would point out best practice that enhance performance.

` SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */  ALACTIVITY.GUSTID as USER_ID,   'GUST' as RESPONSIBILITY_,  to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT
        FROM SKUSERALACTIVITY ALACTIVITY
        WHERE ALACTIVITY.CITYID NOT IN (4,9,16,31,36) AND (ALACTIVITY.EMPID=0)  AND (ALACTIVITY.SDATE >= to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
        Group by GUSTID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')
        UNION 
         SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */  ALACTIVITY.ACTORID as USER_ID,   'ACTOR' as RESPONSIBILITY_,  to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT
         FROM SKUSERALACTIVITY ALACTIVITY 
         WHERE  ALACTIVITY.ACTIVITYTYPEID IN (1,4,6) AND  (ALACTIVITY.EMPID=0)  AND (ALACTIVITY.SDATE < to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
         Group by ACTORID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')
         UNION 
         SELECT /*+USE_NL(ALACTIVITY) INDEX(ALACTIVITY) */  ALACTIVITY.ACTORID as USER_ID,   'MANAGER' as RESPONSIBILITY_,  to_char (ALACTIVITY.SDATE, 'MM-YYYY') as MONTH, count (ALACTIVITY.SUBJECT) as COUNT 
         FROM  SKUSERALACTIVITY ALACTIVITY 
         WHERE ALACTIVITY.ACTIVITYTYPEID IN (2,3) AND (ALACTIVITY.EMPID=0)  AND (ALACTIVITY.SDATE < to_date('2015-01-01','yyyy-mm-dd')) AND (ALACTIVITY.SDATE < to_date('2015-04-01','yyyy-mm-dd'))
         Group by ACTORID, to_char (ALACTIVITY.SDATE, 'MM-YYYY')
2

This is a Microsoft SQL site, so you might not find folk here that know enough about Oracle (you might want to try an Oracle forum).

My first thought is to ask if you need UNION (i.e. which will remove any duplicates) or, if there will be no duplicates, whether you could use UNION ALL instead - which will avoid the Sort and De-Dupe stages.

If that's not the issue then it will require optimisation of the query and I, personally, have no idea how to do that in Oracle - I could tell you how to do it in MS SQL though!!!

3

Thank you Kristen.
I will post this in Oracle forum.