Q
Problem solve Get help with specific problems with your technologies, process and projects.

Data partitioning and surrogate keys

In designing a database with Oracle partitioning in mind, would it be advisable to create surrogate keys or is it better to remain with the original modelled primary key and create data partitions on the unique key (which is the modelled primary key)?

In designing a database with Oracle partitioning in mind, would it be advisable to create surrogate keys or is it better to remain with the original modelled primary key and create data partitions on the unique key (which is the modelled primary key)?
I'm not a big fan of surrogate keys, but sometimes they can't be avoided. If I have a natural key, I'd rather use it.

How you partition your data should be determined by how you will access that data. For instance, if I will be accessing data from my large table one month at a time, then partitioning by some date field makes sense. If I will be accessing the data by district, then partitioning on the DISTRICT column makes sense. If I will be accessing the data by sales totals, then partitioning on this column makes the most sense. So you'll have to take a detailed analysis of the queries that will be issued against the data.

Look at the WHERE clause. See if you can spot a pattern which leads you toward the most logical method of partitioning your data. If your queries most commonly query data from one month or another, then partitioning by geographical region does not make much sense. Your queries for one month would most likely hit each and every partition and you won't realize performance benefits from this approach.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close