let's had table n rows, no existing columns act primary key.
i'd generate 1 (for convenience , completeness).
i have few options doing this.
- i use guid
- i use sequence , generate integer each 1 (e.g., populated 1 n)
- i generate random integer
- (and many more)
i guids have advantages , disadvantages.
is there advantage using randomly generated integer on sequential integer?
any crud operations on indexed column shouldn't affected. , if doing bulk load, temporarily turn off index , restore afterwards
i can't see reason, i've come across situation (in case oracle) has done , i'm hoping more "what's sequence?".
since you're seeing specific implementation has chosen approach, can speculate @ original developer might have been thinking. that's subject error.
my guess original developer trying avoid issue right-most block in index on sequence-generated key becomes resource blocks many different sessions trying insert. "hot block" problem occurs because every session doing insert needs modify data in right-most block (assuming sequential keys) oracle needs serialize access. in systems, isn't big deal-- amount of serialization needed minimal , systems don't have enough simultaneous insert operations meaningful issue. if have high-volume system, particularly if you're running on rac cluster, wait events can meaningful. if had sort of issue, generating random key eliminate causing various sessions (generally) write different blocks in index.
of course, generating random keys not recommended approach if found waiting on right-most block of index frequently. oracle provides reverse-key indexes take care of hot block issue indexing data in reverse distributes i/o across blocks in index. if you're licensed use partitioning option, hash-partitioned index better. more detailed discussion on reverse-key indexes, rac, , mitigating hot block issues, here's link related question.
