Oracle Enterprise stage - Load and Upsert

[vnspn]

Hi,

We have a process were we would need to load data in range of a million into an Oracle table.

We started using the Write Method as Load in the Oracle Enterprise stage as default, thinking that this should be the fastest as it uses Oracle loader concept.

But, just by chance we gave a try with Write Method as Upsert . We find that the Upsert method consistently is slightly faster than when using the Load method.

What could be the reason that Upsert is faster than Load when we always have only inserts to be done? Should Load be done with some special settings?

Thanks.

[kumar_s]

What is the write mode in Load method? Truncate/Append/Create/replace?
Truncate some time takes longer time than expected. Storage sometime need defragmentation.

[vnspn]

The Load Method tried was Append...

[kcbland]

In Oracle if you're loading a partitioned table with global indexes, you can find that for smaller volumes CONVENTIONAL load is more efficient than DIRECT path load because of deferred index rebuilding/updating.

[Yuan_Edward]

what value did you assign to $APT_ORACLE_LOAD_OPTIONS?

Is that table partitioned? Are there any constraints/indexes on that table and how did you maintain the indexes/constraints during loading?

How many records are to be loaded and how many records exist in that table?

=============================================
Couldn't comment if I don't know these information. But I guess if the number of existing records are much more than the records to be loaded. it may be worthy to keep the constraints on instead of disabling them before loading and then renabling them back after loading.

Hi,

We have a process were we would need to load data in range of a million into an Oracle table.

We started using the Write Method as Load in the Oracle Enterprise stage as default, thinking that this should be the fastest as it uses Oracle loader concept.

But, just by chance we gave a try with Write Method as Upsert . We find that the Upsert method consistently is slightly faster than when using the Load method.

What could be the reason that Upsert is faster than Load when we always have only inserts to be done? Should Load be done with some special settings?

Thanks.

[vnspn]

what value did you assign to $APT_ORACLE_LOAD_OPTIONS?

Is that table partitioned? Are there any constraints/indexes on that table and how did you maintain the indexes/constraints during loading?

How many records are to be loaded and how many records exist in that table?

=============================================
Couldn't comment if I don't know these information. But I guess if the number of existing records are much more than the records to be loaded. it may be worthy to keep the constraints on instead of disabling them before loading and then renabling them back after loading.

Hi,

Here are the answers for your questions,

No, we haven't added this environment variable APT_ORACLE_LOAD_OPTIONS.

No, the table is not partitioned. There are some indexes on the table and we used the option to Rebuild the index.

Initially the table is empty and we load through a set of around 5 jobs. Each load process may write around 1 million rows to the table.

Thanks.

[Yuan_Edward]

I am assuming that there are no constraints (primary/foreign/unique keys) except some checking constraints (e.g. some columns are non-nullable).

In the Oracle Enterprise stage, did you specify the option DISABLE CONSTRAINTS to FALSE? If you set it to TRUE, Oracle will spend some time enabling the non-nullable constraints after loading.

This can be observed in the job log. Maybe this is one of the reasons.

=============================================
By the way, $APT_ORACLE_LOAD_OPTIONS is defaulted to empty at the project level, and it will be set to OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES) at runtime.

[vnspn]

Yes, there are no constraints on the table. It originally had some indexes on it and even that was removed to do the Load process.

And also the Disable Contraints is set to False.

[dspxlearn]

vnspn-

What is the total number of records inserted. Per your earlier post, i understand that you will be processing about 5 million in 5 different loads.

And can you please let me know what is the total processing time taken for all these inserts? Cause, i am doing a performance tuning with all different scenerios..

[vnspn]

dspxlearn,

With write method of Load. it takes us approx. 5 mins. for loading 1.5 million rows.

Each row has around 100 columns in them.

[dspxlearn]

Thanks vnspn,

As of now i am using the Upsert method to load. We have two joins stages and aggregation functions(on these 1.5 million records) and filters before loading the data into the target database. For all this process it is taking 1hr and 45 mins.

I am waiting for my DBA's permission to get the SELECT previleges on the System tables before using the 'Load' method.

Thanks again.