1 Reply Latest reply on Feb 29, 2016 10:31 PM by rp0428

    Select with Union Performance issue

    user7188033

      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 (1,3,5) AND (ALACTIVITY.ID=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 (2,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 (12,13) 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')

        • 1. Re: Select with Union Performance issue

          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.

          And someone likely will if you post in the proper forum:

          SQL & PL/SQL

           

          Please mark the thread ANSWERED and repost it. Before you repost read the FAQ for that forum about how to post a tuning request and the info you need to provide. That info includes:

           

          1. The actual query (formatted so it can be read)

          2. The table and index DDL for ALL tables/indexes involved

          3. Row counts for the tables and query predicates

          4. The actual execution plan

          5. The full version of the DB

          6. SPECIFIC info about the problem - 'takes very long to run' has NO MEANING at all. A query of 8 billion rows is going to take a long time to run and unless you post specifics no one will have any idea how many rows are possible.