How to create/select a unique Identifier OID in case I have a table view in oracle and none of the fields or their combination can be unique. The process of selecting OID is described here and here.


Not only does a registered rowid column need to be INTEGER (32-bit), POSITIVE, NON-ZERO, NOT NULL and UNIQUE, it also needs to be reproducible on subsequent invocations. It is not possible to manufacture a field on the fly which meets this reproducibility requirement.

Therefore the only way to generate a rowid for such a non-unique view is to populate a table with whatever combination of attributes are unique, add a sequence-fed NUMBER(38) value to that table, and then join the new table into the view.

It may be possible to produce a join which performs adequately for some use cases, but it is unlikely to perform well in all use cases. For this reason, it is highly advised to find or make a field in the existing tables which does meet the required characteristics, or to manifest the view as a table (updated as frequently as necessary), where a reproducible value can be persisted. (The side effect will be improved performance over the source view in all use cases.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.