Re: materlized views and decodes

From: cptkirkh <khill_at_scic.com>
Date: 26 Jan 2007 07:19:17 -0800
Message-ID: <1169824757.450106.118190@q2g2000cwa.googlegroups.com>

On Jan 22, 10:09 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> cptkirkhwrote:
> > When designing a MV can someone use a decode with a fast refresh on
> > commit?Absolutely.
>
> For REFERESH FAST ON COMMIT, you will need to do the
> requisite CREATE MATERIALIZED VIEW LOG ON ... commands.
> For these, you will find that you need NOT necessarily include the
> columns
> on which you are doing the DECODE, if their use in the materialized
> view is limited to the SELECT clause (even as "input" to DECODE).
> I suggest trying to create the MV log w/out any WITH PRIMARY
> KEY(...) clause at all and just see if it works. Let me know if you
> would like an example.
>
> Hope that helps.
> JH

JH,

    Being a simple man an example would be helpful but let me see if I understand what you are saying. 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. 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. Waht baout functions in a select clause? What do I do to make sure they get refreshed fast on commit. Thanks for your help

Here is an example of my code.

select

a.id,
d.cpd_a_a_n ,
d.cpd_a_a_o ,
d.cpd_a_a_n,
d.cpd_a_a_r,

decode(d.cpd_a_a_d,null,'NONE',d.cpd_a_a_d) ,

F_any(ac_id,1) ,
F_any(a.c_id,2) ,
F_any(a.c_id,3),

from c_and_f a,c_m_a b,a_c c,
C_P_DATA d
where

b.c_id = a.c_id and
a.c_ag_id = c.a_id(+) and
a.c_id = d.c_id(+)

Now the function f_any access three other tables not in this from clause. Oh and this is a scaled down version of the actual select statement we use to build a table we use for data reporting. My goal is to build a MV that refreshes on commit and not one that runs at night and gets recreated every night. Received on Fri Jan 26 2007 - 09:19:17 CST