This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 25, 2013 1:58 PM by rp0428 RSS

Table Partitioning advice

755501 Newbie
Currently Being Moderated
I am on the Oracle DB Enterprise version 11.2.0.2.0. I have some large tables and was trying to get some info on table partitioning historical data.
My tables have a column called 'YearID' which I think would be a good column to seperate by. Maybe the 'range partitioning' option? A couple questions:

1) What type of partitionig would you suggest and why?
2) Can this be automated so at the beginning of each new year (new YearID value) it uses a different partitiion. I am concerned that partitioning would require alot of DBA intervention and need to make sure this can be set-up once.

thanks for any info.
  • 1. Re: Table Partitioning advice
    Justin Cave Oracle ACE
    Currently Being Moderated
    How you partition the table will depend on how the data is used. Do all of your queries include a predicate on YearID? Is YearID reasonably selective? Or do you just have a few years worth of data in the system? Is there another column in the table that is more selective that is always part of the queries?

    If you use interval partitioning, Oracle will automatically create partitions. Otherwise, someone will need to create the new partition at an appropriate time. That's generally not too much work for the DBA (particularly if you are just using one partition per year) though it can be automated if you create an appropriate DBMS_SCHEDULER job to add the partitions at an appropriate time or incorporate adding partitions into your ETL code.

    Justin
  • 2. Re: Table Partitioning advice
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    you may take a look at interval partitioning with automated partition creation.
  • 3. Re: Table Partitioning advice
    SomeoneElse Guru
    Currently Being Moderated
    I am concerned that partitioning would require alot of DBA intervention
    Far from being an annoyance, partitioning is often implemented specifically to give the DBA more options in handling the data.
  • 4. Re: Table Partitioning advice
    rp0428 Guru
    Currently Being Moderated
    >
    1) What type of partitionig would you suggest and why?
    >
    No suggestions are possible without knowing the reason you want to partition. If you've gotten along this far without partitioning why do it now?

    Tell us what problem you are trying to solve and then we can suggest some alternatives for you.
    >
    2) Can this be automated so at the beginning of each new year (new YearID value) it uses a different partitiion. I am concerned that partitioning would require alot of DBA intervention and need to make sure this can be set-up once.
    >
    Can't be answered until you resolve #1.

    It's easy to create an interval partitioned table, even from existing data. But without knowing what problem you have there is no way to know if that is the right solution.
    CREATE TABLE EMP_PART  
    PARTITION BY RANGE (HIREDATE)
    INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))(
    partition p_2011 values less than (to_date('01/01/2012','DD/MM/YYYY')),
    partition p_2012 values less than (to_date('01/01/2013','DD/MM/YYYY')),
    partition p_2013 values less than (to_date('01/01/2014','DD/MM/YYYY'))
    )
    AS SELECT * FROM EMP
  • 5. Re: Table Partitioning advice
    moreajays Pro
    Currently Being Moderated
    Hi,

    Range partition would be good option here.
    Whichever partition method you select based on you data distribution across columns, make sure you create appropriate index (local/global) on partition involving columns
    Also test out every query against partition table to solve the purpose of creating partitioning w.r.t sql performance
    Maintenance is the first thing on DBA's mind whoever planing to switch to partitioning so that to avoid tedious task of purging/re-creation/shrink/move in heap table

    If you want to reduce efforts in tracking & creating new partitions , you can opt for 11g featured interval partitioning else you can depend on min/max(overflow) partitions & split it whenever you want(may be during maintenance window) or create partitions one time for a year or two


    Thanks,
    Ajay More
    http://www.moreajays.com
  • 6. Re: Table Partitioning advice
    755501 Newbie
    Currently Being Moderated
    Thank you all for the information. Let me elaborate a bit further on my interest in data partitioning. My application is site specific and deployed at many sites around the world, each having its own instance of the DB. Many sites do not have DBA's present, so monitoring and maintenance of the DB is not very easy. The application has many transactional tables that are year specific. They use ''YearID' column used to seperate each years worth of data (see sample table below). Many of the larger customers (or customers that have been on the system for many years) have seen their performance degrade on these tables. The band-aid solution was to move previous years data out of these tables into a historical instance of the DB. This is very time consuming and wasteful. As I read more about the table partitioning option, I thought it might be something that could solve the problem. My questions would be

    1) What would be the best partitioning option to use knowing how the tables are set up?
    2) Can it be set up so that it automatically creates a new partition of the table when a new value of the 'YearID' column is enterered into the table? Is this where the interval option is used? I woul dnot be able to do this manually at every site as a new year ('YearID') comes.
    3) Once the partitioning option is turned on, are there specific maintenance/management tasks that a DBA would have to do on this (remember, no DBA's at my sites)?


    DM_HISTORICALGRADE     
    ID     NUMBER     -      19     
         STUDENTID     NUMBER     -      10     
         SCHOOLID     NUMBER     -      10     
         STORECODE     VARCHAR2     10     -      
         YEARID     NUMBER     -      10     
         TERMID     NUMBER     -      10     
         STOREDGRADESDCID     NUMBER     -      10     
         TERMBINSDCID     NUMBER     -      10     
         TERMSDCID     NUMBER     -      10
  • 7. Re: Table Partitioning advice
    JohnWatson Guru
    Currently Being Moderated
    If you have many tables that include this YEARID that you frequently join, perhaps look at interval partitioning for the table(s) at the top of the relational structure, and reference partitioning for the related tables. That will guarantee partitionwise joins (which may help a lot with the performance degradation) and also give you very easy maintenance (like, no maintenance needed). From your decription of the problem, you would probably want to locally partition the indexes.
    Any partitioning exercise requires a lot of analysis to find the optimal strategy.
  • 8. Re: Table Partitioning advice
    755501 Newbie
    Currently Being Moderated
    So using an “Interval” partitioned table, Oracle automatically creates partition for each new 'YearID', as the fresh data comes. Once the table is set up to use Interval partitioning, there is no other tasks that need to be done that would need a DBA. Correct?
  • 9. Re: Table Partitioning advice
    Justin Cave Oracle ACE
    Currently Being Moderated
    That is correct, yes.

    Before we go much further, you are aware, I hope, that partitioning is an extra-cost option on top of the enterprise edition license. I point that out because it seems odd to me that you would have a situation where a database was large enough to benefit from partitioning, important enough that it is worth spending gobs of money to license partitioning (in addition to the Oracle license), but then not have a DBA available to maintain the database.

    Justin
  • 10. Re: Table Partitioning advice
    755501 Newbie
    Currently Being Moderated
    Yes, we have that bundled into our license. Unfortunatley, it is up to each site to decide if they want to secure a DBA to manage their DB. Some of them have 1 and some don't so it is important that I provide a strategy that will work for all.

    So with the table below (DW_HISTORICALGRADE). Can you just confirm the syntax for partitioning the table (that already has data in it) by 'YEARID' so it will automatically create a new partition when a new value is entered in that column.

    Thanks for all the help.


    DM_HISTORICALGRADE
    ID NUMBER - 19
    STUDENTID NUMBER - 10
    SCHOOLID NUMBER - 10
    STORECODE VARCHAR2 10 -
    YEARID NUMBER - 10
    TERMID NUMBER - 10
    STOREDGRADESDCID NUMBER - 10
    TERMBINSDCID NUMBER - 10
    TERMSDCID NUMBER - 10
  • 11. Re: Table Partitioning advice
    Justin Cave Oracle ACE
    Currently Being Moderated
    You can't partition an existing table.

    You would need to
    - create a new, partitioned table,
    - copy the data from the old table to the new table,
    - drop the old table
    - create appropriate constraints, indexes, etc. on the new table
    - rename the new table to use the same name as the old table.

    If you want an example of creating an interval-partitioned table you can look at the documentation.

    Justin
  • 12. Re: Table Partitioning advice
    755501 Newbie
    Currently Being Moderated
    ok. thanks for the help.
  • 13. Re: Table Partitioning advice
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    So with the table below (DW_HISTORICALGRADE). Can you just confirm the syntax for partitioning the table (that already has data in it) by 'YEARID' so it will automatically create a new partition when a new value is entered in that column.
    You cannot partition an unpartitioned table using one DDL.
    You need to create a new empty partotioned table

    create table DM_HISTORICALGRADE_PARTITIONED(
    YEARID NUMBER(10),
    ID NUMBER(19),
    STUDENTID NUMBER(10)
    )
    PARTITION BY RANGE (YEARID)
    INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
    ( PARTITION BEFORE2K VALUES LESS THAN (TO_DATE('2000', 'YYYY')),
    PARTITION Y2000 VALUES LESS THAN (TO_DATE('2001', 'YYYY'))
    );
  • 14. Re: Table Partitioning advice
    755501 Newbie
    Currently Being Moderated
    thanks!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points