Re: materlized views and decodes

From: John K. Hinsdale <hin_at_alma.com>
Date: 29 Jan 2007 09:10:36 -0800
Message-ID: <1170090635.968511.301190@j27g2000cwj.googlegroups.com>

On Jan 26, 10:19 am, "cptkirkh" <k..._at_scic.com> wrote:

> Being a simple man an example would be helpful but let me see if I
> understand what you are saying.

A simple example is below at the end of this post.

> That I may use decodes in the select portion of my query that is
> used to build my MV that refreshes fast on commit. I do this by
> building MV logs on each of the tables in the FROM clause of my SQL
> query. But I don't need to include those particular columsn in the
> MV log.

Yes, all good so far.

> That last statement may confuse me cause I am not sure how it
> actually know s the original column changed if I don't put it in the
> MV log.

It helps to understand how Oracle keeps track of changes to the underlying ("master") tables, and how it applies them to the materialized view (MV):

• Oracle puts an entry in the MV log when ANY column changes for a table on which the MV depends. It puts in the logs only what it needs to uniquely identify a row in the relevant table -- PK value(s) or ROWID depending on how the MV log was set up.
• When applying the changes to the MV, Oracle gets the additional data it needs to fully re-construct the (new or updated) MV row by using the PK values (or ROWID) appearing in the log to access the underlying table(s), and then populates the MV.

I.e., the refresh method is "indirect" in that involves a lookup BACK into the underlying ("master") table(s) to retreive additional columns that either (a) appear directly in the MV or (b) are used as input to functions like DECODE that are part of the MV definition.

> Waht baout functions in a select
> clause? What do I do to make sure they get refreshed fast on commit.

Functions should be OK too (see example below).

Hope that helps to clarify,

        John Hinsdale

-=-= Begin Example -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

/* Create a test table with primary key */ CREATE TABLE stooges (
  name varchar(20) not null,
  iq number null
  constraint pk_stooges primary key (name) );

INSERT INTO stooges VALUES ('Moe',   150);
INSERT INTO stooges VALUES ('Larry', null);
INSERT INTO stooges VALUES ('Shemp', null);
INSERT INTO stooges VALUES ('Curly', 80);

commit;

/* Create the MV log and MV */
CREATE MATERIALIZED VIEW LOG on stooges; CREATE MATERIALIZED VIEW stooges_mv REFRESH FAST ON COMMIT as SELECT name,

       upper(name) as u_name,
       decode(iq,
              null, 'Unknown',
              'Known (' || iq || ')') as iq_known

FROM stooges;

/* Initial contents */
SELECT * from stooges_mv;

/* Update of column on which DECODE depends */ UPDATE stooges set iq = 110 where name = 'Larry'; SELECT * from stooges_mv;
commit;
SELECT * from stooges_mv;

/* Update of primary key column */
update stooges set name = 'Lawrence' where name = 'Larry'; SELECT * from stooges_mv;
commit;
SELECT * from stooges_mv;

-=-= End Example -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Received on Mon Jan 29 2007 - 11:10:36 CST