Re: materlized views from complex queries and refresh

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 15 Jan 2007 10:57:06 -0800
Message-ID: <1168887424.872891@bubbleator.drizzle.com>

cptkirkh wrote:
> 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.

Apology accepted:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref534

Scroll down to:
General Restrictions on Fast Refresh

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Jan 15 2007 - 12:57:06 CST