Assertions versus constraints
In a previous blog post I described the new Assertions feature of Oracle Database 26ai. This feature makes it possible to define constraints between different tables. I also gave two examples of business rules that could be enforced by the new Assertions. But, as Alex Nuijten pointed out to me, the first example could also be enforced by a table constraint. In this blog post I will describe Alex' solution with a table constraint, and I will elaborate on the performance difference between constraints and assertions.
Business Rule 1
In the example I have a table of customers, and a table of locations. Each customer can have multiple locations. Locations can be of type Invoice or Delivery. The business rule states that a customer can only have one invoice location.
A simple unique constraint on customer ID and location type cannot be used. After all, the customer can have multiple delivery locations. The solution is to define a virtual column on the locations table, which is equal to the customer ID when the type is Invoice, and null when the type is Delivery. A unique constraint on this virtual column would then not be effective for delivery locations, because the column value is null.
So, I drop the assertion, and implement the new virtual column and constraint:
drop assertion ctr_ck_one_invoice;
alter table locations add (
lcn_id_ctr number as (case when location_type = 'INVOICE' then ctr_id end)
);
alter table locations add constraint lcn_invoice_uk unique (lcn_id_ctr);
Now I test the new constraint:
SQL> insert into locations(ctr_id,address,location_type) values (1,'Culemborg','INVOICE');
1 rij is gemaakt.
SQL> insert into locations(ctr_id,address,location_type) values (1,'Utrecht','INVOICE');
insert into locations(ctr_id,address,location_type) values (1,'Utrecht','INVOICE')
*
FOUT in regel 1:
.ORA-00001: Unieke beperking (BLOG.LCN_INVOICE_UK) geschonden voor tabel
BLOG.LOCATIONS met kolommen (LCN_ID_CTR)
ORA-03301: (ORA-00001 details) rij met kolomwaarden (LCN_ID_CTR:1) bestaat al.
Help: https://docs.oracle.com/error-help/db/ora-00001/
That works, but can I still add multiple delivery locations?
SQL> insert into locations(ctr_id,address,location_type) values (1,'Culemborg','DELIVERY');
1 rij is gemaakt.
SQL> insert into locations(ctr_id,address,location_type) values (1,'Utrecht','DELIVERY');
1 rij is gemaakt.
Indeed, I can.
Performance of constraints and assertions
So, now I know the business rule can be implemented by either a table constraint or an assertion. But is one better than the other? For that, I will measure the time that it takes to insert 10,000 rows into the locations table. I will do that for a table without any constraints, with a table constraint as outlined before, and with an assertion as I described in the previous blog post.
The code I use is the following:
set serveroutput on
declare
l_tijd timestamp;
begin
l_tijd := systimestamp;
for i in 1..10000 loop
insert into locations(ctr_id,address,location_type) values (1,'Culemborg','DELIVERY');
end loop;
dbms_output.put_line('10,000 rows inserted in '||(systimestamp - l_tijd));
end;
/
I will drop all assertions and remove the unique constraint and the virtual column from the locations table. Then I run the code:
10,000 rows inserted in +000000000 00:00:00.560392000
PL/SQL-procedure is geslaagd.
10,000 rows are inserted in about half a second.
Now I add the virtual column and the unique constraint on the column, and run the code again:
10,000 rows inserted in +000000000 00:00:00.488996000
PL/SQL-procedure is geslaagd.
Still about half a second. Of course, the execution time varies if I execute the statement multiple times, but it is always less than a second. It is safe to conclude that adding the table constraint does not have a huge impact on performance.
Then I create the ctr_ck_one_invoice assertion again, and then I run the 10,000 rows insert:
10,000 rows inserted in +000000000 00:00:17.457867000
PL/SQL-procedure is geslaagd.
The time to insert 10,000 rows is now increased to almost 20 seconds. It may be concluded that using assertions does have an impact on performance.
Conclusion
Assertions are a valuable addition to the database. With assertions it is possible to enforce business rules that involve multiple tables. Care should be taken however that assertions are not used where table constraints would be sufficient, because the performance would be considerably worse. A general rule of thumb is, when a business rule only involves columns of one table (as in my case the customer ID foreign key and the location type in the locations table), a table constraint can be used. A lesson I have learned is that a virtual column can also be a valuable tool for data integrity.
Comments
Join the discussionNo comments yet. Be the first to comment.





