Re: Refresh Materialized view manually

From: ddf <oratune_at_msn.com>
Date: Wed, 3 Jun 2009 11:10:38 -0700 (PDT)
Message-ID: <14b80189-46b0-4cfe-9346-cdc1f3cfa0bb_at_g20g2000vba.googlegroups.com>



On Jun 3, 11:44 am, joel garry <joel-ga..._at_home.com> wrote:
> On Jun 3, 8:34 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Jun 3, 3:53 am, Randolf Geist <mah..._at_web.de> wrote:
>
> > > > We refresh this view every 30 seconds automatically.
> > > > Sometimes underlying tables will be updated by users and they want to
> > > > generate a report right away with the updated data.
> > > > They don't want to wait next 30 seconds, for the latest data. So want
> > > > to provide a manually refresh so that latest data will updated in
> > > > materialized view.
>
> > > Naga,
>
> > > although I second the doubts that this design is sound, if you really
> > > have the need for "real-time" refreshes why not go for the ON COMMIT
> > > refresh clause of the materialized view?
>
> > > Regards,
> > > Randolf
>
> > > Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> > > SQLTools++ for Oracle (Open source Oracle GUI for Windows):http://www.sqltools-plusplus.org:7676/http://sourceforge.net/projects...
>
> > Depending upon how long the refresh actually takes, using the ON
> > COMMIT refresh option can increase the time required to complete the
> > insert/update/delete transactions to the defining tables, which may be
> > an unacceptable option especially if this is an OLTP system.  Granted
> > a 30-second refresh schedule is (in my mind) ridiculous, but using ON
> > COMMIT may slow down transactions to an unacceptable level.
>
> > Of course we know nothing of this system outside of the 30-second
> > refresh schedule for a  materialized view for which we have no
> > definition.
>
> But we can infer that the system is already fupped, I'd go further and
> speculate it already takes too long and that is why they want a
> cocaine-crazed-lab-rat-lever to pound on to "make it go faster."
>

Don't hold back, Joel, tell us how you really feel.

> Give them a "go faster" button to click on, that does an alter
> database go_faster with error redirection to the bit bucket.
>

Then they'd want the bit-bucket to work faster ...

> I'd be wondering how long the refreshes and updates take now.
>

Obviously the refreshes take less than 30 seconds, which tells me there isn't much to this materialized view. Possibly they could update a "reporting" table in the master database via (gasp) triggers then run Streams to their "reporting" database to effect essentially 'real-time' updates, sort of like this:

Update tables t,z,x,c,f,e,r,y,u,o,p,q and a --- triggers, triggers and more triggers ---> update 'reporting' table FLOOBAR  

|  

|  

Streams  

|  

|  

V

                                              'Reporting' database
where copy of FLOOBAR is updated  

^

                                                                              /
\
                                             Impatient end-users who
can't wait to see their updates

> jg
> --
> _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/jun/03/1b3tivo214441-tivo...- Hide quoted text -
>
> - Show quoted text -

I don't know why these users would 'need' to see these instantaneously updated 'reports' -- unless this is a trading system for stocks, bonds, commodities, etc. But we don't know any more than the OP chose to tell us.

But, hey, we're guessin' good.

David Fitzjarrell Received on Wed Jun 03 2009 - 13:10:38 CDT

Original text of this message