Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: April 21, 2016 - 5:30 pm UTC

Last updated: April 21, 2016 - 7:24 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

We receive transaction files that we load via ETL. These transactions have no unique identifier on the data file, but of course get a sequence (surrogate key) once loaded. If I load that same file in a different environment (test, dev, etc), I can't rely on the surrogate key. Can I rely on ROWNUM for the select from the external table to be stable? Is it always the physical record number? If so, would using parallel affect this?

Thanks in advance.

and Chris said...

No. Rownum is assigned as each row is returned:

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

So if the data is returned in a different order, rows will have different rownums.

You can assign each row in the external table an ID using recnum. There's some evidence that Oracle always reads these sequentially, so a given row in the file will have the same recnum (or rownum):

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347075367803

Sequential reading is not 100% guaranteed though (it's not documented anywhere). It also assumes that the files are identical across all environments.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.