Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to optimize performance of this SQL query

Gayathri VenugopalJul 6 2015 — edited Jul 6 2015

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

This post has been answered by BluShadow on Jul 6 2015
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 3 2015
Added on Jul 6 2015
3 comments
267 views