Page 1 of 1
Oracle Commit frequency
Posted: Mon Nov 29, 2010 2:06 pm
by sheema
Hi,
I am using Oracle Enterprise stage to Load table into a Oracle database using the below option
Write Method : Load
Write Mode :Append
I am using the below environment variable
$APT_ORACLE_LOAD_OPTIONS --> OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)
is there a way that i specify the Oracle to Commit all the rows at once.
Thanks
sheema
Posted: Mon Nov 29, 2010 8:18 pm
by ray.wurlod
Bulk load is non-transactional, so COMMIT doesn't come into it.
Posted: Mon Nov 29, 2010 9:26 pm
by chulett
For DIRECT=TRUE that would be correct. For FALSE it falls back to conventional inserts with commits. And you only get 'all rows at once' by setting the commit interval to a very high number AFAIK. Other options will affect the interval like read size and bind size. You'd really need to have a chat with your DBA to get all of the gory details.
Posted: Mon Nov 29, 2010 9:33 pm
by sheema
Thanks for the reply.
Do we set the commit row interval using the below 2 environment variables, in this case(I am not sure as this is not Upsert Load Method)
$APT_ORAUPSERT_COMMIT_TIME_INTERVAL
$APT_ORAUPSERT_COMMIT_ROW_INTERVAL
please guide me ,how to set the commit intervals when I am using the Load Method.
$APT_ORACLE_LOAD_OPTIONS --> OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)
Posted: Mon Nov 29, 2010 10:21 pm
by chulett
No, they are only applicable to the Upsert method and are not used by a Load. You would need to expand on your "OPTIONS" clause to include ROWS and, as noted, you really should be discussing that subject with your DBA.
Posted: Mon Nov 29, 2010 10:31 pm
by sheema
Thanks for the reply. I will discuss this with my dba.