Good old BIN_TO_NUM to check the overall status

A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.

The requirement that I needed to implement was the following:

A number of tasks need to be fulfilled, the order doesn't matter, and they need to be checked by a supervisor. Before the supervisor gives his/her stamp of approval, the tasks all need to be completed.

For this example I will leave the authorization out, it is irrelevant.

create table tasks
(task_date       date   not null
,task_a          number not null check (task_a in (0,1))
,task_b          number not null check (task_b in (0,1))
,task_c          number not null check (task_c in (0,1))
,tasks_checked   number not null check (tasks_checked in (0,1))
)
/

The table contains a date for which the tasks need to be completed, indicators for the individual tasks (A,B, and C) and the Tasks Checked indicator.
All indicators are NOT NULL, datatype NUMBER and only allowed to contain a zero or one. An zero indicates FALSE, a one indicates TRUE.

The requirement states that the verification of the tasks should take place before the tasks are done. There are several ways to implement this requirement, and this time I'm going to use the combination of zeros and ones to create a binary representation.
I will add to overall status as a virtual column to the table using the built-in function BIN_TO_NUM.

alter table tasks
add (bin_status as (bin_to_num (task_a, task_b, task_c, tasks_checked)))
/

Now the (not so) magic part:
When the binary status equals 14, all tasks are completed and can be checked by a supervisor.
When the binary status is an odd number, the checking of the tasks is done before the tasks are completed and this is not allowed. The only exception is when the binary status equals fifteen (15), then all tasks are done and it is checked by a supervisor.
When the binary status an even number, all is good.
All this can easily be captured in a CHECK constraint:

alter table tasks
add constraint chk check (
   case when bin_status = 15 then 1
        when mod (bin_status, 2) = 0 then 1 
        else 0 end = 1
)
/

Finally two inserts to show that it works as expected.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,0
   ,0
   ,1
   ,0
 14     );

1 row created.

insert into tasks
   (task_date
   ,task_a
   ,task_b
   ,task_c
   ,tasks_checked   
   )
values
   (sysdate
   ,1
   ,0
   ,1
   ,1
   );

insert into tasks
*
ERROR at line 1:
ORA-02290: check constraint (ALEX.CHK) violated

For the first time I've used LiveSQL to create the scripts that go along this blogpost, you can find that right here.

Links

• Oracle documentation: BIN_TO_NUM
• LiveSQL demo

Deadlock with a Virtual Column

Update: There is already a bug filed for this issue, it is registered under number: 22591494

Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.

First of all let's start with the version of the database that I tested this on. Yes, I know it's an 11 database that's because the client is still running on this release.
These tests were run on the Virtual Box image that is provided by Oracle.
I still need to run these tests on Oracle 12c.
I just ran the script on my Oracle 12c database (in a PDB) and the same deadlock occurs.

   SQL> select *
     2    from v$version
     3  /

   BANNER
   ----------------------------------------------------------------------
   Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
   PL/SQL Release 11.2.0.2.0 - Production
   CORE 11.2.0.2.0 Production
   TNS for Linux: Version 11.2.0.2.0 - Production
   NLSRTL Version 11.2.0.2.0 - Production

The setup for this test is based on a copy of the EMP table.

SQL> create table emp
  2  as
  3  select *
  4    from scott.emp
  5  /

Table created.

To create a Virtual Column on my copy of the EMP table, I need a deterministic function.
This function takes two arguments, one for the ENAME and one for the EMPNO. And what does the function do? Actually nothing, it returns NULL.

   SQL> create or replace
     2  function vc
     3    (p_ename in emp.ename%type
     4    ,p_empno in emp.empno%type
     5    )
     6   return varchar2 deterministic
     7  is
     8  begin
     9   return null;
    10  end vc;
    11  /

   Function created.

The function needs to be deterministic because that is required when you want to define a Virtual Column.
Now we can add the Virtual Column (called VC) to my copy of the EMP table.

SQL> alter table emp
  2  add descr as (vc (ename, empno))
  3  /

Table altered.

So far, no problems. It all works.
The trouble began when you execute a TRUNCATE TABLE statement.

   SQL> truncate table emp
     2  /
   truncate table emp
          *
   ERROR at line 1:
   ORA-04020: deadlock detected while trying to lock object ALEX.EMP
   

To be honest, this is not the first deadlock that I created and it probably won't be the last :)
The snag with deadlocks is trying to figure out what caused it in the first place.
Of all things that I thought would happen, a deadlock is not one of them.
How can it? It is my own personal VirtualBox and I am the only one using it.

The first step investigating a deadlock is usually the alert.log, however there was nothing in it regarding the deadlock... honest.

After a bit of googling, I found a note on deadlocks by Yong Huang (link at the bottom) describing the causes of deadlocks.
In that article he points out that you can get more insight if you set a certain event, and that's what I did.

   SQL> alter session set events '4020 trace name processstate forever, level 10'
  2  /
  
Session altered.
   

To find out where the trace file was located, and the name of it, I used a query by Tanel Poder (link at the bottom).

      SQL> select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
     2 (select spid||case when traceid is not null then '_'||traceid else null end
     3       from v$process where addr = (select paddr from v$session
     4       where sid = (select sid from v$mystat
     5           where rownum = 1
     6      )
     7         )
     8 ) || '.trc' tracefile
     9* from v$parameter where name = 'user_dump_dest'
   SQL> /

   TRACEFILE
   -------------------------------------------------------------------------------------------------
   /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3791.trc
   

In that trace file was the following information:

   A deadlock among DDL and parse locks is detected.
   This deadlock is usually due to user errors in
   the design of an application or from issuing a set
   of concurrent statements which can cause a deadlock.
   This should not be reported to Oracle Support.
   The following information may aid in finding
   the errors which cause the deadlock:
   ORA-04020: deadlock detected while trying to lock object ALEX.EMP
   --------------------------------------------------------
    object   waiting  waiting       blocking blocking
    handle   session     lock mode   session     lock mode
   --------  -------- -------- ----  -------- -------- ----
   0x31ae0d7c  0x3aab3cf4 0x31afc4c0    X  0x3aab3cf4 0x31aeb718    S
   

As you can see in the text (taken from the trace file), you can see that the sessions involved in the deadlock is the same, both the waiting and the blocking session are 0x3aab3cf4.

So at least my assumptions were correct, I was blocking myself.
Not that it got me any further...

After quite a long time fiddling around, I discovered the following.
If I change the function like below, the deadlock doesn't occur. See if you can spot the difference.

   SQL> create or replace
  2  function vc
  3    (p_ename in varchar2
  4    ,p_empno in number
  5    )
  6   return varchar2 deterministic
  7  is
  8  begin
  9   return null;
10  end vc;
11  /

Function created.

SQL> truncate table emp
  2  /

Table truncated.

Did you spot the difference?
The function at first used anchored datatypes for the arguments (%TYPE) and later on just a simple type (NUMBER and VARCHAR2).
Using the simple types, the truncate works.
There are some oddities when it exactly occurs and I haven't figured out yet when the deadlock occurs exactly. It seems that when an argument is anchored (%TYPE) and the underlying datatype is a NUMBER, the deadlock occurs...
Like I said I haven't really figured out what causes it.

Links

1. Two common Deadlocks by Yong Huang
2. Tanel Poder: Querying the current tracefile name, using SQL – with tracefile_identifier
3. Oracle Base on Virtual Columns

Business Rule: Only One per Day, but keep the time

The business rule states:

Only one entry is allowed per ID and per day and the time should be recorded.

The table involved (simplified for the blog post)

SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  );

Wouldn't it be nice if it was possible to do it like this?

SQL> create table test

  2  (id         number

  3  ,inspection_dt date

  4  ,constraint one_per_day unique (id, trunc (inspection_dt))

  5  );

,constraint one_per_day unique (id, trunc (inspection_dt))

                                    *

ERROR at line 4:

ORA-00904: : invalid identifier

This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.
As you can tell from the error message, this is not allowed.
Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.

Business Rule: Only One Clerk per Department

During the 7Up workshop, a workshop geared towards experienced developers who want to get up to speed with everything that happened in Oracle since release 7, one of the “tricks” that passes by is how to implement the Business Rule:

Only one Clerk per Department is Allowed

The way we show how to declaratively implement this Business Rule is by using a Unique Function Based Index. Every now and then someone will comment that the implementation is a hack, “'cause an Index is not meant to implement a Business Rule, it’s there to enhance performance.”
I don’t necessarily agree with this, but I do believe that Oracle 11g offers a more elegant solution (be it very similar, but is considered less of a hack).

First let’s take a look at the “hack”, with the Unique Function Based Index. Then we’ll look at the way to do the same in Oracle 11g. Lastly I will show you the similarity between the two.

Unique Function Based Index

First off, the Function Based Index. FBI’s were introduced in Oracle 9i and allow you to create an index using a function.
Especially useful when you have a query like

select *
from emp
where upper (ename) = :ename
;

When the ename column in the example above is indexed (the regular way) you can not use this index because of the UPPER function.
Indexing the ename column with the UPPER function would allow use of the index. Here is the code for this example:

create index upper_name
on emp (upper(ename))

When the index is created this way, it can be used with the first statement.

The way to implement the Business Rule “Only one Clerk per Department”:

create UNIQUE index one_clerk_per_deptno
on emp (case job
        when 'CLERK'
  then deptno
  end
)

Using a simple CASE expression only DEPTNO are used in the index when the JOB is equal to CLERK. Because it is a unique index, there can only be one CLERK in each department.

Virtual Columns

Some might call the implementation of the Business Rule in the previous section a “hack”. Instead of using indexes to increase performance, the index is used to declaratively implement a Business Rule.
Oracle 11g introduced Virtual Columns. I really like Virtual Columns more and more because of the multitude of possibilities. Using Virtual Columns to implement this Business Rule is very straight forward.
Instead of using the simple CASE expression in an index, we will use it in the definition of the Virtual Column:

alter table emp
add one_clerk_per_dept as (case
                          when job='CLERK'
     then deptno
       end
                        )

With the statement above an extra column is added to the EMP table. This Virtual Column, based on the CASE expression, will only show a value when the JOB equals CLERK.

SQL> select job
2       , one_clerk_per_dept
3    from emp
4  /

JOB       ONE_CLERK_PER_DEPT
--------- ------------------
CLERK                     20
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK                     20
CLERK                     30
ANALYST
CLERK                     10

As you can see in the resultset above, the ONE_CLERK_PER_DEPT has a value in its column when the JOB is a CLERK. Because the Business Rule says “Only one Clerk per Department” we need to make this column UNIQUE.
With this data this is not possible, because department has two CLERKS. One has to go…

SQL> update emp
2     set job = 'NOCLERK'
3   where job = 'CLERK'
4     and rownum = 1
5  /

1 row updated.

SQL> alter table emp
2    add constraint one_clerk_uk UNIQUE (one_clerk_per_dept)
3  /

Table altered.

SQL> update emp
2     set job = 'CLERK'
3   where job = 'NOCLERK'
4  /
update emp
*
ERROR at line 1:
ORA-00001: unique constraint (ALEX.ONE_CLERK_UK) violated

And now the Business Rule is implemented in a more “natural” way, without resorting to a “hack”.

The Same Difference…

Both methods implement the same Business Rule. Both implement the Business Rule in a declarative way. To some people the first method feels like a “hack” and find the second method more “natural”. But how different are they really?
Not as much as you might have suspected. When you use the first method (hacking with Function Based Indexes), the USER_IND_COLUMNS datadictionary view reveals the implementation.

SQL> select column_name
2    from user_ind_columns
3   where index_name = 'ONE_CLERK_PER_DEPT'
4  /

COLUMN_NAME
---------------------------------------------------------
SYS_NC00010$

SQL> select job
2       , SYS_NC00010$
3    from emp
4  /

JOB       SYS_NC00010$
--------- ------------
NOCLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK               20
CLERK               30
ANALYST
CLERK               10

A hidden column is added to the table with a very obscure name. Apparently a Function Based Index is pretty similar to a Virtual Column.

Business Rule: Only use Active Records

The Business Rule that needed to implemented:

Only "active" records can be used in other tables.

Before Oracle 11g, one way to implement this Business Rule was to Materialized View with a Check Constraint defined on it. However In Oracle 11g you can implement this Business Rule a lot simpler by using a Virtual Column and a Foreign Key.

First let set up the tables that we are going to use in this example:

create table lookup
(id number primary key
,code varchar2(10)
,value varchar2(100)
,ind_active varchar2(1)
);

alter table lookup
add constraint lookup_ind_chk check (ind_active in ('Y', 'N'))
/ 

This table has the ind_active column which dictates whether the record in this table may be used in other tables. "Y" indicates that we can use it in other tables, "N" indicates that you can not use it (anymore).

Next we need another table to reference our Lookup Table:

create table t
(id number primary key
,lu_id number references lookup
); 

Together with some data in both tables:

insert into lookup values (1, 'One', 'First Value', 'Y')
/
insert into lookup values (2, 'Two', 'Second Value', 'Y')
/
insert into t values (1, 1)
/
insert into t values (2, 2)
/ 

Now to implement the Business Rule, using the Materialized View method. Because the rule needs to be checked whenever someone wants to use a record from the Lookup table, we want to validate the rule as soon as possible. To have the Materialized View refresh when a COMMIT is issued, we need Materialized View Logs on both base tables:

create materialized view log on t with sequence, primary key including new values
/
create materialized view log on lookup with sequence, primary key including new values
/ 

The Materialized View definition will be

create materialized view t_lookup_mv
build immediate
refresh force on commit
as
select l.ind_active
from t
  , lookup l
where l.id = t.lu_id
/ 

This way we will get a record in the Materialized View whenever the T-table uses a record in our Lookup Table. The final thing we need is a Check Constraint on this Materialized View, because we only want Lookup records with the active_ind is “Y”:

alter materialized view t_lookup_mv
add constraint active_chk check (ind_active = 'Y');

Just to check that the Materialized View does what it is supposed to do:

update lookup
set ind_active = 'N'
where rownum <= 1
/
commit;

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (ALEX.ACTIVE_CHK) violated 

That seems to work. But as you can see, you need quite a lot of code to implement such a simple rule. The same Business Rule can be implemented in Oracle 11g a lot simpler, with less code.

Virtual Columns

This is the way we're going to implement the rule:

1. Add a Virtual Column to the Lookup table
2. Create a Foreign Key referencing the Virtual Column

And that's all there is to it. Let's take a look at the code. First of all the Virtual Column.

Virtual Columns are a new feature of the Oracle 11gR1 database. Using this feature you can create an extra column in the table that is based on an expression.

active_fk as (case when ind_active = 'Y' then id end) unique 

The values in this Virtual Column are only shown when the IND_ACTIVE column has the value of "Y". The value that is shown is the primary key of the base table. Querying the LOOKUP table, reveals the content of the ACTIVE_FK Virtual Column:

SQL> select *
2    from lookup
3  /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         Y          2

Changing the IND_ACTIVE column to "N" will remove the value from the ACTIVE_FK column:

SQL> update lookup
2 set ind_active = 'N'
3 where id = 2
4 /

1 row updated.

SQL> select *
2 from lookup
3 /

     ID CODE       VALUE                I  ACTIVE_FK
---------- ---------- -------------------- - ----------
      1 One        First Value          Y          1
      2 Two        Second Value         N

SQL> rollback;

Because we are going to use the Virtual Column as referenced by a Foreign Key we are going to make it UNIQUE as well.

Now for the table which uses the records from our Lookup Table:

create table t
(id number primary key
,lu_id number
);
alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
/

The foreign key is this case is not on the primary key, but on the Virtual Column instead.

Let's add some data to the table and try it out.

insert into t values (1, 1);
insert into t values (2, 2);

update lookup
set ind_active = 'N'
where rownum <= 1
/
update lookup
 * ERROR at line 1: ORA-02292: integrity constraint (ALEX.T_LOOKUP_FK) violated - child record found

And there you have it. It's not possible to use deactivated records anymore.

Check when you COMMIT?

Note the difference though, the Materialized View method is checked when you commit your transaction. The Virtual Column method checks it immediately. If you want the Virtual Column to have similar to the Materialized View method, change the foreign key:

alter table t
add constraint t_lookup_fk foreign key (lu_id) references lookup (active_fk)
deferrable initially deferred
/

Note the last sentence, deferrable initially deferred, this tells Oracle to not validate the constraint immediately but to postpone it until the transaction is ended.