Re: materlized views from complex queries and refresh

From: cptkirkh <khill_at_scic.com>
Date: 15 Jan 2007 09:53:22 -0800
Message-ID: <1168883601.780054.188890@q2g2000cwa.googlegroups.com>

cptkirkh wrote:
> sybrandb wrote:
> > DA,
> > I replied to the last response to my question. I also asked the
> > quetion because I was hoping to find out what others might do in this
> > same situation. Let me rephrase my question since you seem to be to
> > dense to understand my question. I have a very compex SQL query that
> > involves decodes and functions run on various tables to form a table
> > that makes it easier to report off of for our report designers. it
> > also uses outer joins to join two tables. I am not the one who
> > designed the SQL query that creates the current view just the one who
> > has to fix it. 10.1.0.3 would be the correct version. What I am
> > trying to achieve is a materialzed view that would refresh on commit.
> > It seems since i use such a compex query fast refresh is out of the
> > question. Full refresh is where I am confused. What if this query
> > takes 90 minutes to run? DOes this mena the MV would take 90 minutes
> > to refresh?
> > designers. it
> > > also uses outer joins to join two tables. I am not the one who
> > > designed the SQL query that creates the current view just the one who
> > > has to fix it. 10.1.0.3 would be the correct version. What I am
> > > trying to achieve is a materialzed view that would refresh on commit.
> > > It seems since i use such a compex query fast refresh is out of the
> > > question. Full refresh is where I am confused. What if this query
> > > takes 90 minutes to run? DOes this mena the MV would take 90 minutes
> > > to refresh?
> > >
> > >
> > >
> >
> > MVs do not refresh on SELECT. I don't think there is any correlation
> > between the time to query and the time to refresh. But likely the time
> > to refresh would be longer, not shorter.
> > Finally: could you look up 'top-posting' somewhere and stop behaving
> > rude?
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Ok ?I see what you are tlaking baout sorry it is my bad I didn't
> realize that people preferred I type at the bottom of the posting.

 First off I would like to apologize to everyone here let's just say it has been a bad weekend.

Ok so if you build a MV from a select statement initially then when it refreshes it does so based upon the change of the row in the original table for example here is a select statement.

select DISTINCT id,prefix, first_name, last_name, middle_initial, maiden_name, suffix,
address, city, state, zip, zip_plus,f_d_date(1,cli_rid,3) c_date,

f_d_date(2,id,3) ci_date, f_d_date(3,id,3) cr_date,f_d_date(21,id,3)
csr_date,
f_d_date(1,id,1) cnpdate,decode(F_PO(Id,14),14,'YES','NO') CIB,

decode(F_PO(ID,15),15,'YES','NO') CISB from facilitators,mailing_address,companies,
PERSONAL_DATA
where
mid=id and
cag_id=agid(+) and
ID = CID(+) Can I setup a fast refresh on commit for a query such as this? Waht would anyone else reading this post use as a query if your requirements were that you keep the data in the MV as current as possible? Thanks for your input and once again I would like to apologize to everyone int he group for my rudeness. Received on Mon Jan 15 2007 - 11:53:22 CST