« The Oracle Active Session History (ASH) - a real treasure trove | Main | dramatic differences of in memory scanning performance on range queries »

Yet another workaround for missing AVG() function for the Oracle interval type


Isn't it similar to you experience with Oracle SQL? there are a few places in the Oracle SQL implementation where you think why didn't they think it through to the end or did they only partially implement a feature?

So you are not the only one suffering from this. It does not mean Oracle SQL is inconsistent as it is indeed a mighty mature tool to handle data and structures in the Oracle database.

Coming to the point:

There are max()/min() functions defined for the interval Oracle data type but not an average avg() function. This means if you want a consistent output, same data type and precision you have to work around a bit and grab into the tool set of SQL to emulate this missing functionality.

Following SQL reports the runtime/duration of Oracle Auto-Task Jobs the last 14 days, with min()/max() and average results:

SELECT Dajh.Client_Name AS Auto_Task_Name
      ,MAX(Dajh.Job_Duration) AS Max_Job_Duration
      ,MIN(Dajh.Job_Duration) AS Min_Job_Duration
      ,CAST(Numtodsinterval(AVG(Extract(DAY FROM Dajh.Job_Duration) * 24 * 60 * 60 +
                                Extract(Hour FROM Dajh.Job_Duration) * 60 * 60 +
                                Extract(Minute FROM Dajh.Job_Duration) * 60 + Extract(SECOND FROM Dajh.Job_Duration))
                           ,'SECOND') AS INTERVAL DAY(3) TO SECOND(0)) AS Avg_Job_Duration
      ,COUNT(*) Executions
  FROM Dba_Autotask_Job_History Dajh
WHERE Dajh.Job_Start_Time > Trunc(SYSDATE) - 14
GROUP BY Dajh.Client_Name;
-------------------------------- ---------------- ---------------- ---------------- ----------
auto optimizer stats collection  +000 00:09:20    +000 00:00:14    +000 00:02:33            30
auto space advisor               +000 00:01:40    +000 00:00:04    +000 00:00:39            30
sql tuning advisor               +000 01:00:21    +000 00:00:10    +000 00:27:01            14

As you see there are multiple conversion steps necessary

The conversion steps

  • converting interval to seconds :

secs = Extract(DAY FROM Dajh.Job_Duration) * 24 * 60 * 60 + Extract(Hour FROM Dajh.Job_Duration) * 60 * 60 + Extract(Minute FROM Dajh.Job_Duration) * 60 + Extract(SECOND FROM Dajh.Job_Duration)

  • do an avg on the number value

avg_secs = avg(secs)

  • out of the average amount seconds generate an interval type again

avg_interval = Numtodsinterval(avg_secs)

  • now adjust the precision of days to seconds to the output of the precision of the max()/min() columns

avg_interval_casted = CAST(avg_interval AS INTERVAL DAY(3) TO SECOND(0))


So as you see mssing SQL features can be emulated via SQL. But hopefully some native version of AVG() will work for the Oracle built in interval type. If you know a shorter version with same output just let me know, cheers.


References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>