I have a table which has reletively huge number of records (around 20Million). The table has the data for two companies ( "H" for HLP , "E" from entex)
I have to do a lot of updates and deletes into these tables on the daily basis for both the companies.
I have decided to go by the partitioning of the table on the company column.
I have to decide the better way of doing the partitioning (Range or Hash) and the step, I need to do the partitioning.
(If possible step by step). Also let me know if I can add another partition later on(In case of a third company).
I would realy appreciate your advise and guidelines
If you choose range partitioning on the company column, it's easy to add a new partion if you get a new company.
If you choose hash partitioning, you choose a fixed number of partitions ( 2,4,8,16,... ) It's up to the hash function to determire where a record is put. When you add a company, you will have the same number of partitions.
consider the following:
company and date(record create date) partitioning vs
company partitiong vs
If date partitiong takes place, then number of partitions depends on period (weekly, monthly, year, ):
many partitions slow down local indexes (if partition pruning is unavailable for a query), while usage of global indexes loose advantage of partitioning.
However date partitioning can help manage such things as archiving historical data and removing it from database.
Click Here to Expand Forum to Full Width