I need to copy large data from one table to another (insert into..
values (select * from ...)) on production environment (system must be
running). I can do this in few parts, copying data partition by
partition. Are there any hint for this problem to avoid slowdown of
system?

On Sep 28, 6:55*am, "mick.w" <mic... (AT) poczta (DOT) fm> wrote:
Well, something has got to give. First of all, which exact versions
of everything are you using? What kind of volumes of data are we
talking about? Are there times when the system is less heavily
loaded? How long do you have to do it? Are you using any tools like
Informatica, Toad, or just sql, or pl/sql? Are you on the server or
is a client machine involved? Does the data need any transform? Are
constraints the same? What kind of storage are you using?

In general, if you can do it in sql that will be faster than pl/sql or
other tools, although there are some situations where pl could be
faster, and some situations where you might want start/stop
capability. It is also possible that you could lower the priority of
the os process doing the transfer, if you have flexible time
constraints for completion.

Some obvious things to check in testing is how big redo files are, it
may make sense to make them bigger for this time. Same for undo,
though some people argue to let undo grow as much as needed (my
opinion is this is an exception to that, unless you are doing this
periodically).

Also, don't use select *, be explicit about the columns. That's just
good programming practice, and has a slight performance effect
(although normally too small to notice).

A more risky move would be to temporarily use noarchivelog mode, but
users would have to sign off on the risk of losing production data
during the load time. Not really a strategy for a system that needs
to keep running, but may be worth it if a small time window overrides
other considerations.

jg
--
@home.com is bogus.
http://www.ocregister.com/articles/l...-bradford.html

Thanks for your response!

this at the weekends or nightly hours, so the time is enough
execute
key must be changed

Does executing command insert into ... values (select a, b from abc
partition(part1)) will produce a big slowdown? Does hint 'append'
could improve performance of this operation? (tables will be empty and
no records will be deleted from them for now)