Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: fastest method to copy data between databases
In article <56ah79$bs5_at_leto.pcc.edu>, David Pelinka <dpelinka_at_pcc.edu>
writes
>Thanks to everyone for their responses. I've tried incorporating arraysize and
>copycommit into the COPY command without appreciable effect. I've also
>experimented with SQL*Loader and find that the load is VERY fast. The
>bottleneck is still in the query to extract the data.
>
>Looking at the query (select * from [view]), I notice that if all or most of
>the fields are selected, I get a start/stop behavior that acts as if a buffer
>is being filled. Using SQLPlus, about 20 records will display followed by a
>pause for 5 seconds, then the next 20, and so on. If I only select a few
>fields, the display is continuous. I eliminated the functions from the view
>so they aren't the cause. I also see this behavior on another much smaller
>database on the same machine. The behavior seems to persist during time of low
>usage.
>
David,
Do you have the opportunity to do complete table reloads? This would save you having to determine which records to extract/update.
Also, you mention using a view - perhaps you could extract from the underlying tables, this may be quicker.
Andy
Andy Hardy (Internet: aph_at_ahardy.demon.co.uk, CIS: 100015,2603)
PGP key available on request