Partitioning tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Partitioning tables

  1. #1
    Join Date
    May 2003
    Posts
    2

    Partitioning tables

    Hello,

    I'm creating a biologic database. There only few entities in which must be inserted nearly 50Gb of data. That 's why i want to use partitioned tables.
    My questions are :
    how many partitions must i create ?
    is there a limit for the number of partitions ?
    is there a limit size for an optimum use of the partition ?

    Thank you in advance for your help.
    David

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    First of all you need to know the type of partitioning methods available. The based on the methods you need to decide which method would suit your situation best.

    Read this thoroughly
    http://otn.oracle.com/docs/products/...titi.htm#20590

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Jan 2003
    Location
    Denver
    Posts
    152
    We had 24 partitions for a 2-year window of monthly telecom data.
    We needed to go to daily loads. One of many solutions I suggested was to simply have daily partitions, its not even 800 partitions.
    Dont worry about the number of partitions your not likely to hit the limit.
    I used range partitioning for my date key. Oracle prefers more partitions since the granularity is one partition to a parallel process ( has Oracle improved that in 9.2 ? ). So the more the merrier. Local indexes ease administration.
    Load into standalone flat table and 'exchange partition' to swap data/index/stats with a partition which results in no data movement, only data dictionary pointers. Very fast and you never have to rebuild the other partitions indexes so it 'scales'.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The caveat against having too many partitions is that if you have queries that do not utilize partition elimination, it can require much more work.

    Comparing a non-partitioned table to a table with 600 partitions, a query might have to touch 600 index partitions to find a row that exists in only one partition. That could be a Bad Thing.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    Comparing a non-partitioned table to a table with 600 partitions, a query might have to touch 600 index partitions to find a row that exists in only one partition. That could be a Bad Thing.
    There's this new thing Oracle has called an "Index". Is it more work to touch 600 partitions than do a FTS on one table that has the same number of rows? Maybe. In comparison, though, I wouldn't say it is "much" more...
    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."

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Indexing? Really? Fantastic what they think of nowadays.

    Of course, comparing a FTS on a non-partitioned table to an index scan on a partitioned table is rather disingenuous and misleading. Why would you not index the column just because the table is not partitioned?

    If you would index a partitioned table on particular columns, then you would index a non-partitioned equivalent on the same, wouldn't you? The difference would be that a query predicated on the indexed column would only have to touch one index, instead of 600 seperate partitions of an index.

    The point i am making is that you have to consider both the positives and the negatives of the feature befoe jumping straight into it. Partitioning can improve parallelism sure, but parallelism has a downside also. Who parallelises on an OLTP application where individual queries return very small numbers of rows? Maybe only people who believe that non-partitioned tables do not need indexes
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I go with slimdave...

    Non Partitioned Tables will be more helpful, if properly planned to build the index on key columns in right order.

    And the queries optimised for APPL usage, will fetch, rather there will be less overhead for the retrivel of data compared to the Partitioned Tables.

    We have Non Partioned Tables holding around 30GB data..and we dont face any problems..And we have such N number of tables.



    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm not saying the either partitioned or non-partitioned is better, just that they suit different situations. Think about how data gets in the table, how it's modified once it's in there, how it's selected, how it's deleted.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2003
    Posts
    2

    Thumbs up Partitioning tables

    Hello,

    thank you for all your answers and I have well understood that i have to think about the use of the data before to decide whether or not i'll use partitioned tables. However i have another constraint to tell you : nearly 1 Gb will be added to the database each week and the size of the data added will grow quickly?

    In this case, is it not better to use partitionned table than index on non partitionned table ?

    Bye.
    David

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    See having a partitioned table doesnot always guarantee that the performance will increase. You need to test and see if performance improves or not and also you need to modify your queries so that it fetches the correct partitions.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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