With the ever-burgeoning feature sets available in modern
databases, it’s difficult to separate the wheat from the chaff. There are
plenty of high-level data warehousing features in Oracle for instance that you
might never need. Moreover, there may be some other features such as ACID
compliant transactions that you just can’t do without. We’ll look at the major
ones, such as stored procedures, views, snapshots, table datatypes, transactions,
and so on. We’ll look at Postgresql, MySQL, and Oracle and discover which ones
do what you need.
Stored Procedures
Storing application code in the database has had it’s
champions and detractors. I remain fairly agnostic on the issue so I’ll try to
discuss both sides. When you start putting application code in the database, you
are in the thoroughly non-portable arena. That code, were you to port your
application to another database, would have to be rewritten. But its very
specificity to that database means it can also take advantage of, and wire
close to that engine. There are situations where stored code in the database
can be notably faster. Supposed you have to update some chunk of a million
rows after doing some machinations on the data.
In a stored procedure the data is read, manipulated, and
updated in one step. Meanwhile if you did the same in your middle tier
application code, you would have to send that data set over the network, do
your manipulations, and send it back. Not only would it make this one task slower,
but other transactions vying for that same data could potentially have to wait
while that data is in transit, and being manipulated. Also, stored code can
serve to encapsulate specific requests which can be invaluable at simplifying
your overall application. All three databases support stored procedures and
functions. Oracle also supports packages, or collections of stored procedures
as well as various object oriented features, which almost no one ever uses. An
additional note, a database engine actually context switches between stored
code, and the SQL code embedded therein. As of 9i, Oracle introduced bulk
binding, so you can do work on large sets of rows, and update them all in one
go, instead of each loop iteration. This feature can even further improve
performance quite dramatically.
Views
Views are basically stored queries, and as such are not
overly complex to implement. However when they are used in a query, they
necessarily make that query more complex. So obviously, subqueries need to be supported
before Views can become available in a database. Oracle has obviously had
Views for some time. As of 5.0, MySQL has also supported views. As with Oracle, MySQL also supports UPDATEABLE views, with
some restrictions. Postgres also supports views and UPDATEABLE views. See
details in the Complex SQL section.
Materialized Views (snapshots)
These are supported well in Oracle by default. As a refresher, remember a materialized view (I prefer the much more visual snapshot terminology, but I digress) is a periodically updating copy or subset of a table. Imagine a view that fills a mirror copy with its query. Until the next refresh, that copy is static, not updated with the master. Usually a tradeoff is made between frequency of update, and maintenance of the transaction log (like an index) which supports it. On paper, MySQL and Postgresql do not support materialized views, however there are implementations of this on the internet, which should fit your needs, should you go this route and need some support. A stored procedure creates the materialized view and another one refreshes it. In essence a CREATE TABLE my_copy AS SELECT…
Language Integration
Today, programming web based applications for various
database applications is a truly egalitarian world. Almost all of the popular
web languages support all of these database types. Java, PHP, Perl, Python,
C#/.NET, etc etc. The world is your oyster.
Triggers
MySQL, Oracle, and Postgres all support BEFORE & AFTER
event triggers on INSERT, UPDATE and DELETE. Personally I prefer to shy away
from triggers unless absolutely necessary. They tend to be forgotten about, and
sometimes come back to bite you. When used sparingly, they can be great though.
Security
All three of these databases have vulnerabilities. It is truly
the nature of software to have corners with trouble hiding there. Moreover,
all three of them have regular patches of updates released. My personal
feeling though is that open-source means that necessarily more eyes, and often
more critical eyes, will be on the code. What’s more, the pressure of the
community is much greater in the open-source world. In the commercial space, the
vendor can, and often does spin it’s wheels when the repair is more expensive than
the perceived cost of waiting on the fix.
On the point of security inside the database, all three
support password logins and encryption of various types inside the database.
Oracle does have a newer feature called virtual private database, where
sections of tables, and columns can be encrypted, and hidden from view. This
can be very useful for controversial or sensitive data, that DBAs and other administrators
should not have access to.
Conclusion
Our three database platforms obviously have a lot of features, and different solutions for the same problems. In terms of security, triggers, views, materialized views, and stored procedures they provide a lot of the same functionality, though with differences in terms of performance and configurability. In part II we’ll talk about some of the ways that the databases really start to differ significantly, in terms of indexing, but probably most importantly in their optimizing engines.
