Database Reference
In-Depth Information
Knowing What's Out There
Not having a full understanding of what is available to you can come back to haunt you in the long run. I was working
with some developers with years of experience developing database applications—on other databases. They built
analysis software (trending, reporting, visualization software). It was to work on clinical data related to healthcare.
They were not aware of SQL syntactical features like inline views, analytic functions, scalar subqueries. Their major
problem was they needed to analyze data from a single parent table to two child tables; an Entity Relation Diagram
(ERD) might look like Figure
1-1
.
Child_1
Parent
Child_2
Figure 1-1.
Simple ERD
The developers needed to be able to report on the parent record with aggregates from each of the child tables.
The databases they worked with in the past did not support subquery factoring (
WITH
clause), nor did they support
inline views—the ability to “query a query” instead of query a table. Not knowing these features existed, they wrote
their own database of sorts in the middle tier. They would query the parent table and for each row returned run an
aggregate query against each of the child tables. This resulted in their running thousands of tiny queries for each
single query the end user wanted to run. Or, they would fetch the entire aggregated child tables into their middle tier
into hash tables in memory—and do a hash join.
In short, they were reinventing the database, performing the functional equivalent of a nested loops join or a hash
join, without the benefit of temporary tablespaces, sophisticated query optimizers, and the like. They were spending
their time developing, designing, fine-tuning, and enhancing software that was trying to do the same thing the database
they already bought did! Meanwhile, end users were asking for new features but not getting them, because the bulk of
the development time was in this reporting “engine,” which really was a database engine in disguise.
I showed them that they could do things such as join two aggregations together in order to compare data that was
stored at different levels of detail. Several approaches are possible, as illustrated in Listings 1-1 through 1-3.
Listing 1-1.
Inline Views to Query from a Query
select p.id, c1_sum1, c2_sum2
from p,
(select id, sum(q1) c1_sum1
from c1
group by id) c1,
(select id, sum(q2) c2_sum2
from c2
group by id) c2
where p.id = c1.id
and p.id = c2.id
/
Listing 1-2.
Scalar Subqueries That Run Another Query per Row
select p.id,
(select sum(q1) from c1 where c1.id = p.id) c1_sum1,
(select sum(q2) from c2 where c2.id = p.id) c2_sum2
from p
where p.name = '1234'
/
