Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: SQL Analytics
Oracle Tips by Burleson

A specific area of difficulty that does not have a good work around is the topic of SQL Analytics.  Analytic functions are like aggregates (SUM, COUNT, AVG) in that they work on groups of rows but they differ in that they return a value for each row in the group. 

If your application is currently making heavy use of analytics, there is not currently a good candidate for conversion to EnterpriseDB.  You can replace a single analytic query with a multiple query, inline view (in most cases), but it is an error prone and performance-degrading task. 

An example of an analytic query might be:

SELECT last_name,
       avg(salary) over () company_average,
       avg(salary) over (partition by department_id) department_average
  FROM hr.employees;

This query will return, for each employee, the employee's salary, the company's over all average salary and the departmental average salary.  This is a pretty simple query but it is not easily duplicated without analytic support.

The easiest solution would be to change the format of whatever report is using this to not expect all the answers in a single query.  If that is not an option, it can be duplicated this way:

SELECT last_name,
       emp.salary as salary,
       dept.salary as deptartment_average,
       comp.salary as company_average
  SELECT last_name,
    FROM hr.employees ) as emp
  SELECT department_id,
       avg(salary)  as salary
  FROM hr.employees emp2
  GROUP BY department_id ) as dept
ON emp.department_id = dept.department_id
       avg(salary) as salary
    FROM hr.employees ) as comp

That means that applications that use analytics can be ported but that the effort becomes much larger.  If you use analytics heavily, it is probably a losing proposition.  That is only true until EnterpriseDB and PostgreSQL support analytics and I expect that to happen in the not too distant future.


The PL/SQL language is a huge language.  Ignoring for a moment the large amount of supplied packages, the inherent features of PL/SQL makes it my favorite language.  PL/SQL is a very robust (great exception handling) language with full native support for both static and dynamic SQL.  In my opinion, there is no language that is more perfect for writing database applications.

EnterpriseDB (the company) has taken on a tremendous task in trying to fully support a compatible procedural language.  The EnterpriseDB Superset Procedural language (SPL) is making great strides in providing that compatibility. 

As of this writing, SPL already provides a large percentage of the native support that PL/SQL provides.  The support for the supplied packages that comes with Oracle is a much lower percentage.  How important those percentages are to an individual application depends greatly on how that application is written.

I am a user of advanced features.  That's just the type of developer that I am.  I would rather let the language do everything it can for me and only code what I must.  Unfortunately, that is the type of programmer who might be most disappointed with what is missing in EnterpriseDB SPL.

I want to make the point again that EnterpriseDB is continually improving and adding to the database server.  In just the last year it has come a tremendous distance.  A book like this will start to get stale by the time it hits the shelves.  If you need a particular feature that I mention below, verify that it is still not supported with EnterpriseDB support. 

Not everything I mention below is a "missing feature".  Like the SQL support above, some things are not bad, only different.  There are no show stoppers below, just points to watch out for when developing or porting your applications.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter