We have a process ( a stored proc) that exports data ( about 10
million records) from a huge table,t1 and loads it into another huge
table t2. The export runs for 3 hours everyday because of a very
complex sql...
My question is can we load from the table directly instead of exporting
the data and loading it from the falt file??
like using load from cursor??
does it have any disadvantages?? when we declare a cursor where is the
result stored?? can it overflow to the disk?? people here had lots of
performance issues using cursors in a Stored proceedure...
I do not know the answer to your question. However, a side note:
The export runs for 3 hours everyday because of a very complex sql...
I do not know if this applies, but using a temp TABLE, a splitting up
the actions of the complex SQL to multiple simple SQL statements may
speed things up a bit.
I just sped up a query that took aboutone minute and forty seconds to
under a second by using a temp table instead.
I don't see any disadvantages with using CURSOR except make sure only
after the LOAD only you issue a commit.
Regarding memory issues check other posts..
