Laurent Schneider

I am data-modeling those days.

Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) must be selectable and editable. Someone, the little ones are views of the big one, or the big one is a view of the little ones.

Plan 1: there is one big table and many small views

create table global(
  type varchar2(15),
  id number,
  description varchar2(10),
  primary key(type,id));
create or replace view fruit as 
  select id,description 
  from global 
  where type='fruit';
create or replace view meat as 
  select id,description 
  from global 
  where type='meat';

plan 2: one view which union all all little tables

create table fruit(
  id number,
  description varchar2(10), 
  primary key(id));
create table meat(
  id number,
  description varchar2(10), 
  primary key(id));
create or replace view global as 
  select 'fruit' type,id,description 
  from fruit 
  union all
  select 'meat',id,description 
  from meat;

Is this fine? As long as I only select rows, both are possible. However, a select * from fruit will take much longer if fruit is a view and the rows are retrieved from a huge table, probably per Index scan.

In the solution 2, I keep small lookup tables, and it should be faster to retrieve data from those. However, the UNION ALL view is not updatable.

In solution 1, the small views seem updatable, but it will not work.

Let’s see

SQL> create table global(
  2    type varchar2(15),
  3    id number,
  4    description varchar2(10),
  5    primary key(type,id));

Table created.

SQL> create or replace view fruit as
  2    select id,
  3      description
  4    from global
  5    where type='fruit';

View created.

SQL> insert into fruit values(1,'Mango');
insert into fruit values(1,'Mango')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AUDBA"."GLOBAL"."TYPE")

I cannot tell the view to default the type to fruit when inserting in fruit

In plan 2, the view is not updatable at all

SQL> create table fruit(
  2    id number,
  3    description varchar2(10),
  4    primary key(id));

Table created.

SQL> 
SQL> create table meat(
  2    id number,
  3    description varchar2(10),
  4    primary key(id));

Table created.

SQL> 
SQL> create or replace view global as
  2    select 'fruit' type,id,description
  3    from fruit
  4    union all
  5    select 'meat',id,description
  6    from meat;

View created.

SQL> 
SQL> insert into global values(
  2    'meat',1,'beef');
insert into global values(
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

So the solution is called INSTEAD OF trigger. An instead of trigger is a very special trigger specific to views. It will make all views updatable, how magic!

As a DBA, I am not very confident with triggers, because the developer is doing the consistency check, no more the Oracle Engine. It will therefore be much less performant and it may contain errors. Here I write a trigger for each case. They certainly have bugs. I am myself not convinced by using triggers at all. Those kind of DML home-made rewrites are just too dangerous, but what else can I do…

OK, plan 1: one instead of trigger for every single view

create or replace trigger fruit_insteadof
  instead of insert or update or delete on fruit
begin
  if inserting then
    insert into global 
      values('fruit',:new.id,:new.description);
  elsif deleting then
    delete from global 
    where type='fruit' 
      and id=:old.id 
      and description=:old.description;
  elsif updating then
    update global 
      set type='fruit', 
        id=:new.id,
        description=:new.description
     where type='fruit' 
       and id=:old.id 
       and description=:old.description;
  end if;
end;
/

Plan 2: One instead of trigger for the global view

create or replace trigger global_insteadof
  instead of insert or update or delete on global
begin
  if inserting then
    if (:new.type='fruit') then
      insert into fruit values(
        :new.id,:new.description); 
    elsif (:new.type='meat') then
      insert into meat values(
        :new.id,:new.description);
    else
      raise_application_error(-20001, 
        'Trigger cannot insert into '}µçææWrçG—R“²Ð –VæB–c°Ð¢VÇ6–bFVÆWF–ærF†VàТ–bƒ¦öÆBçG—SÒvg'V—Br’F†VàТFVÆWFRg'V—Bv†W&RТ–CÓ¦öÆBæ–BТæBFW67&—F–öãÓ¦öÆBæFW67&—F–öã°Ð¢VÇ6–bƒ¦öÆBçG—SÒvÖVBr’F†VàТFVÆWFRÖVBv†W&RТ–CÓ¦öÆBæ–BТæBFW67&—F–öãÓ¦öÆBæFW67&—F–öã°Ð¢VÇ6PТ&—6UöÆ–6F–öåöW'&÷"‚Ó#ÂТuG&–vvW"6ææ÷BFVÆWFRg&öÒwÇæöÆBçG—R“²Ð¢VæB–c°Ð¢VÇ6–bWFF–ærF†VàТ–bƒ¦æWrçG—RÓ¦öÆBçG—R’F†VàТFVÆWFRg&öÒvÆö&ÂТv†W&RG—SÓ¦öÆBçG—RТæB–CÓ¦öÆBæ–BТæBFW67&—F–öãÓ¦öÆBæFW67&—F–öã°Ð¢–ç6W'B–çFòvÆö&ÂfÇVW2€Ð¢¦æWrçG—RææWræ–BææWræFW67&—F–ö⓰ТVÇ6–bƒ¦æWrçG—SÒvg'V—Br’F†VàТWFFRg'V—B6WBТ–CÓ¦æWræ–BÀТFW67&—F–öãÓ¦æWræFW67&—F–öàТv†W&R–CÓ¦öÆBæ–BТæBFW67&—F–öãÓ¦öÆBæFW67&—F–öã°Ð¢VÇ6–bƒ¦æWrçG—SÒvÖVBr’F†VàТWFFRÖVB6WBТ–CÓ¦æWræ–BÀТFW67&—F–öãÓ¦æWræFW67&—F–öàТv†W&R–CÓ¦öÆBæ–BТæBFW67&—F–öãÓ¦öÆBæFW67&—F–öã°Ð¢VÇ6PТ&—6UöÆ–6F–öåöW'&÷"‚Ó#ÂТuG&–vvW"6ææ÷BWFFRwÇæöÆBçG—R“²Ð¢VæB–c°Ð¢VæB–c°Ð¦VæC°Ð¢ðÐ

I am not at all convinced about the data consistency in such an approach.

Well, I will try to push a solution with no GLOBAL table. We probably do not need it.

This entry was posted on Monday, April 30th, 2007 at 12:13 and is filed under sql, data modeling. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl