We have an application engine in which the first step is to load data into a temporary table before processing. We did an insert into ... from a select * from a custom view (very simple select statement). It happened , occasionnally, that the insert statement inserts records which does not exist in the view , or insert the same record twice, or does not insert the record at all . The volume of the records inserted turns around 40000 rec. The target record is initially empty and each record inserted has the process instance part of record field, so definitely the extra records could not come from elsewhere .
We discover the problem when the program failed with a unique constraint, on the temp table . We ran the select to find out which records caused the duplicate key (by doing a record count group by key), but could not find any.
Finally we discovered that the insert statement does not always insert the proper record. It insert the same records twice, even if in the source, the record exists only once.
The custom view from which the record is selected , is a complex sql which listed all the historical student records for each activated term , with data related to the academic program, academic plan, etc...
When we execute the select statement only, it works , the results are as expected, never have problem. However the same select statement executed within an insert into... select from , occasionnally does not give the same results, If after a unique constraint fail, I tried to run the insert again and again, after a couple of tries , it will pass through, (no longer unique constraint) and I end up with the correct results .
Has anybody ever face the same issue ?, Is it any factor (buffer size, memory , cache issues etc...database config ) could explain this behavior ? This is a very very uncomprehensive problem....
Thank you