Tuesday, July 15, 2008

Use sequence.currval for record count only

Currently I'm doing a data migration and the process is simple. Read the data from text file into temp permanent table in oracle, then transfer the data to the real time table. The temp table then will be updated a id number for reference to keep track which data has imported, so the script/procedure can be run again only for the failed records. A log table also created to log the import process, in case of any error appear for specific transactions the message is recorded.

Again, sequence of the imported data must be followed and therefore it's tempting to use the currval value to update the table. I was told that using currval will going to cause some trouble as environment is in real time and the sequence number might be changed when I use currval. The solution for this problem is simple: assign the sequence.nextval value to a variable and use that variable value instead of currval.

So what's the currval can be used of? Due to the value it holds can be unreliable and I have no idea who has call nextval and cause the value changed, therefore I believe it is best used for record count instead of max() or count() function. The benefit of this approach is maybe some performance improvement but more importantly, a more reliable application.

Reference:
CURRVAL-AND-NEXTVAL
Basic Element of Oracle SQL