Database Reference
In-Depth Information
Listing 1-3.
Subquery Factoring via the
WITH
Clause
with c1_vw as
(select id, sum(q1) c1_sum1
from c1
group by id),
c2_vw as
(select id, sum(q2) c2_sum2
from c2
group by id),
c1_c2 as
(select c1.id, c1.c1_sum1, c2.c2_sum2
from c1_vw c1, c2_vw c2
where c1.id = c2.id )
select p.id, c1_sum1, c2_sum2
from p, c1_c2
where p.id = c1_c2.id
/
In addition to what you see in these listings, we can also do great things using the analytic functions like
LAG
,
LEAD
,
ROW_NUMBER
, the ranking functions, and so much more. Rather than spending the rest of the day trying to figure
out how to tune their middle tier database engine, we spent the day with the
SQL Reference Guide
projected on the
screen (coupled with SQL*Plus to create ad-hoc demonstrations of how things worked). The end goal was no longer
tuning the middle tier; now it was turning off the middle tier as quickly as possible.
Here's another example: I have seen people set up daemon processes in an Oracle database that read messages
off of pipes (a database IPC mechanism implemented via
DBMS_PIPE
). These daemon processes execute the SQL
contained within the pipe message and commit the work. They do this so they could execute auditing and error
logging in a transaction that would not get rolled back if the bigger transaction did. Usually, if a trigger or something
was used to audit an access to some data, but a statement failed later on, all of the work would be rolled back. So, by
sending a message to another process, they could have a separate transaction do the work and commit it. The audit
record would stay around, even if the parent transaction rolled back. In versions of Oracle before Oracle 8
i
, this was an
appropriate (and pretty much the only) way to implement this functionality. When I told them of the database feature
called
autonomous transactions
, they were quite upset with themselves. Autonomous transactions, implemented
with a single line of code, do exactly what they were doing. On the bright side, this meant they could discard a lot of
code and not have to maintain it. In addition, the system ran faster overall, and was easier to understand. Still, they
were upset at the amount of time they had wasted reinventing the wheel. In particular, the developer who wrote the
daemon processes was quite upset at having just written a bunch of “shelfware.”
I see examples like these repeated time and time again—large complex solutions to problems that are already
solved by the database itself.
I've been guilty of this myself.
I still remember the day when my Oracle sales consultant
(I was the customer at the time) walked in and saw me surrounded by a ton of Oracle documentation. I looked up at
him and just asked “Is this all true?” I spent the next couple of days just digging and reading. I had fallen into the trap
that I knew all about databases because I had worked with SQL/DS, DB2, Ingress, Sybase, Informix, SQLBase, Oracle,
and others. Rather than take the time to see what each had to offer, I would just apply what I knew from the others to
whatever I was working on. (Moving to Sybase/SQL Server was the biggest shock to me—it worked nothing like the
others at all.) Upon actually discovering what Oracle could do (and the others, to be fair), I started taking advantage
of it and was able to move faster, with less code. This was in 1993. Imagine what you can do with the software today,
almost two decades later.
Take the time to learn what is available. You miss so much by not doing that. I learn something new about Oracle
pretty much every single day. It requires some keeping up with; I still read the documentation.
