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

Thread: partiton

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Question

    I have a 5 Million row table that I'm trying to recreate as a partitioned table sy roughly into partitons pt1,pt2.............now my question is after creating these partitons should i change my application who orginally say acessed the tables as

    select * from t1

    now should i go around and change the code at every place to

    select * from pt1
    select * from pt2

    and so on ?what could be the repercussions of partioning a table

    i am running oracle8.1.6 R2 on redhat linux 6.2

  2. #2
    Join Date
    Dec 2000
    Location
    Washington DC
    Posts
    42

    Thumbs up Partitioning

    The great aspect of Oracle Partitioning is that it knows which partition to go to based on the partitioning key set up during table creation. Therefore, this eliminates the need as a application developer to understand the physical storage aspects of the data. Your developer will only need to perform queries as normal select * from x where y=a;

    There are two types of Oracle Partitioning range and hash. I would need to know more about your data before I could recommend which way method of partitioning to use. We use hash partioning very effectively on an OLTP application.

    What type of data and how will it be accessed in your 5 million row table.

    Ken
    Ken Hinerman
    Data Interaction
    DBASupport@datainteraction.com

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    In order to access a partition directly, you would use:
    select * from t1 partition (pt1);

    If you have chosen a good partition key, oracle will know which partitions you need to look at. I would look at the explain plan output of the queries you intend to run in order to determine if they will use the partition scheme you have setup.

    BTW, I wouldn't partition a table just because it had 5M rows. A good index would be just as effective.
    Jeff Hunter

  4. #4
    Join Date
    Nov 2000
    Posts
    212
    your syntax for accesing partitiones is wrong, but in general you do not need to change application.

    Partitioning tables is one thing, another one is indexes: local indexes are partitioned as table is and therefor more maintenable. But if your query can not prune partitions then I believe it can take longer time to get response on local index(as oracle has to look into every index partition lineary) than on global index.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    my application is a normal oltp and lil bit of batch can anybody tell me a reason why i should not partiton a table taht is 5 million rows ? what type of aprtitons to use hash range or composite

  6. #6
    Join Date
    Dec 2000
    Location
    Washington DC
    Posts
    42

    Smile Thoughts

    I would recommend looking strongly into partioning any time your table exceeds 2GB that goes for individual partitions. Lets say you start out with 5 million rows, and 2 partitions, you conclude that each partition is taking up 1.5GB of space based on you user_segments view. Lets say you add another 2 million rows to your table, it grows to 2.1GB. Now you conclude it's time to add another 2 partitions.

    Note: Only create partitions in even numbers, this little know fact is buried deep within the Oracle docs and know it from experience myself.

    In general use a hash index partition key in a OLTP database because you are always going to be going after a small set of specific records. You would want to use range partitioning in a warehouse environment where you are performing large sweeps on the data and know the ranges you are looking for.

    It doesn't sound like you need to use composite keys yet, as your table is not large enough to benefit, but if you think you need to access your data based on ranges and there is a alot of it you can benefit from a combination of range,hash.
    Ken Hinerman
    Data Interaction
    DBASupport@datainteraction.com

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    Wink

    thanks for all of you for sharing those thoughts of your's

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