Business Rule: Only One Clerk per Department – 11g Style 20188367001

Business Rule: Only One Clerk per Department – 11g Style

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

   1: select *
   2:   from emp
   3:  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:

   1: create index upper_name
   2: 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”:

   1: create UNIQUE index one_clerk_per_deptno
   2: on emp (case job 
   3:         when 'CLERK' then deptno 
   4:         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:

   1: alter table emp
   2: add one_clerk_per_dept as (case 
   3:                            when job = 'CLERK' 
   4:                            then deptno 
   5:                            end)
   6: /

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.

   1: SQL> select job
   2:   2       , one_clerk_per_dept
   3:   3    from emp
   4:   4  /
   5:  
   6: JOB       ONE_CLERK_PER_DEPT
   7: --------- ------------------
   8: CLERK                     20
   9: SALESMAN
  10: SALESMAN
  11: MANAGER
  12: SALESMAN
  13: MANAGER
  14: MANAGER
  15: ANALYST
  16: PRESIDENT
  17: SALESMAN
  18: CLERK                     20
  19: CLERK                     30
  20: ANALYST
  21: 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…

   1: SQL> update emp
   2:   2     set job = 'NOCLERK'
   3:   3   where job = 'CLERK'
   4:   4     and rownum = 1
   5:   5  /
   6:  
   7: 1 row updated.
   8:  
   9: SQL> alter table emp
  10:   2  add constraint one_clerk_uk unique (one_clerk_per_dept)
  11:   3  /
  12:  
  13: Table altered.
  14:  
  15: SQL> update emp
  16:   2     set job = 'CLERK'
  17:   3   where job = 'NOCLERK'
  18:   4  /
  19: update emp
  20: *
  21: ERROR at line 1:
  22: 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.

   1: SQL> select column_name
   2:   2    from user_ind_columns
   3:   3   where index_name = 'ONE_CLERK_PER_DEPT'
   4:   4  /
   5:  
   6: COLUMN_NAME
   7: ---------------------------------------------------------
   8: SYS_NC00010$
   9:  
  10: SQL> select job
  11:   2       , SYS_NC00010$
  12:   3    from emp
  13:   4  /
  14:  
  15: JOB       SYS_NC00010$
  16: --------- ------------
  17: NOCLERK
  18: SALESMAN
  19: SALESMAN
  20: MANAGER
  21: SALESMAN
  22: MANAGER
  23: MANAGER
  24: ANALYST
  25: PRESIDENT
  26: SALESMAN
  27: CLERK               20
  28: CLERK               30
  29: ANALYST
  30: 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.

3 Comments

  1. Toon Koppelaars December 20, 2008
  2. Alex Nuijten December 20, 2008
  3. John Flack December 19, 2008