PL/SQL and edition-based redefinition:
the perfect marriage.

  • November 1, 2016

NoPlsql versus ThickDB

Steven Feuerstein
Developer Advocate for PL/SQL

What do we mean by the terms NoPlsql and ThickDB?

We use the terms NoPlsql paradigm and Thick Database paradigm—hereinafter ThickDB—in the "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.

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.

A Real-World Comparison of the NoPlsql and Thick Database Paradigms

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.

Join the discussion

Comments ( 2 )
  • Jeff Jacobs Friday, November 4, 2016

    To make matters worse, I see developers using anonymous PL/SQL blocks because stored PL/SQL is not allowed. So my poor developer waits 45 minutes for the build to complete for the most minor change, and, if lucky, nobody else has broken the build!

  • Bryn Saturday, November 5, 2016

    Here's my stance on anonymous PL/SQL blocks. They serve a useful purpose when they'll be used just once. This is quite common in installation scripts. They can also be useful if you want to take advantage of PL/SQL in a schema where you can't create PL/SQL objects. Active Data Guard is the canonical example of this use case. For all ordinary cases, the anonymous block has no role to play. Your PL/SQL should be safely and securely inside the database. That way, its source code will therefore be, of course, invisible to client-side code; and the internal structures that store the data will remain a closely guarded secret.

    Finally, I have to concede that the primitive anonymous PL/SQL block--that's one that does nothing except invoke exactly one PL/SQL procedure or function--must sometimes be used instead of the call statement. This scenario occurs when you want to invoke a function whose return type is among the non-persistable, PL/SQL-only, datatypes.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.