RE: Merge

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <tim@xxxxxxxxx>, <Richard.Goulet@xxxxxxxxxxx>
  • Date: Tue, 6 Jul 2010 13:25:05 -0400

Do I dare ask?   Ok.   Do you need to pay for partitioning option
licensing?

 

Joel Patterson 
Database Administrator 
904 727-2546 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Tuesday, July 06, 2010 10:54 AM
To: Richard.Goulet@xxxxxxxxxxx
Cc: ORACLE-L
Subject: Re: Merge

 

Richard,

How about if the "reporting tables" were re-defined as
range-partitioned, but with only one partition?  Then, instead of
modifying the "reporting tables" as they are being read by users, the
refresh could instead be performed by populating an empty table from the
view (i.e. using INSERT /*+ APPEND */), then performing EXCHANGE
PARTITION to swap the newly-populated table with the single partition.
So, all direct-path nologging inserts, all direct-path nologging
compute-statistics index creations?  No downtime for queries, hardly any
redo, hardly any undo, no fuss, no muss?

Just an idea...



Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim@xxxxxxxxx
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...


On 7/6/2010 6:26 AM, Goulet, Richard 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 <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.

 

-- //www.freelists.org/webpage/oracle-l

Other related posts: