3 Replies Latest reply: May 29, 2013 12:49 PM by MLBrown RSS

    How to run query incremental

    718371
      Hi i have complex select query with aggergate functions. the results of query update two tables. This query is causing very high resource comsumption .

      [select campaignid, adgroupid, advid, count(*), sum(cost) as 'cost',
                 sum(billed) as 'billed', sum(unbilled) as 'unbilled',
                 avg(avgpos) as 'avgpos'
                 from (select campaignid, adgroupid, advid, count(*), sum(cost) as 'cost',
                    sum(billed) as 'billed', sum(unbilled) as 'unbilled',
                    avg(avgpos) as 'avgpos'
                    from test1
                    group by 1,2,3
                  union all
                    select campaignid, adgroupid, advid, count(*), sum(cost) as 'cost',
                    sum(billed) as 'billed', sum(unbilled) as 'unbilled',
                    avg(avgpos) as 'avgpos'
                    from test2
                    group by 1,2,3
      ]

      what is wrong with ?
        • 1. Re: How to run query incremental
          MLBrown
          What exactly is your question? Is it related to SQL Developer?

          The SQL you attached as a few syntax prolems with it so it wouldn't run for me even if I had your tables and data. The syntax is at least fixed in this version if that is the problem:
          select campaignid,
                 adgroupid,
                 advid,
                 count,
                 cost,
                 billed,
                 unbilled,
                 avgpos
            from (select   campaignid,
                           adgroupid,
                           advid,
                           count (*) count,
                           sum (cost) cost,
                           sum (billed) billed,
                           sum (unbilled) unbilled,
                           avg (avgpos) avgpos
                      from test1
                  group by 1, 2, 3
                  union all
                  select   campaignid,
                           adgroupid,
                           advid,
                           count (*) count,
                           sum (cost) cost,
                           sum (billed) billed,
                           sum (unbilled) unbilled,
                           avg (avgpos) as avgpos
                      from test2
                  group by 1, 2, 3);
          • 2. Re: How to run query incremental
            718371
            Qery is very slow . Taking about 4 hours to complete. Table is very large from it selecting data and updating resultant data of query two tables .

            Actually I want to know how it how I can tune it. ?

            Thanks
            • 3. Re: How to run query incremental
              MLBrown
              For tuning questions, you should post to this forum instead: {forum:id=75}

              Make sure you provide the version of the database, the query, and statistics about the query: (See How to post a SQL statement tuning request: SQL and PL/SQL FAQ