1 Reply Latest reply: Nov 16, 2012 12:20 PM by bencol RSS

    Constraint in Materialized View misbehave

    luisvil
      Enviroment
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      s.o : windows 7. on laptop Intel core i7.
      --------------------------------------------------------------------------------------------------------------------------

      create table product_type
      ( product_type_id varchar2(3) constraint product_type_pk primary key,
      product_type_name varchar2(100) constraint product_type_name_nn not null,
      move_inv varchar2(1) default 'Y' constraint product_type_move_inv_nn not null,
      generate_abc varchar2(1) default 'N' constraint product_type_generate_abc_nn not null,
      constraint product_type_move_inv_check check (move_inv in ('Y','N')),
      constraint product_type_gen_abc_check check (generate_abc in ('Y','N'))
      );

      create table product_class
      ( product_type_id varchar2(3) constraint product_class_type_id_nn not null,
      product_class_id varchar2(3) constraint product_class_id_nn not null,
      product_class_name varchar2(50) constraint product_class_name_nn not null,
      inv_negative varchar2(1) default 'N' constraint product_class_inv_negative_nn not null,
      constraint product_class_pk primary key (product_type_id,product_class_id),
      constraint product_class_by_product_type foreign key (product_type_id) references product_type(product_type_id),
      constraint product_class_inv_negative_chk check (inv_negative in ('Y','N'))
      );

      create materialized view log on product_type with rowid, primary key , sequence (move_inv) including new values;
      create materialized view log on product_class with rowid, primary key , sequence (inv_negative) including new values;

      create materialized view prd_type_val_prd_class
      refresh fast on commit as
      select count(*) tot_rows
      from product_type pt
      join product_class pc
      on ( pt.product_type_id = pc.product_type_id )
      where pt.move_inv = 'N' and pc.inv_negative = 'Y';

      alter table prd_type_val_prd_class add constraint rows_in_prd_type_val_prd_class check( tot_rows < 1 );

      -- sentence 1, expected to works
      insert into product_type values('SAR','FARMA','N','N');

      commit; --- WORKS fine.

      -- sentence 2, expected to works
      insert into product_class values('SAR','LIQ','LIQUID' ,'N');

      commit; --- WORKS fine

      -- sentence 3, expected to fail due materialized view constraint
      insert into product_class values('SAR','CAP','CAPSULES','Y');

      commit; ---> SHOW the materialized view constraint Error FINE!!!!!!


      update product_type set move_inv = 'Y'; -- Update product type to allow sentence 3 to be ok

      commit; ---> works fine

      --- Re execute sentence 3 ----> success FINE!

      ---sentence 4, WORKS unexpected. --- it suppose to show the error, due materialized view constraint as did in sentence 3 (first try)
      update product_type set move_inv = 'N';

      commit; --- WORKS :( since this point ON the materialized view constraint error is show no More.... :(


      insert into product_class values('SAR','TAB','TABLETS','Y');

      commit;

      insert into product_class values('SAR','ABC','ABCZXY','Y');

      commit;

      select count(*) tot_rows
      from product_type pt
      join product_class pc
      on ( pt.product_type_id = pc.product_type_id )
      where pt.move_inv = 'N' and pc.inv_negative = 'Y';

      tot_rows
      --------------
      3

      Any Idea why is this not working as expected??? maybe I'm missing something or is this a glitch???? hope is not.

      thank you
        • 1. Re: Constraint in Materialized View misbehave
          bencol
          luis,

          I don't understand why your mv failed, but I tried it in a way I would do it, using an empty MV rather than one with a single rwos with 0 in it. i.e.:
          create materialized view prd_type_val_prd_class
          refresh fast on commit
           as
          select pt.product_type_id
                ,pc.product_type_id pc_bc_product_type_id
                ,pc.product_class_id
                ,pc.rowid pc_row_id
                ,pt.rowid pt_row_id
          from product_type pt
             , product_class pc
          where pt.bc_product_type_id = pc.bc_product_type_id 
          AND  pt.move_inv = 'N' and pc.inv_negative = 'Y';
          and changed the constraint to keep it empty:
          alter table prd_type_val_prd_class add constraint rows_in_prd_type_val_prd_class check(1=0);
          Is it seemed to pass all your tests