Re: Materialized view and SQLLoader

From: emerald <emerald_at_net.hr>
Date: Sat, 16 Nov 2002 00:54:02 +0100
Message-ID: <ar41hf$jqg$1_at_sunce.iskon.hr>


In documentation is said that fast refresh can be used only if a table is filled by direct load. In my post I explained why I can't use direct load on my tables - it would slow loading since indexes are huge (direct load is recommended only on small tables).

What I'm saying is that you can't use fast refresh on large tables because you can not fill them by direct load. Am I right? Jusung Yang <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0211150035.7f3e781b_at_posting.google.com...
> I don't quite understand your concern. You might be better off just
> clearly state your current data flow design and what you would like to
> accomplish.
>
> Direct load and MV fast refresh are two separate technics that will
> not interfere with each other. In fact direct path load supports,
> since 8i, MV fast refresh by tracking the data change in the master
> table with all_sumdelta. After the direct load, the MV will become
> staled. When you fast refresh it, ORACLE will recompile it and refresh
> it properly.
>
>
> - Jusung Yang
>
>
>
> "emerald" <emerald_at_net.hr> wrote in message
 news:<ar12uf$pph$1_at_sunce.iskon.hr>...
> > First: Oracle 8.1.6.0.0 (yes, no patches, don't ask), AIX 4.3.3
> >
> > Here's my problem: in order to use materialized view logs, table on
 which
> > the log is created should be filled with sqlloader using direct load.
> >
> > Direct load is not reccomended on large tables, because it merges
 existing
> > index with index created on added records. It also locks table while
> > loading, and no update or select can be performed on such table during
 the
> > load.
> >
> > So, if a table is large, you should use materialized view logs, so you
 can
> > do fast refresh. But you can't use direct load, because table is large.
> >
> > If a table is small, you can use direct load. But, there is no point
 using
> > materialized view logs, because table is small.
> >
> >
> > So, how can I use these two?
> >
> > Right now I have 6 large table (two of them have +200,000,000 rows). I
 have
> > some load tables, in which I load stuff with direct load, do some
> > processing, and then copy data into main tables (insert into a select *
> > from a_load)
> >
> >
> > Since tables are large, I'm looking for a way to use materialized views
 on
> > them , instead having some summarized data in separate tables, thus
 having
> > to maintain procedures to fill those tables.
> >
> > Since we'll upgrade on 9.2 soon, any answer for 9.2. will be helpfull,
 too.
> >
> > Thanx
Received on Sat Nov 16 2002 - 00:54:02 CET

Original text of this message