Richard,
Even though Dan can't use the suggestion, I thought it was a pretty good
idea. Quite innovative.
P.S. the source database has to be Enterprise Edition Oracle.
-----Original Message-----
From: Dan Pappas
[mailto:oracledba-ezmlmshield-x8538469.[Email address protected]
Sent: Monday, August 29, 2005 3:40 PM
To: LazyDBA Discussion
Subject: RE: Materialized Views Input
Richard, thanks for responding. As usual, there are various constraints
that prevent me from some of your suggestions:
1.
I am pulling the data via synonyms of the tables that reside on
distributed servers. Therefore, I assume that I am using DBLINKS in the
background.
2.
I don't get direct access (and not likely to be able to get this
authority) to the server in order to create by own objects. Therefore,
I can't use the transportable tablespace suggestion.
3.
If I use a CTAS approach, then I will have to create the table
on my local server and select the data from the distributed tables. If
I do this, then will parallelism still work? If so, then how do I force
parallelism, or will this happen automatically?
Thanks again,
Dan
________________________________
From: Armstrong Richard [mailto:oracledba-ezmlmshield-x71823972.[Email
address protected]
Sent: Mon 8/29/2005 1:21 PM
To: LazyDBA Discussion
Subject: RE: Materialized Views Input
Dan,
Great question. A few folks I have discussed the issue with like them,
but I have had less than impressive results with materialized views.
One thing is for certain though, if you have problems with the SQL like
it is now, the SQL in the materialized view will not be any faster. I
have had some materialized views that never materialize because the SQL
is too complex or poorly tuned. I often turn to CTAS (create table as)
statements to take advantage of parallelism and eliminating rollback
issues related to inserting into a table.
Whenever I hear about 12 hour runs, I immediately think about tuning.
Even if you ultimately end up with an architecture solution different
from what you have today, it would probably be very beneficial to tune
what you have developed so far so that you have the tables and SQL tuned
for the type of SQL you need to run to pull the data. It is a good
exercise that can also flush-out other creative ways of pulling the
data.
You are working with a good-sized join, complicated by the distributed
data, so it can be a challenge. Due to the number of rows you are
working with, some creativity above and beyond traditional SQL tuning
might be required.
For example, consider this option:
On each Oracle instance/server, create a transportable tablespace.
Use a series of CTAS statements at each source data location to create
one table containing the subset of rows you want to play with into that
transportable tablespace.
Transport the tablespace(s) to a central location and bring them online.
Do any final table consolidation to get your one table and then process
the combined subset.
You can do all of this without transportable tablespaces by using
database links, but then you will be slowed by SQL*Net. The
transportable tablespace solution is SQL*Net free and based upon a
normal FTP instead.
-Richard
-----Original Message-----
From: Dan Pappas
[mailto:oracledba-ezmlmshield-x87594677.[Email address protected]
Sent: Monday, August 29, 2005 12:39 PM
To: LazyDBA Discussion
Subject: Materialized Views Input
I am part of a team that is building a small data model for a set of
users. The data I need is
spread out over multiple servers (typical). I am thinking that I might
need to use Materialized Views. For example, I want to select data from
2 different tables (Tables A & B) and insert this data into a work or
staging table daily. Each of those tables has about 125 million rows;
however, I am only interested in about 3 million rows of that data.
I created a view that determines the amount of data in one table
(Table A) by comparing equal values in a reference data table (the 3
million rows). Using the view, I try to match on Table B and then
insert the resulting data into my staging table. However, this process
takes too long (takes about 12 hours to complete this task so far).
I have not used materialized views before, but reading about them I
get the impression that I can essentially take a "snapshot" of the data
that is residing on these distributed servers and make it local to my
server--is that correct? Also, can a materialized view have statistics
updated
and if so how? (e.g. mv = test_mv)
Any thoughts or suggestions would be welcome.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page