5

In MS SQL Server, it is well documented that the type of the primary key can have a dramatic effect on performance. Using narrower types and sequential values significantly improves insert performance when using SQL Server, which is why using integers instead of guids for surrogate keys tends to make sense.

However, I cannot find any references to the same discussion for Oracle. Someone asked me to look at a schema for them that uses varchar2(32)'s for all of the primary keys, which seems much wider than needed. Does this matter in Oracle?

Bonus points for anyone who can refer me to a good blog like Kimbery Tripp's but in Oracle world.

3
  • 1
    I'd be rather surprised if the same general rules didn't apply. That said I can't find any references for it, so I don't actually know. On Oracle blogs - it's not quite a blog, but AskTom -- asktom.oracle.com -- is an amazing resource. Feb 18, 2012 at 4:07
  • What do you mean with "using integers instead of surrogate keys"? Feb 18, 2012 at 8:18
  • @ypercube Sorry, typo. Meant to say use integers instead of guids, fixed it above. Feb 19, 2012 at 6:24

1 Answer 1

4

From what I know, it depends on whether your table is IOT (Microsoft calls it clustered table, a table with clustered index)or not . Historically, Oracle IOTs performed worse than its heap tables. That seems to change (I believe with Oracle 10), but there is still prejudice against IOT. So the article you mentioned can be applied to some extent if you use IOT in Oracle, but not for heap tables.
Don't misunderstand me, having a PK constraint supported by index on large varchar2 column has impact on performance, but if it's an Oracle heap table, it's the same as having an index on non-PK varchar2 column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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