-- Thanks for the question regarding "updatable views", version 10.1.0

You Asked

Hi Tom,

I'm facing a problem with updateable views and instead of triggers.

I've to deal with a "big" table, more than 300 columns and hundred thousands
of records.

I would like to replace this table with a view and splitting the "big"
table in several easier to mantaine tables.

The first tests figured out an big performance boost, but I'm facing problems
with updates.

Replacing the values of a column, taking some minutes on the original
table, but breaks after more than half an hour with end of rollback space.

Is there a possibility to easy figure out which columns are to be changed
to issue a more precise update statement? Or do I have to build a case construct
to check 300 old values versus new values, or do I understand something wrong?

My idea is to have a view acting as a replacement for the "big" table
and the user/application should not face any difference of behavior
except the speed (hopefully).

The problem is that I can not change the application. So my view must be
as transparent as possible for the application. Furthermore a direct change
via sqlplus must be possible, in this case I can not predict the column(s)
going to be changed.

My intention is to get the information similar to "The columns c27, c96, c198
are to be changed", so that I can issue inside the trigger:

update table t1 set (c27, c96) = (....) where ....;
update table t2 set (c198) = (....) where ....;

instead of the following updates

update table t1 set (c1, .... , c27, ...., c96, ...., c129 ) = (....) where ....;
update table t2 set (c130, .... , c198, ..., c256 ) = (....) where ....;
update table t3 set (c257, ...., c305 ) = (....) where ....;

Following an Example with smaller tables:

--Former table which should be replaced by a view:
--
--create table t_total (
--    TableIndex    number    (10),
--    ColumnName1    char (20),
--    ColumnName2    char (20),
--    ColumnName3    char (20),
--    ColumnName4    char (20),
--    ColumnName5    char (20),
--    ColumnName6    char (20),
--    ColumnName7    char (20),
--    ColumnName8    char (20)
--);


-- new basic tables:

create table t1 (
    TableIndex number (10), ColumnName1 char (20), ColumnName2 char (20), ColumnName3 
char (20)
);
create unique index t1_TableIndex on t1 (TableIndex);

create table t2 (
    TableIndex number (10), ColumnName4 char (20), ColumnName5 char (20), ColumnName6 
char (20)
);
create unique index t2_TableIndex on t2 (TableIndex);

create table t3 (
    TableIndex number (10), ColumnName7 char (20), ColumnName8 char (20)
);
create unique index t3_TableIndex on t3 (TableIndex);

-- view:

create or replace view t_total as
select
    
t1.TableIndex,t1.ColumnName1,t1.ColumnName2,t1.ColumnName3,t2.ColumnName4,t2.ColumnName5,t
2.ColumnName6,t3.ColumnName7,t3.ColumnName8
from 
    t1, t2, t3
where
    t1.TableIndex = t2.TableIndex and t1.TableIndex = t3.TableIndex;

-- triggers:

-- I use 2 triggers, one for insert, one for update, so I don't need to distinguish
-- inside the triggers if I'm doing an insert or update.
-- Disadvantage: having two triggers to maintain.

-- insert:

CREATE OR REPLACE TRIGGER "t_total_TRIGGER_insert" 
INSTEAD OF
   INSERT 
ON t_total
   REFERENCING OLD AS old_value NEW AS new_value FOR EACH ROW
BEGIN
       INSERT INTO t1 (
    TableIndex,ColumnName1,ColumnName2,ColumnName3
    ) 
    VALUES (
    
:new_value.TableIndex,:new_value.ColumnName1,:new_value.ColumnName2,:new_value.ColumnName3

    );
       INSERT INTO t2 (
    TableIndex,ColumnName4,ColumnName5,ColumnName6
    ) 
    VALUES (
    
:new_value.TableIndex,:new_value.ColumnName4,:new_value.ColumnName5,:new_value.ColumnName6

    );
       INSERT INTO t3 (
    TableIndex,ColumnName7,ColumnName8
    ) 
    VALUES (
    :new_value.TableIndex,:new_value.ColumnName7,:new_value.ColumnName8
    );
END;
/

-- update

CREATE OR REPLACE TRIGGER "t_total_TRIGGER_update" 
INSTEAD OF
   UPDATE
ON t_total
   REFERENCING OLD AS old_value NEW AS new_value FOR EACH ROW
BEGIN
       UPDATE t1 SET (
    TableIndex,ColumnName1,ColumnName2,ColumnName3
    ) 
    = (
    select
    
:new_value.TableIndex,:new_value.ColumnName1,:new_value.ColumnName2,:new_value.ColumnName3

    from dual
    )
    where t1.TableIndex = :new_value.TableIndex;

       UPDATE t2 SET (
    TableIndex,ColumnName4,ColumnName5,ColumnName6
    ) 
    = (
    select
    
:new_value.TableIndex,:new_value.ColumnName4,:new_value.ColumnName5,:new_value.ColumnName6

    from dual
    )
    where t2.TableIndex = :new_value.TableIndex;
    
       UPDATE t3 SET (
    TableIndex,ColumnName7,ColumnName8
    ) 
    = (
    select
    :new_value.TableIndex,:new_value.ColumnName7,:new_value.ColumnName8
    from dual
    )
    where t3.TableIndex = :new_value.TableIndex;
END;
/

-- Check that the columns are updatable

select 
TABLE_NAME, INSERTABLE, UPDATABLE, DELETABLE 
from 
    dba_updatable_columns 
where 
    TABLE_NAME in ('T1', 'T2', 'T3', 'T_TOTAL');

-- Using the view a user can issue:

update t_total set(
ColumnName2,ColumnName5
)
= (
select 'Testdata a','Testdata b' from dual
) 
where
TableIndex = 815;

-- resulting in the change of 2 basic tables t1 and t2 or

update t_total set ColumnName7=    'Testdata c';

-- resulting in the change of the 3rd basic table.

The triggers are touching columns in the update statements
which are not changing, the last update only changes the 3rd basic
table t3 results in updates to t1 and t2 and in touching ColumnName8
of t3 although only ColumnName7 was changed.

My goal is that the update trigger for the last update results only
in:

update t3 set ColumnName7 = 'Testdata c';


Hope my example is not too big and that my question got clearer.

Many thanks

Stefan

and we said...

that would involve lots of dynamic sql and lots of code.

I don't like this "design" at all - it is not going to scale no matter what you do. You have access to all of the :old and :new values - a simple:

if ( :new.c <> :old.c or coalesce(:new.c,:old.c) is null )
then
   we are different
end if

would tell you if a given column has changed - but then you have to dynamically construct an update statement (and given you have hundreds of columns - you'll dynamically generate THOUSANDS or MILLIONS of different updates - flooding the shared pool - killing performance - definitely not scaling up at all). You'd have to use dbms_sql - since you don't know the number of bind variables at compile time. You would have to parse each and every statement each and every time (no caching of sql like static sql would do).

This would be a horrible idea. You will use static sql and pay that penalty (that of updating all columns) because the alternative - flooding the shared pool with thousands of unique updates that have to be parsed each and ever time) is infinitely worse.



statements like this:

The problem is that I can not change the application. So my view must be
</>
are never true. That is a choice you have made. It is a decision you have choosen.

I don't know how this could run out of rollback - if you are updating a row at a time (small transactions). If you are doing mass updates, then you have simply undersized your rollback space..


This'll be my epitaph ...

The problem is that I can not change the application.

Either that or

just bind, just join

one of the two will kill me some day....

Reviews

April 8, 2008 - 11am Central time zone Bookmark | Bottom | Top Reviewer: John Lavoie  You should just use an updateable view and not bother with an instead-of trigger. Followup   April 9, 2008 - 2pm Central time zone: well, that would move the goal posts wouldn't it. one must presume the view they need is the view they need - and hence an updatable one is not feasible since that would not provide the data they need. sort of like saying "querying dual is faster than querying a million row table so query dual instead" it is true that querying dual would likely be faster, but not relevant since it doesn't provide the necessary data.

---

Write a Review