Using Indexes for your business logic

Intruction

In a recent post I wrote about indexing of NULL values. We can utilize the fact that NULL values are not indexed under normal circumstances to implement our business logic inside the data model itself. You wonder what I mean by that? I will show you in a short example.

The business rule

In our example application we manage contracts between different parties at a time. The contracts can be active or canceled. Only one active contract is allowed between two parties. Canceled contracts do not matter.
Many times I see those logic being implemented using triggers or code inside the application. But how do you handle concurrency and locking? How do you handle changes that check old versions of the data while someone else is also doing changes? Why not use the database to handle concurrency and locking? The database should now it best.

The data model

These are the two simple tables, should be self-explaining:

SQL> create table parties (
  2    id     number,
  3    party_name varchar2(30),
  4    constraint pk_parties primary key (id)
  5  );

Table created.

SQL> create table contracts (
  2    party1_id number,
  3    party2_id number,
  4    contract_state varchar2(10) check ( contract_state in ('enabled', 'canceled') ),
  5    constraint fk_party1_id foreign key (party1_id) references parties(id),
  6    constraint fk_party2_id foreign key (party2_id) references parties(id)
  7  );

Table created.

Now I need some simple test data:

SQL> insert into parties values(1, 'Party 1');

1 row created.

SQL> insert into parties values(2, 'Party 2');

1 row created.

SQL> insert into parties values(3, 'Party 3');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from parties;

        ID PARTY_NAME
---------- ------------------------------
         1 Party 1
         2 Party 2
         3 Party 3

The implementation

Now we could start inserting the contracts into the mapping table. But as of now we could insert anything:

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

According to our business rule these two entries must not exist at the same time.

SQL> rollback;

Rollback complete.

We implement the rule by creating a unique index which only contains value pairs of enabled contracts. IDs of canceled contracts will be mapped to NULL which suppresses indexing of these value pairs and thus uniqueness will not be checked by the database.

SQL> create unique index uq_contracts_business_rule on contracts (
  2    case when contract_state='enabled' then party1_id else null end,
  3    case when contract_state='enabled' then party2_id else null end
  4  );

Index created.

Let’s check how and if that works:

SQL> insert into contracts values (1, 2, 'enabled');

1 row created.

Ok, this is the first row, nothing special.

SQL> insert into contracts values (1, 3, 'enabled');

1 row created.

This is a contract between another value pair, inserting is permitted.

SQL> insert into contracts values (1, 2, 'canceled');

1 row created.

That value pair has already a contract, but this contract is in ‘canceled’ state, so it does not matter according to our business rule. This is achieved by the CASE expression inside the index. If the state is not ‘enabled’ the CASE expression returns NULL which in turn means that no index entry is generated and thus also uniqueness is not being checked.

SQL> insert into contracts values (1, 2, 'enabled');
insert into contracts values (1, 2, 'enabled')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UQ_CONTRACTS_BUSINESS_RULE) violated

Now the same value pair with ‘enabled’ state. The CASE expression inside the index now returns the IDs, the database tries to add row to the index and fails because an identical value pair is already indexed. That means our implementation of that business rule works 🙂

Conclusion

This simple example outlines the implementation of complex business rules inside the data model itself. You don’t need to take care of locking or handling concurrency issues or anything else that could happen. The database does it for you. You can also create your own functions inside the database that control indexing of values which allows you to build more complex rules. Just be creative. And the best, it’s all for free, function based indexes are part of Standard Edition of Oracle Database.

Advertisements

ORA-14098 – Part 2: Primary Key vs. Unique Index

A couple of days ago I wrote about ORA-14098 during an “exchange partition” operation. The workaround was to simply disable the primary key constraint on both tables. But as I learned, this has some trade-offs that I want to show you.

Fact 1: If a primary constraint on a table is created without having a matching index in place, this index is automatically created by the database. Knowing that, one can define index attributes with the “using index” clause.

Fact 2: If a matching index is already in place at the time when a primary constraint is added to a table, the database does not create another index but uses the existing one to enforce the primary key constraint.

Given the sitation described in “Fact 1”, the database drops the corresponding index when disabeling the primary key constraint:

SQL> create table with_pk(
  2    id number
  3  );

Table created.

SQL> alter table with_pk add constraint c_with_pk primary key (id);

Table altered.

SQL> insert into with_pk values (1);

1 row created.

SQL> insert into with_pk values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> col index_name format a10
SQL> select index_name, status from user_indexes where table_name='WITH_PK';

INDEX_NAME STATUS
---------- --------
C_WITH_PK  VALID

SQL> alter table with_pk modify constraint c_with_pk disable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITH_PK';

no rows selected

SQL> alter table with_pk modify constraint c_with_pk enable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITH_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITH_PK       VALID

Not nice, the index gets dropped and re-created during the disable/enable of the primary key constraint. There were only two rows in my example, but there can be billions of rows in real-life scenarios where index creation can consume a lot of time.

So let’s have a look at “Fact 2”, the behaviour is quite different:

SQL> create table without_pk(
  2    id number
  3  );

Table created.

SQL> create unique index c_without_pk on without_pk(id);

Index created.

SQL> alter table without_pk add constraint c_without_pk primary key (id);

Table altered.

SQL> insert into without_pk values (1);

1 row created.

SQL> insert into without_pk values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID


SQL> col index_name format a15
SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID

SQL> alter table without_pk modify constraint c_without_pk disable;

Table altered.

SQL> select index_name, status from user_indexes where table_name='WITHOUT_PK';

INDEX_NAME      STATUS
--------------- --------
C_WITHOUT_PK    VALID

SQL> alter table without_pk modify constraint c_without_pk enable;

Table altered.

In this case the index survived the disable/enable procedure of the primary key constraint.

What does that mean for my exchange partition procedure? With “Fact 1” I can exchange partitions when disabeling the primary key constraints in the first place. But that means my indexes are gone. On the other hand, with “Fact 2” I still can not exchange partitions even when the primary key constraints are disabled. The corresponding indexes need to be dropped to successfully exchange partitions. That is a lose-lose situation.

All this applies to Oracle RDBMS 12.1.0.2

SQL> select banner from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

I am currently in the process of writing a loading procedure for a large table which is partitioned. The idea behind that is to change the physical structure of the table including change of partitioning method, LOB storage parameters and change of a column from being a real column filled by a default value to being a virtual column.

Basically I created the table with the new structure and then create interim tables per partition which then will become part of the new table by a simple “exchange partition” command.

But what happened is this:

SQL> alter table NEW_TABLE exchange partition SOME_PARTITION with table INTERIM_TABLE including indexes;
alter table NEW_TABLE exchange partition SOME_PARTITION with table INTERIM_TABLE including indexes
                                                            *
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION


Elapsed: 00:00:00.02

A quick look at all the indexes showed no difference, everything was pretty equal. So I switched to My Oracle Support and found ORA-14098 Even When Indexes and Constraints on the Exchange Table Match the Partitioned Table (Doc ID 1612345.1) which pretty much met my issue.

Since there is no fix for this issue as of now, I implemented the workaround which went fine:

SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK disable;

Table altered.

Elapsed: 00:00:00.26
SQL> alter table INTERIM_TABLE modify constraint INTERIM_TABLE_PK disable;

Table altered.

Elapsed: 00:00:00.08
SQL> alter table NEW_TABLE exchange partition IW05 with table INTERIM_TABLE including indexes;

Table altered.

Elapsed: 00:00:00.61

But the tricky stuff follows. When re-enabling the constraint, Oracle would validate it by scanning the whole table for inconsistencies. That would take a couple of hours if not days since my table is about 5T in size… So I use “enable novalidate” and then “rely” in the next step to tell the database that it can rely on the constraint even though it was not validated. That does the trick.

SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK enable novalidate;

Table altered.

Elapsed: 00:00:00.06
SQL> alter table NEW_TABLE modify constraint NEW_TABLE_PK rely;

Table altered.

Elapsed: 00:00:00.02

Oracle 12.1.0.2 Standard Edition 2 released

Yesterday Oracle released Database 12.1.0.2 SE2 (Download at OTN). There are some major changes, one can only use at most 2 sockets with a maximum of 8 cores per socket. It is allowed to use RAC witch in turn means, that each server must not have more than 1 socket. Hard partitioning using Oracle VM is allowed. 

There are many questions regarding licensing such as

  • What about migrating old SE licenses?
  • What happens to SE RAC installations utilizing 4sockets?
  • The download is for SE2 explicitly. Can I use this with old SE/SE. one license or do I need to buy new SE2?

We will see, how Oracle answers all these questions. All in all it is a major change that may not improve customer satisfaction. 

Update 02-SEP-2015

Some answers to the above questions:

  • SE One: Need to pay additional fee to get SE2
  • SE utlizing 2 sockets: No additional cost
  • SE RAC with 2 sockets overall: No additional cost
  • SE RAC with 4 sockets overall: need to reduce sockets OR upgrade to EE