when to partition a table????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: when to partition a table????

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    I have been reading about partitioning and how it inproves performance of of queries in case of very large tables.

    1. My question is how large is large enough to partition. When do you decide that the table needs partioning... when it has grown very large or when you know its gonna be very large in future.

    2.what are the performance implications if you partition a small Table

    3. What are the issues one will face when dealing with partioned Tables.

    Thanks

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    1. Depends. I've partitioned tables with only 100K rows, and I've not partitioned tables with 200M rows. It really depends on how you are going to access the data.

    To me, if a table has transactional data that can be grouped by date, it is the ideal candidate for partitioning.

    Test your queries against a properly indexed non-partitioned table vs. a properly indexed partitioned table. Partitioning is not the silver bullet for "large" tables.

    2. none.

    3. Indexes are the biggest pain with partitioned tables. If you move a partition or drop a partition, some or all of your index will become UNUSABLE and you will have to rebuild.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi;
    Partitioning is also good for table-data which is categorized in some values, like status-types for application-objects;

    3. The administration of partitioned tables is a critical point.
    it depends on how you partitition your table. For example if your partition-criteria grows ( date for example ) then you have add partitions from time to time.

    Partitioned tables alway use more place on Disk!

    if the distribution of the partition-key is not clear, then you have to create scripts to output partition-ddl scanning the taabledata thru the script.

    Check if you can access data with bitmapped indexes. Combining some bitmapped indexed coulumns in a query can result fast answertime and little indexfiles.

    Oracle should provid a partition-manager, to ease the use of this feature.

    In spite of all these points partitioning can be the last way to access large tables fast.

    Orca



  4. #4
    Join Date
    Sep 2000
    Posts
    384
    A big table(More than 512M or more than 1-2 million rows ),frequently used and if it has I/O contention then I will try to do a partition.
    Radhakrishnan.M

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