Hemant,
If I understand this correctly the issue is around the redo overhead imposed by DELETE. If you are licensed to use partitioning, then you can try out a DIY refresh using a partition swap method
- create a range partitioned (pick any column) target table (TT) with the desired structure, with just one maxvalue partition
- create an empty intermediate table (IT) with the same structure as TT. This table will hold data from your source table (ST) temporarily
- create a stored procedure to refresh :
- truncate IT --> helps eliminate the DELETE redo
- insert /*+ append */ into IT select * from ST --> helps reduce redo
- exchange TT partition with IT (including indexes without validation)
- execute this refresh procedure at desired frequency
You can also swap statistics, drop/disable, add/enable constraints in the refresh procedure. I've used this technique and it works like a charm. Once you get the refresh procedure right, you can make it generic and then use it for any number of source/target table refresh.
The partition exchange happens almost instantaneously that an end user will not even notice it.
Regards,
Arul
On 4/4/07, Hemant K Chitale
<hkchital@singnet.com.sg> wrote:
Now , *that* is thinking "beyond the box".
a. Something in Oracle (10046)
b. Something else -- the script / job identifying it's own tracefilename
c. Something on FileSystem (the trace file)
d. A named pipe
e. A sleep interval
f. A programming language other then the Bourne shell !
All very doable. But would be difficult to explain !
It is easier to say "I rewrote the SQL query and reduced the scope of
the date-range in the query"
then to say a. b. c. d. e. f when explaining the solution to User
Management. !
Hemant
At 12:02 AM Wednesday, Tanel Poder wrote:
>Hemant,
>
> > How do you control a session's IO rate ?
>
>Create a pipe instead of the while which would be created if you enabled
>tracing.
>
>
>Then start a script which periodically reads bunch of rows from tracefile
>pipe and just discards them.
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com
--
http://www.freelists.org/webpage/oracle-l
