We have a procedure that inserts data into a table using insert select. The table has a primary key. We see deadlocks on this insert, on this primary key. Unfortunately, I cannot change the input data.
How can I ensure that the data is inserted in a particular order to avoid deadlock? Order by in select doesn't help. Will it work if I add rownum, and therefore, force oracle to materialize the dataset?
Or is my only option to select into a collection, and insert with forall insert?