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


 

HTML Text

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,
       salary,
       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
FROM
  (
  SELECT last_name,
       salary,
       department_id
    FROM hr.employees ) as emp
JOIN
  (
  SELECT department_id,
       avg(salary)  as salary
  FROM hr.employees emp2
  GROUP BY department_id ) as dept
ON emp.department_id = dept.department_id
FULL OUTER JOIN
  (
  SELECT
       avg(salary) as salary
    FROM hr.employees ) as comp
ON TRUE

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.

Procedural

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