Re: Accessing 2 tables with 1 name

We had one major problem, which was a large join took forever to complete. A query that the developers originally designed needed data from this join. We wanted to precompute the join and store the results in a table and have the application query from this table. This table gets "refreshed" once a day. Missing a days worth of data, between refreshes, was not a problem for us. And this took our query from hours down to seconds. So our application end users were happy.

Now keep in mind that we engineered this solution back in the Oracle 8.0.4 days.....

There was one big reason we didn't use read consistency. Read consistency, in our particular environment for this query, introduced a small performance hit on the application. This was noticable by the end users and we wanted to eliminate this. Load on the rollback segments wasn't too much of a problem, but having the application use the rollback segments for read consistency was.

So we just set up two tables with the precomputed join. A synonym pointed to one and the application just selected data from the synonym. In a nightly batch job, we precomputed the join again and stored the results in the other table. Once complete, the synonym was dropped and recreated to point to the freshest table.

Since Oracle 8i, we've wanted to look at introducing Materialized Views, but we just haven't gotten around to it yet.

HTH,
Brian

Phil Kaufman wrote:
>
> Brian, I am curious why you felt you needed to this since, with full
> transactional support built into the Oracle database engine, refreshing data
> by a process need not be seen by other reading processes, until it commits
> its new information.
>
> But perhaps I am missing something; perhaps that would have been too much
> load on Oracle's rollback segments, and you had insufficient storage and
> physical hardware to make sufficient use of this Oracle feature? I'm not
> sure, just curious, that's all.
>
> Phil
>
> "Brian Peasland" <oracle_dba_at_remove_spam.peasland.com> wrote in message
> news:3F033B89.B6995D40_at_remove_spam.peasland.com...
> > We did this exact thing before Materialized Views came into existence.
> > We would preaggregate data into a table to aid in searching. We would
> > have two such tables to perform a "refresh" operation on one while the
> > application read from the other. The synonym pointed to the correct
> > table and was moved to the new table after the refresh was complete.
> >
> > HTH,
> > Brian
> >
> > Mark Perrault wrote:
> > >
> > > Thanks for the advice.
> > >
> > > The reason I have 2 identical tables is that I load 1 while using the
> > > other and then simply update the State table to "point" to the new
> > > data.
> > >
> > > After some testing, I've chosen to use synonyms to solve my problem.
> > >
> > > Now I load the "inactive" table, update the State table, and then
> > > execute a stored procedure that creates a public synonym to the new
> > > active table.
> > >
> > > Thanks again for the advice.
> > >
> > > Mark
> >
> > --
> > ===================================================================
> >
> > Brian Peasland
> > oracle_dba_at_remove_spam.peasland.com
> >
> > Remove the "remove_spam." from the email address to email me.
> >
> >
> > "I can give it to you cheap, quick, and good. Now pick two out of
> > the three"

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"