DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Steps for Table Partition and Index partition

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    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
    Badrinath

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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.

    Regards
    Gert

  3. #3
    Join Date
    Nov 2000
    Posts
    212
    consider the following:
    company and date(record create date) partitioning vs
    company partitiong vs
    date partitioning.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width