Re: How to create pl/sql that run efficiently in background ?

Don't select the max(pk)+1 from the actual table to get the next value, because it's inherently fragile (under some circumstances you'll end up with a whole bunch of blocking locks), and a rollback can leave a gap.

Instead, pre-load a separate table with a reasonable number of unused values. When you create a new record, lock this table, select the smallest value, then delete it. If your whole OLTP transaction subsequently commits, you get to keep the number. If it rolls back, the number returns to the source table automatically and will be used for the next record.

This gives you "no gaps".

The table of numbers will only have one column, so it can probably have some thousands of values and still stay in memory. A background process can run periodically to top it up. By the sounds of it, every 24 hours is probably sufficient.

You will get contention, but it won't be as bad as you'd get if you were getting max(pk)+1 from the main table.

• Phil