Partitioning Strategy
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Partitioning Strategy

  1. #1
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51

    Partitioning Strategy

    Hi All
    I need some help. In our database I have a table which has an average rowsize of 271 (from analyzed statistics). This will contain approx 200 million rows . So the Tablesize comes around approx 57 GB.
    I would like to partition this table. So to ensure good performance how many partition I should have?.. I am also not sure about the attribute on which I should partition it. But that may be I can do it on a date attribute which is also being used in the queries executed on this table but I am not sure about the distribution of data on this date. So can I go for hash partitioning of the table on this date attribute. I currently have about 13 tablespaces for data.
    Do I need to have more partition than this number.i.e do I need more tablespaces?.......What kind of partitioning is advisable? I am using Oracle 9.2.0.5.0
    Please suggest
    Thanks in advance.
    regards
    nik

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Partitioning strategy depends on the kinda queries you are running. Each Partitioning has its own advantages and disadvantages, you have to justify the strategy from business sense which minimise effort for the maintenance of partitions.

    You said you would like to partition on Date , number of partitions depends on how you are partitioning it with date (monthly, quarterly, yearly). Also you mentioned hash partitioning, what conclusions made you to choose hash partitioning with date as partitioning column ? Read the documentation before making decision. Range would be better with date than hash partitiong, again it all depends on how you want your data to be distributed across paritions and how you are querying that data.
    Reddy,Sam

  3. #3
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51
    Thanks for the reply reddy
    But the thing is thst even if I partition the table on a date.
    My queries are not being done on that date attribute. So even if I am able to partition the table on that date still it would help?
    regards
    nik

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Then what made you to choose DATE for your partitioning attribute ?
    Reddy,Sam

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Take into account that partitioning is an extra option. You have to pay separately for it.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Apr 2003
    Location
    Delhi
    Posts
    51
    Thanx for the replies all of you
    I tried it and got the answer. My concept of partitioning was not that sound . But gettin better.!!
    regards
    nik

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