Forum Stats

  • 3,769,604 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Categories

How to optimize performance of this SQL query

Gayathri Venugopal
Gayathri Venugopal Member Posts: 108
edited Jul 6, 2015 5:18AM in SQL & PL/SQL

Hi,

I need to find the age for each day ,but I need it for all previous dates in one query. So I used the following query:

select trunc(sysdate) - level + 1 DATE

,trunc(sysdate) - level + 1 - created_date AGE 

  from items

connect by trunc(sysdate) - level + 1 - created_date > 0

I am getting output(FOR DATE & AGE) which is fine and correct:

DATE              AGE

   --------- ----------

   6-JUL-15          22

   5-JUL-15          21

   4-JUL-15          20

   3-JUL-15          19

   2-JUL-15          18

   1-JUL-15          17

   30-JUN-15         16

   29-JUN-15         15

   28-JUN-15         14

   27-JUN-15         13

   26-JUN-15         12

   25-JUN-15         11

   24-JUN-15         10   

Now I need to calculate average age for each day so I added average in the following query:

select trunc(sysdate) - level + 1 DATE ,

   avg(trunc(sysdate) - level + 1 - created_date ) AVERAGE_AGE  

   from items

   connect by trunc(sysdate) - level + 1 - created_date > 0

   group by trunc(sysdate) - level + 1


is this query correct? When I add aggregate function (avg) to this query, it takes 1 hour to retrieve data .When I remove the average function from query it gives result in 2 seconds?What is the possible solution to calculate average without affecting the performance ?Please help

Tagged:
invalidsearch
· Share on TwitterShare on Facebook

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Jul 6, 2015 5:18AM Accepted Answer

    Perhaps you're looking for something like this...

    SQL> ed
    Wrote file afiedt.buf
  1  with t(item, created_date) as (
      2         select 1, date '2015-06-24' from dual union all
      3         select 2, date '2015-06-29' from dual union all
      4         select 3, date '2015-06-17' from dual
      5        )
      6  --
      7  -- end of test data
      8  --
      9  select item
     10        ,trunc(sysdate)-level+1 as dt
     11        ,trunc(sysdate)-level+1-created_date as age
     12        ,round(avg(trunc(sysdate)-level+1-created_date) over (partition by trunc(sysdate)-level+1),2) as avg_in_day
     13  from   t
     14  connect by level <= trunc(sysdate)-created_date+1
     15         and item = prior item
     16         and prior sys_guid() is not null
     17* order by 1,2
    SQL> /
          ITEM DT                 AGE AVG_IN_DAY
    ---------- ----------- ---------- ----------
             1 24-JUN-2015          0        3.5
             1 25-JUN-2015          1        4.5
             1 26-JUN-2015          2        5.5
             1 27-JUN-2015          3        6.5
             1 28-JUN-2015          4        7.5
             1 29-JUN-2015          5       5.67
             1 30-JUN-2015          6       6.67
             1 01-JUL-2015          7       7.67
             1 02-JUL-2015          8       8.67
             1 03-JUL-2015          9       9.67
             1 04-JUL-2015         10      10.67
             1 05-JUL-2015         11      11.67
             1 06-JUL-2015         12      12.67
             2 29-JUN-2015          0       5.67
             2 30-JUN-2015          1       6.67
             2 01-JUL-2015          2       7.67
             2 02-JUL-2015          3       8.67
             2 03-JUL-2015          4       9.67
             2 04-JUL-2015          5      10.67
             2 05-JUL-2015          6      11.67
             2 06-JUL-2015          7      12.67
             3 17-JUN-2015          0          0
             3 18-JUN-2015          1          1
             3 19-JUN-2015          2          2
             3 20-JUN-2015          3          3
             3 21-JUN-2015          4          4
             3 22-JUN-2015          5          5
             3 23-JUN-2015          6          6
             3 24-JUN-2015          7        3.5
             3 25-JUN-2015          8        4.5
             3 26-JUN-2015          9        5.5
             3 27-JUN-2015         10        6.5
             3 28-JUN-2015         11        7.5
             3 29-JUN-2015         12       5.67
             3 30-JUN-2015         13       6.67
             3 01-JUL-2015         14       7.67
             3 02-JUL-2015         15       8.67
             3 03-JUL-2015         16       9.67
             3 04-JUL-2015         17      10.67
             3 05-JUL-2015         18      11.67
             3 06-JUL-2015         19      12.67

41 rows selected.
    invalidsearch
    · Share on TwitterShare on Facebook

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Jul 6, 2015 5:11AM

    Please read the FAQ:

    And also:

    It would be most helpful if you could post more information, as well as some example data for us to reproduce the query results.

    · Share on TwitterShare on Facebook
  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Jul 6, 2015 5:11AM

    What do you mean by "average age for each day"?

    Give us some example data from items, and the results you're expecting to see from it.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Jul 6, 2015 5:18AM Accepted Answer

    Perhaps you're looking for something like this...

    SQL> ed
    Wrote file afiedt.buf
  1  with t(item, created_date) as (
      2         select 1, date '2015-06-24' from dual union all
      3         select 2, date '2015-06-29' from dual union all
      4         select 3, date '2015-06-17' from dual
      5        )
      6  --
      7  -- end of test data
      8  --
      9  select item
     10        ,trunc(sysdate)-level+1 as dt
     11        ,trunc(sysdate)-level+1-created_date as age
     12        ,round(avg(trunc(sysdate)-level+1-created_date) over (partition by trunc(sysdate)-level+1),2) as avg_in_day
     13  from   t
     14  connect by level <= trunc(sysdate)-created_date+1
     15         and item = prior item
     16         and prior sys_guid() is not null
     17* order by 1,2
    SQL> /
          ITEM DT                 AGE AVG_IN_DAY
    ---------- ----------- ---------- ----------
             1 24-JUN-2015          0        3.5
             1 25-JUN-2015          1        4.5
             1 26-JUN-2015          2        5.5
             1 27-JUN-2015          3        6.5
             1 28-JUN-2015          4        7.5
             1 29-JUN-2015          5       5.67
             1 30-JUN-2015          6       6.67
             1 01-JUL-2015          7       7.67
             1 02-JUL-2015          8       8.67
             1 03-JUL-2015          9       9.67
             1 04-JUL-2015         10      10.67
             1 05-JUL-2015         11      11.67
             1 06-JUL-2015         12      12.67
             2 29-JUN-2015          0       5.67
             2 30-JUN-2015          1       6.67
             2 01-JUL-2015          2       7.67
             2 02-JUL-2015          3       8.67
             2 03-JUL-2015          4       9.67
             2 04-JUL-2015          5      10.67
             2 05-JUL-2015          6      11.67
             2 06-JUL-2015          7      12.67
             3 17-JUN-2015          0          0
             3 18-JUN-2015          1          1
             3 19-JUN-2015          2          2
             3 20-JUN-2015          3          3
             3 21-JUN-2015          4          4
             3 22-JUN-2015          5          5
             3 23-JUN-2015          6          6
             3 24-JUN-2015          7        3.5
             3 25-JUN-2015          8        4.5
             3 26-JUN-2015          9        5.5
             3 27-JUN-2015         10        6.5
             3 28-JUN-2015         11        7.5
             3 29-JUN-2015         12       5.67
             3 30-JUN-2015         13       6.67
             3 01-JUL-2015         14       7.67
             3 02-JUL-2015         15       8.67
             3 03-JUL-2015         16       9.67
             3 04-JUL-2015         17      10.67
             3 05-JUL-2015         18      11.67
             3 06-JUL-2015         19      12.67

41 rows selected.
    invalidsearch
    · Share on TwitterShare on Facebook
This discussion has been closed.