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

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

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

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

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

Database 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 after Comparison.

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.