SEO

#1 (**permalink**) 02-22-2008, 05:38 PM

I have 2 IDENTICAL tables (TableName_A and TableName_B) that I use in
my production system. I have a table (TableState) with 1 row and 1
column whose value determines which table is the "active set". (ie,
TableState had a column named TableSet whose value is either 'A' or
'B').

I would like to setup a view that can allow me to write queries with
"from Tablename" where the selection of A or B is done in the
background on the fly.

Is this possible?

Thanks,
Mark

#2 (**permalink**) 02-22-2008, 05:38 PM

On 1 Jul 2003 13:05:16 -0700, mperrault@ingdirect.com (Mark Perrault)
wrote:

>I have 2 IDENTICAL tables (TableName_A and TableName_B) that I use in
>my production system. I have a table (TableState) with 1 row and 1
>column whose value determines which table is the "active set". (ie,
>TableState had a column named TableSet whose value is either 'A' or
>'B').
>
>I would like to setup a view that can allow me to write queries with
>"from Tablename" where the selection of A or B is done in the
>background on the fly.
>
>Is this possible?
>
>Thanks,
> Mark

yes it is possible by simply
select * from table_a
union
select * from table_b

However, it looks like you really should merge the 2 tables into one,
and setup table_a and table_b as views with check option. Likely much
more efficient.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

#3 (**permalink**) 02-22-2008, 05:41 PM

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

#4 (**permalink**) 02-22-2008, 05:41 PM

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@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"

#5 (**permalink**) 02-22-2008, 05:41 PM

Sounds like you might want to check out Oracle's Workspace manager as a
possible option.

If you do, learn about it using the Oracle9i R2 documentation & database,
if possible. Then, if necesary, backport your experience to an older
version of the database as it's been available since 8.1.6 but poorly
documented and poorly understood 'till recently. The 9iR2 OEM has decent
graphical suport for this as well & the OBE section of OTN has a good set
of demo samples.

#6 (**permalink**) 02-22-2008, 05:44 PM

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@remove_spam.peasland.com> wrote in message
news:3F033B89.B6995D40@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@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"

#7 (**permalink**) 02-22-2008, 05:46 PM

"Brian Peasland" <oracle_dba@remove_spam.peasland.com> wrote in message
news:3F098553.7BB05EA3@remove_spam.peasland.com...
> 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.
>
Again I agree with Brian. Back in the mid-80's we were using what we then
called 'preaggregated redundant tables' to speed query execution, on a Wang
mini, using the proprietory RDBMS.

Much less is new than most think

Regards,
Paul

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Accessing 2 tables with 1 name