The Talk is dead, long live the Talk
It's just over a year since I published my "Why Use PL/SQL?" blog post and paper. At the start of the paper, I say that Toon Koppelaars has, for at least the last decade and a half, been a practical exponent of, and a vigorous advocate for, the Thick Database paradigm. (These days, Toon is a Consulting Member of Technical staff in Oracle Corporation's Real-World Performance Group.)
I've given the companion talk more than a dozen times over the last year or so, and it's now time to take this advocacy project to the next level. I'm going to support Toon in a new season of conference speaking with a two-session talk called "A Real-World Comparison of the NoPlsql and Thick Database Paradigms". Toon has already delivered a teaser entitled "The NoPlsql and Thick Database Approaches—Which One Do You Think Requires a Bigger Database Server?" It's posted on YouTube.
What do we mean by the terms NoPlsql and ThickDB?
I use the terms NoPlsql paradigm and Thick Database paradigm—hereinafter ThickDB—in my "Why Use PL/SQL?" paper. They're reasonably self-explanatory; but they imply just a bit more than the bare words.
Of course, NoPlsql implies that there's absolutely no PL/SQL anywhere in the picture: neither are there any PL/SQL units in the database; and nor are anonymous PL/SQL blocks issued from client-side code. This, in turn, implies (for a normal application that does no DDL at run-time) that the only database calls from client-side code are select, insert, update, and delete statements—together with commit and rollback. But we mean yet more by the term. There's a huge correlation between the avoidance of PL/SQL and the avoidance of anything but naïve single-row SQL statements. Toon says more about this in the YouTube video.
In contrast, ThickDB implies that every database call from client-side code invokes just a single PL/SQL subprogram, and that therefore all select, insert, update, delete, commit, and rollback statements are issued from PL/SQL code. But we mean yet more by this term too. We imply that the SQL that is issued by PL/SQL code is humanly written (so you might also see merge statements), and that the relational design of the tables that these statements query and change is also the result of intelligent, explicit human design. This implies, in turn, that bulk retrieval of results, and bulk batching of changes, is implemented as a matter of course; and, more significantly, that the full power of SQL is used, when appropriate, to specify operations on potentially very large sets of rows as single statements.
Toon's teaser: The NoPlsql and ThickDB Approaches
Toon delivered this talk in the OakTable World event that took place at the same time as, and next door to, Oracle OpenWorld 2016. I asked him to repeat it at Oracle HQ on the Friday after OpenWorld so that we could make a good recording to post on the Oracle Learning Library YouTube channel. Here's the description:
Toon Koppelaars describes an experiment to measure the work done by Oracle Database to complete a specific task using different approaches. The NoPlsql approach treats the database as no more than a persistence layer, using only naïve single-row SQL statements; it implements all business logic outside of it. The ThickDB approach treats the database as a processing engine; it uses a combination of sophisticated set-based SQL statements and PL/SQL to implement all business logic inside it. 'No business logic in the database' advocates take note: the ThickDB approach gets the task done with far less database work than the NoPlsql approach.
I'm pleased to see that, just a day after publication, the video has already received a couple of hundred views.
Update added 28-Nov-2016: the video has now been viewed almost 2,000 times.
A Real-World Comparison of the NoPlsql and Thick Database Paradigms
Toon (with a little editorial help from me) has put together a two-session talk and we shall be presenting it this year at the upcoming Oracle User Group conferences in Bulgaria, Germany, and the UK, and next year (so far) at the Hotsos Symposium. We've also submitted it for Kscope17 and we're keeping our fingers firmly crossed that the committee will say "yes, please". Here's the abstract:
The NoPlsql paradigm starts with an object oriented domain model in the middle tier. This leads naturally to treating the database as a bag of tables, so that primitive SQL statements express its API. The ThickDB paradigm starts with the design of the relational model. This leads naturally to allowing client-side code only to call PL/SQL subprograms, so that all SQL is done from PL/SQL. These two paradigms are mutually incompatible.
In our first session, we show, based on real-world use cases, how the NoPlsql paradigm brings problems with correctness, security, and performance, and we note that projects that adopt the NoPlsql paradigm use most of their resources on developing plumbing code rather than code that implements actual business functionality. These problems are just the tip of the iceberg and we explain how the NoPlsql paradigm also brings maintenance nightmares.
In our second session, we turn to the ThickDB paradigm, rehearse the reasons for its superiority, and explain that it implies more than just allowing database calls to invoke only PL/SQL subprograms. We formalize a layered code classification scheme which leads to optimal understandability and maintainability of both your PL/SQL and your SQL code -- which properties bring the maximum probability of correct behavior. We will convince participants that adopting the ThickDB paradigm guarantees the avoidance of the awful problems brought by the NoPlsql paradigm and establishes the database tier as a reliable central service provider in the application landscape. We will debunk the myth that "moving business logic out of the data tier and into the middle tier" improves scalability.