Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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. –  Simon Righarts Feb 18 '12 at 4:07
    
What do you mean with "using integers instead of surrogate keys"? –  ypercube Feb 18 '12 at 8:18
    
@ypercube Sorry, typo. Meant to say use integers instead of guids, fixed it above. –  Chris Pitman Feb 19 '12 at 6:24

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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