Write database error - unique constraint vs batch mode

radema

Hi,

I'm getting some error with a Write Database operator. I usually write data using append mode in a table in an Oracle Database which as a unique constraint on some attributes. The connection with the database is not direct but I rather connect to a Denodo "database" which automatically push the data to the original database. 

I've observed that if I write data in Append bond with a batch value smaller than the table size I get the following error:

There was an error during a batch insertion: ORA-00001: unique constraint (<constraint_name>) violated

On the other hand, if I set the batch to 0 the operation is rather slow. Have you never faced something similar? It is not clear if the problem is related to some back-end operator of the Write Database operator or if it is due to Denodo connection.

Thanks

R

BalazsBarany

Hi,

the batch size means that the given number of operations is grouped together in a transaction. This can be a huge performance benefit.

I haven't seen the problem you're describing. You could try batch sizes like 1, 2, 5, 10 and see if the error still happens.

The additional database layer can of course cause problems like this. 

Regards,
Balázs

BalazsBarany

Hi,

the batch size means that the given number of operations is grouped together in a transaction. This can be a huge performance benefit.

I haven't seen the problem you're describing. You could try batch sizes like 1, 2, 5, 10 and see if the error still happens.

The additional database layer can of course cause problems like this. 

Regards,
Balázs