My NoPlsql versus SmartDB deep-dive presentation materials

For those who are interested here are my two slidedecks used in yesterday's deep-dive session at ODTUG, here they are in ppsx format which includes all the animations. Part 1: Comparison between NoPlsql and SmartDB approach Part 2: SmartDB, how to do it? Enjoy. Toon

The Relational Data Model

Mid-eighties. I was studying Computer Science. Little did I know back then that this thing called "The Relational Data Model" (RDM) would become huge in the IT-industry. The topic was still hot in academia at that time. My luck was that I liked those courses. Predicate Logic, Set Theory, Database Design, SQL. I aced them all. It was no surprise then, that I ended up working with Oracle software

NoPlsql vs ThickDB: which one requires a bigger database server?

Slides of my Oaktable World presentation held during OOW 2016 can be found here: NoPlsql vs ThickDB

Continuing the story

One of my 2015 new year's resolutions, was to finish the story I started on this blog. The story being a talk that I had delivered a couple of times 3-4 years ago on various Oracle/usergroup events. The talk is about why I think triggers should *not* be considered harmful. There is one specific use-case for triggers -- implementing validation code for what's called a multi-row constraint -- that I will treat in-depth on this (more...)

New post on semantic query optimization bug


New blog with general Oracle database posts.

Started Oracle database tidbits blog here.

The materialized view approach for implementing a table constraint

| Mar 8, 2013
In yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.

The high-level cookbook for this approach is as follows:
  1. We create a materialized view that refreshes on commit,
  2. The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,
  3. And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,
  4. We devise (more...)

And what about table constraints?

| Mar 7, 2013
In a previous post we've introduced a classification scheme for constraints:
  • attribute constraints
  • tuple constraints
  • table constraints
  • database constraints
  • dynamic constraints
And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.

Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would (more...)

Implementing attribute and tuple constraints

| Mar 3, 2013
In our previous post we have introduced a classification scheme for data integrity constraints. In todays post we will present thoughts & guidelines around how to implement the first two classes:  attribute and tuple constraints.

The examples given in the previous post were:

Attribute constraints
  • Salary must be a positive integer between 750 and 14000.
  • Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.
Tuple constraints
  • A manager cannot have a salary less than 5000.
  • An employee working in department 10, cannot be a salesman.

We could implement these constraints using a trigger approach. Let's go (more...)

Data integrity constraint classification

| Mar 3, 2013
Before we start investigating the complexities involved in implementing data integrity constraints using database triggers, we will first introduce a classification schema for data integrity constraints. Agreeing upon a classification scheme for constraints, helps us a lot in implementing them: for we can then approach the problem area on a class-by-class basis.

A classification scheme needs to have a few properties:
  1. The constraint classes should be mutually exclusive: any given constraint should clearly fall into one, and only one, of the classes;
  2. The scheme should cover all types of constraints: i.e. it cannot be that we can (more...)

The Helsinki Declaration (IT-version) 2012-09-30 12:49:00

Just attended this session here at Oracle Openworld: Flexible Design and Modeling: Planning for Constant Change Gwen Shapira - Senior Consultant, Pythian Robyn Sands - Principal Member of Technical Staff, Oracle Which was expressing the same message as the Helsinki Declaration does: Create API's in the database layer and do not let outside software layers access tables directly. It

The fourth use-case for triggers

| Mar 26, 2012
In our previous post we talked about three of the four use-cases we introduced. Triggers can:
  1. Assign/modify (row) column values.
  2. Execute insert/update/delete statements.
  3. Execute non-transactional stuff.
We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.

In this post we continue with use-case four. Triggers can:
  1. Execute select statements.

This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a (more...)

Where TK agrees with TK (or: why are triggers harmful)

| Feb 6, 2012
Sofar we've explored playing around with a few triggers to implement a business rule. In this post I'd like to step back a bit and take a birds-eye view at the common use-cases that I see brought up for introducing triggers into the app-development picture.

The first use-case is: we use triggers to assign or modify column values of mutating rows (that is, rows currently being inserted, updated, or deleted). Here's a few typical examples of this use case that, I'm sure, must look familiar to you too.

For this use-case we always use a row-level trigger (for each row), (more...)

Statement level constraint consistency

| Jan 29, 2012
In the past week I've been investigating how Oracle fires triggers with the Merge and Multi-Table-Insert statements. Also took a look at 'statement-level constraint consistency' with these two types of statements. My findings are here:

Normal transmission on harmful triggers should resume shortly.

Workaround 2: don’t use the row-trigger

| Jan 4, 2012
So where were we?

We wanted to implement the following business rule: "Cannot have a manager without a clerk in the same department." At the end of this post, we tried implementing that using a straight-forward row-trigger, which introduced us to the mutating table error. And we've explained thereafter (here and here) that this error is there for a very good reason.

We've also clarified that the two common 'workarounds' 1a and 1b really should not be used.

So using a row-trigger to check for a clerk whenever a manager is inserted will not work. (more...)

Look mom: a mutating table error without a trigger!

| Dec 30, 2011
Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?

ORA-04091: table ... is mutating, trigger/function may not see it

We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from (more...)

The mutating table error prevents non-deterministic behavior of your code

| Dec 23, 2011
A short recap of workarounds 1a and 1b from our previous post.
  • In workaround 1a we ensured that the query that was selecting from the mutating table, was being executed from within an autonomous transaction.
  • In workaround 1b we ensured that this query was being executed via a loopback database link.
We also discussed the major difference between the two workarounds:
  • 1a: the query sees the mutating table as it existed prior to the start of the main transaction.
  • 1b: the query sees the mutating table as it is being mutated by the triggering DML statement. Essentially this workaround behaves (more...)

"Workarounds" for ORA-04091

| Dec 13, 2011
In the previous post, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table.

  • The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger);
  • It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager;
  • (more...)

Some preliminaries

| Nov 25, 2011
There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.

If you try to do so, Oracle will throw an (more...)

So what triggers are we talking about?

| Nov 22, 2011
Here's an overview of the talk.

This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to (more...)