I was wondering if there was a reason for or against using precision in numeric surrogate keys in either a staging or presentation (star) environment in a data warehouse. We have Oracle 8i. One architect would prefer to make all surrogate keys type NUMBER (defaulting to 38), and the other would prefer to use NUMBER (precision) where the precision is equal to cardinality for static dimensions, and cardinality + 2 for growing dimensions. The first architect says there's no difference because Oracle will only store the precision necessary for any row, and the keys would be consistent. The second architect says that having precision will increase query performance. Oracle consulting recommended precision just for additional error checking. What’s your recommendation?


Chuck Kelly’s Answer: I would recommend NUMBER (precision), but for different reasons than what you mentioned. Most ETT vendors do not deal with NUMBER (no precision defaulting to 38) very well. The joining of the tables (query performance) should be the same. I am sure the definition of the second architect for the defining of precision is size/length of the cardinality (1000 is 4 and 100 is 3) and not the cardinality itself. I would use a rather large number, since you never know how growth changes over time. Very few dimensions are really static.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access