RE: Merge
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: "Richard.Goulet@xxxxxxxxxxx" <Richard.Goulet@xxxxxxxxxxx>, Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>, Chris.Stephens@xxxxxxx
- Date: Tue, 6 Jul 2010 15:03:19 -0700 (PDT)
Hey Dick,
Have you looked into CDC? I had a similar problem, one that mviews would not
resolve, but was able to built reporting tables, using an mview log on the
source table and then wrote packages, (shhhh...don't tell Oracle! :)) based
off of CDC code to update my reporting table utilizing the PK for the inserts,
updates and deletes that had occurred. This environment is a hybrid OLTP/OLAP,
2TB with inserts hitting the system, at times, 30,000 per second.
Let me know if you are interested, I've written a support doc on it and would
be happy to send it to you if it can be of any help!
Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Tue, 7/6/10, Stephens, Chris <Chris.Stephens@xxxxxxx> wrote:
From: Stephens, Chris <Chris.Stephens@xxxxxxx>
Subject: RE: Merge
To: "Richard.Goulet@xxxxxxxxxxx" <Richard.Goulet@xxxxxxxxxxx>, "Toon
Koppelaars" <toon.koppelaars@xxxxxxxxxxx>
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tuesday, July 6, 2010, 1:46 PM
Sometime I feel like we should have a support group for DBA’s that have to deal
with this. I feel helpless trying to unwind some of the dooda I see at my
place of employment. My brain just can’t handle it and I have been unable to
find a way to break the problem down into smaller pieces. Views on views on
views on views.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Goulet, Richard
Sent: Tuesday, July 06, 2010 8:52 AM
To: Toon Koppelaars
Cc: ORACLE-L
Subject: RE: Merge
Yep, they are really complex views, which is why the duhvelopers don't want to
use them directly. Course a good pile of that is the complexity with which
they wrote them, view on top of several dynamic views which themselves are on
top of dynamic views. You know "spaghetti code".
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
From: Toon Koppelaars [mailto:toon.koppelaars@xxxxxxxxxxx]
Sent: Tuesday, July 06, 2010 9:23 AM
To: Goulet, Richard
Cc: ORACLE-L
Subject: Re: Merge
The DELETE clause for the MERGE statement cannot help you here?
Are the views too complex to turn them into materialized views that can be
fast-refreshed every 15 minutes?
On Tue, Jul 6, 2010 at 2:26 PM, Goulet, Richard <Richard.Goulet@xxxxxxxxxxx>
wrote:
All,
Not often that I ask for assistance from the list, but I'm a bit
baffled by this one.
We have a number of reporting tables that get populated every 15
minutes from an equal number of views. The developers are saying that to run
the reports off of the views is too long for customer acceptance. The current
process is to delete all rows form the tables, no not truncate, that would be
too easy. And then populate the tables with a select * from the view in
question. I'm looking at the merge command to replace this and the tons of
redo that it produces and has to be shipped to a standby on a very small
straw. To put is mildly the db is 74GB is size & produces 150GB of redo a
day. Now I'd simply shutoff the redo for that tablespace, but they claim that
the standby HAS to be able to come up on a moments notice & be fully
available. Yeah, this is a pile of Duhvelopers.
Now Merge as I see it handles 90% of what we need, but my problem is
that if a row is not matched because it no longer exists in the source view,
how do you delete it?
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
900 Chelmsford St, Suite 310
Lowell, MA 01821
978.614.2857
Richard.Goulet@xxxxxxxxxxx
http://www.parexel.com
The information transmitted in this communication is intended only for the
person or entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or other use of,
or taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received this
in error, please destroy any copies, contact the sender and delete the material
from any computer.
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential and
exempt from disclosure under applicable law. If the reader of this message is
not the intended recipient or the employee or agent responsible for delivering
this message to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify us
immediately by email reply.
Other related posts:
