Wednesday, 1 June 2016

Improved Defaults in Oracle Database 12c

The new DEFAULT clause provides better values for getting started and better performance.

Gordon has just completed some self-study Oracle Application Express training and is eager to start building applications to improve his skills and, more importantly, to rapidly deliver value to the business stakeholders in the company where he works.

He knows that Oracle Application Express is a database-centric development tool, so he wants to make sure he takes as much advantage as possible of the features available to him within the database so that the applications he builds will be more robust; will require less coding; and could even be ported to other front-end technologies, should the need arise. The applications will be simply a window into his data, but the data relationships, integrity, and controls will be within the database, alongside the data.

From his Oracle Application Express training, Gordon knows there are advantages to ensuring that all of the database tables are keyed with surrogate keys, either instead of, or in addition to, any natural keys. Using surrogate keys will also reduce the amount of coding required in Oracle Application Express, and Gordon aims to deliver applications rapidly, so he wants to minimize the amount of coding he has to do.

However, his data-centric vision for building applications dictates that even without Oracle Application Express as an interface to the data, the integrity and function of the data in the database should remain intact. So Gordon needs to ensure that his surrogate keys are populated even if Oracle Application Express is not the application interface. And that means he will need a database trigger for every surrogate key he wants to populate. For Gordon, this seems like a contradiction. To achieve less coding in Oracle Application Express, he will have to do more coding in the database. But not wanting to compromise his vision, Gordon builds triggers for each of his new database tables. Here’s one example trigger:

SQL> create or replace
    trigger TRG_SALES_SURROGATE_KEY
    before insert on SALES
    for each row
    begin
      :new.SALE_SEQ := SEQ_SALES.nextval;
    end;
    /

Trigger created.

After a few days of development and testing, Gordon’s first Oracle Application Express module, a sales analysis tool, is deployed into production and is a great success with the users at his company. So much so that in the weeks after the deployment, several of the company’s existing applications have been extended to interface with his new database tables. Gordon’s foresight in not assuming that his Oracle Application Express modules would be the only interface to his new tables has proven well justified.

Trigger Overhead


But Gordon’s success has also created some new challenges. The popularity of the application has led to a request to load all the company’s daily sales data into Gordon’s database tables—millions of records per day. Gordon does some preliminary testing in a nonproduction environment to gauge the impact of loading the data:

SQL> insert /*+ APPEND */ into SALES
    select rownum, ...
    from
     ( select 1 from dual connect by level <= 10000 ),
     ( select 1 from dual connect by level <= 1000 )
    /

10000000 rows created.

Elapsed: 00:06:37.75

Just under seven minutes seems quite reasonable for loading 10 million rows, but Gordon is curious to see if the sequence population trigger is adding to the overhead. So he repeats the loading test with the trigger disabled:

SQL> alter trigger SALES_SURROGATE_KEY disable;

Trigger altered.

SQL> insert /*+ APPEND */ into SALES
  2  select rownum, ...
  3  from
  4   ( select 1 from dual connect by level <= 10000 ),
  5   ( select 1 from dual connect by level <= 1000 )
  6  /

10000000 rows created.

Elapsed: 00:00:04.73

Gordon is astounded by the difference. From nearly seven minutes down to less than five seconds! Without the trigger, Gordon could easily load the historical data with almost no disruption to the running application. But conversely, the trigger cannot be disabled without the threat of a major disruption, namely that a surrogate key value wouldn’t be generated automatically—which could mean that the application would crash or that data would be corrupted. Although Gordon has maintenance control over his Oracle Application Express code, he has no control over, or even awareness of, the other company applications that are now all interfacing with his data. Those applications might need the trigger, they might not—but Gordon cannot assume that either is true. He unfortunately just has to accept the slow performance.

Other problems have also surfaced. One of the applications outside Gordon’s control implements record update logic via a delete-then-insert strategy, and the application is crashing whenever it attempts such an operation. When the application attempts to reinsert the record with the previous surrogate key value, Gordon’s trigger automatically overwrites it with the next sequence value. Here’s an example of the problem observed by the crashing application:

SQL> delete from sales where sale_seq = 456;

1 row deleted.

SQL> insert into sales (sale_seq, invoice_num, .... )
  2  values (456, 720001, ... );

1 row created.

SQL> select * from sales where sale_seq = 456;

no rows selected

When the application attempts to retrieve a row it just inserted, it can no longer find it. Gordon rushes an emergency fix into production to all the surrogate key generation triggers for his database tables, so that they assign a sequence value only when no primary key value is provided on insertion. Here’s an example:

SQL> create or replace
    trigger SALES_SURROGATE_KEY
    before insert on SALES
    for each row
    when (new.SALE_SEQ is null)
    begin
      :new.SALE_SEQ := SEQ_SALES.nextval;
    end;
    /

Trigger created.

But Gordon is becoming less confident that the triggers will not cause other issues as time goes on. He spends the weekend digging through the Oracle Database documentation, looking for a better solution.

New DEFAULT Clause Options


By Monday he has the solution he wants. In Oracle Database 12c, a suite of improvements has been implemented around the DEFAULT clause for table columns. Of particular interest to Gordon is that a sequence value can now be assigned as the default value for a column. With one simple command, Gordon has removed the need for his triggers:

SQL> alter table SALES modify SALE_SEQ default SEQ_SALES.nextval;

Table altered.

Gordon is also delighted to discover that the DEFAULT clause implementation is not simply equivalent to having a “hidden” trigger somewhere within the Oracle Database kernel, as evidenced by the fact that nightly loads of sales data are no longer slow. They are as now just as fast as his tests were when there was no trigger at all. He now has faster performance—and no sequence assignment triggers to maintain. Armed with his knowledge of the Oracle Database 12c new DEFAULT clause features, he looks for other opportunities to improve the application.

Looking through the SALES table, he discovers an unfortunate oversight. The INVOICE_DATE column, which should have been mandatory, was never constrained as such. Gordon hadn’t noticed the omission, because his Oracle Application Express code correctly provided a value for INVOICE_DATE in all cases. But the presence of some null values indicates that some of the external applications are not providing a value for INVOICE_DATE. Gordon adds a DEFAULT clause, thinking that this will resolve the issue:

SQL> alter table SALES modify INVOICE_DATE default sysdate;

Table altered.

However, a few days later, when Gordon examines the SALES table, he discovers that rows are still being created with a null INVOICE_DATE. He’s puzzled by how this could possibly be the case, given that there is now a default value assigned in the definition of the column. Unable to see the source code for the external applications, he performs some database tracing, using DBMS_MONITOR, to see what could be the cause. When Gordon examines the resulting trace file, the problem becomes apparent. Within the trace file is a typical INSERT statement from one of the external applications:

insert into SALES ( INVOICE_NUM, INVOICE_DATE, QUANTITY, ... )
values ( :invoice_num, null, :quantity, ... );

Even though a DEFAULT clause is present, the default value is not being respected, because the application code is explicitly assigning a null value to the INVOICE_DATE. But Gordon knows that the new DEFAULT features in Oracle Database 12c can also handle this scenario. The DEFAULT ON NULL clause will force a default value into a column even when a null value has been explicitly included in the INSERT statement. Gordon makes a slight adjustment to his column definition and makes an interesting discovery when he tries to apply the change:

SQL> alter table SALES modify INVOICE_DATE default on null sysdate;
ERROR at line 1:
ORA-02296: cannot enable - null values found

Adding a DEFAULT ON NULL clause means that the database will automatically convert the column to being also constrained as NOT NULL. So Gordon first must correct the missing values for INVOICE_DATE. But once he has completed the data correction, the DEFAULT ON NULL clause can be successfully added to permanently stop the problem:

SQL> alter table SALES modify INVOICE_DATE default on null sysdate;

Table altered.