Not overlapping (MV approach)

Presented earlier a not overlapping function based unique indexes approach. In this post I am using a materialized view and a unique constraining that. Several commits seen in here because MV approach makes constraints kind of deferrable. The MV is refreshed at commit phase. As the dirty hack function based unique indexes on the table itself are violated straight at the insert.

DROP TABLE Z CASCADE CONSTRAINTS PURGE;

DROP TABLE YEARS CASCADE CONSTRAINTS PURGE;

DROP MATERIALIZED VIEW Z_MV;

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
             , CONSTRAINT FRO2000 CHECK (2000 < VALIDFROM)
             , CONSTRAINT TIL2050 CHECK (VALIDTILL <= 2050)
             , CONSTRAINT FROTIL CHECK (VALIDFROM <= VALIDTILL)
             );


CREATE TABLE YEARS AS 
SELECT 2000+LEVEL TIM FROM DUAL CONNECT BY LEVEL < (2051-2000)
;

CREATE MATERIALIZED VIEW LOG ON Z WITH ROWID
;

CREATE MATERIALIZED VIEW LOG ON YEARS WITH ROWID
;

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z INNER JOIN YEARS T ON VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

With Oracle 11.1.0.7 and 11.2.0.1 getting
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

No worries. It is about inner join syntax not so widely supported with MVs.

CREATE MATERIALIZED VIEW Z_MV REFRESH FAST ON COMMIT AS
SELECT Z.ROWID ZRID,T.ROWID TRID,Z.Z,T.TIM 
  FROM Z,YEARS T 
 WHERE VALIDFROM < T.TIM AND T.TIM <= VALIDTILL 
;

ALTER TABLE Z_MV ADD CONSTRAINT Z_MV_U UNIQUE (Z,TIM);

CREATE INDEX Z_MV_ZRID_IDX ON Z_MV(ZRID);

ZRID indexed to give the optimizer at least a possibility to do small updates "fast". More about the issue may be read from Alberto Dell'Era's Oracle blog

INSERT INTO Z VALUES(1,2001,2011);

INSERT INTO Z VALUES(1,2011,2011);

COMMIT;

INSERT INTO Z VALUES(1,2010,2012);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

INSERT INTO Z VALUES(2,2049,2050);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED



INSERT INTO Z VALUES(2,2010,2012);

COMMIT;

INSERT INTO Z VALUES(2,2001,2049);

COMMIT;

SQL ERROR: ORA-12008: ERROR IN MATERIALIZED VIEW REFRESH PATH
ORA-00001: UNIQUE CONSTRAINT (RAFU.Z_MV_U) VIOLATED


INSERT INTO Z VALUES(2,2014,2017);

COMMIT;

SELECT * FROM Z ORDER BY Z, VALIDFROM;

1 2001 2011
1 2011 2011
2 2010 2012
2 2014 2017
2 2049 2050

SELECT COUNT(*) FROM Z_MV;

16

Rely constraint validated mess

Not so happy with the html support pages. Could not create a SR. Had to go back to flash pages.

Today's issue is about a constraint that is in validated state. Oracle documentation says that if you use rely constraints, you should know what you are doing. Maybe you did not know and want to go back to norely mode. Here is an example what you should not do.

SQL> create table pa (pa_id number constraint pa_pk primary key);

Table created.

SQL>
SQL> insert into pa
  2  select level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> create table ch(ch_id number constraint ch_pk primary key
  2                , pa_id not null constraint ch_pa_fk references pa
  3                )
  4  ;

Table created.

SQL>
SQL> insert into ch select level ch_id, level pa_id from dual connect by level < 3;

2 rows created.

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk rely;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
DISABLED NOT VALIDATED RELY

SQL>
SQL> insert into ch select level+10 ch_id, level+10 pa_id from dual connect by level < 3;

2 rows created.

SQL> select * from pa;

     PA_ID
----------
         1
         2

SQL> select * from ch;

     CH_ID      PA_ID
---------- ----------
         1          1
         2          2
        11         11
        12         12

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk validate;

Table altered.

SQL>
SQL> --Why was that possible?
SQL>
SQL> alter table ch modify constraint ch_pa_fk norely;

Table altered.

SQL>
SQL> alter table ch modify constraint ch_pa_fk enable validate;

Table altered.

SQL>
SQL> select status,validated,rely from user_constraints where constraint_name = 'CH_PA_FK';

STATUS   VALIDATED     RELY
-------- ------------- ----
ENABLED  VALIDATED

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

no rows selected

SQL>
SQL> alter table ch modify constraint ch_pa_fk disable novalidate;

Table altered.

SQL>
SQL> select *
  2    from ch c
  3    where not exists (select null from pa p where p.pa_id = c.pa_id)
  4  ;

     CH_ID      PA_ID
---------- ----------
        11         11
        12         12

SQL> alter table ch modify constraint ch_pa_fk enable validate;
alter table ch modify constraint ch_pa_fk enable validate
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYSTEM.CH_PA_FK) - parent keys not found

Going back from rely mode to norely. Go first to norely and validate after.

Check constraints and AND

Rob van Wijk has described nicely check constraints in inheritance conversion to a relation using single table implementation. The issue I planned to write about some day. Similar approach also to checks depending on state/status fields.

Denormalize safely

For some reason there is a need to denormalize values from p table to c table.
How to ensure that denormalized values are the same that original values in p?

drop table c cascade constraints purge;

drop table p cascade constraints purge;

create table p(p_id number(10) primary key
             , p_name varchar2(200) not null);

create table c(c_id number(10) primary key
             , p_id constraint c_p_fk references p
             , c_value varchar2(200) not null);

The denormalization.

alter table c add (p_name varchar2(200) not null);

alter table p add unique (p_name,p_id);

alter table c add constraint c_p_2fk foreign key(p_name,p_id) references p(p_name,p_id);

Make the c_p_2fk deferrable if there is a need to update the denormalized values. To satisfy unindex:

create index c_p_2fk_idx on c(p_id,p_name);