 |
|
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.
|
