This discussion is archived
12 Replies Latest reply: Jul 7, 2013 7:06 PM by Hemant K Chitale RSS

Oracle 10g

84b09b47-0add-4854-b184-296b3903b009 Newbie
Currently Being Moderated

Hi Everyone,

 

I have a scenario as below and need to implement dynamic partitioning.

Lets say I have a partition by timestamp:

 

--2012-1 (Jan-2012)

--2012-2 (Feb-2012)

.

.

.

.

.

.

.

--2012-12 (Dec 2012)

 

Then I have Default partition (XYZ).

 

Any data with timestamp of year 2013 and more will be stored in the default partition. As a result, slowly this default partition will be sooo huge that it will go beyond control.

 

Now I need a way so that I can break this default partition dynamically in Oracle 10g.

 

Kindly advice. Thanks.

  • 1. Re: Dynamic Partitioning in Oracle 10g
    Karthick_Arp Guru
    Currently Being Moderated

    You need to split the partition. See Ask Tom "How to split a partition"

     

    Here is an example

     

    create table t
    (
      dt date,
      val varchar2(10)
    )
    partition by range (dt)
    (
       partition jan_2012 values less than (to_date('01-02-2012', 'dd-mm-yyyy'))
    , partition feb_2012 values less than (to_date('01-03-2012', 'dd-mm-yyyy'))
    , partition mar_2012 values less than (to_date('01-04-2012', 'dd-mm-yyyy'))
    , partition apr_2012 values less than (to_date('01-05-2012', 'dd-mm-yyyy'))
    , partition may_2012 values less than (to_date('01-06-2012', 'dd-mm-yyyy'))
    , partition jun_2012 values less than (to_date('01-07-2012', 'dd-mm-yyyy'))
    , partition jul_2012 values less than (to_date('01-08-2012', 'dd-mm-yyyy'))
    , partition aug_2012 values less than (to_date('01-09-2012', 'dd-mm-yyyy'))
    , partition sep_2012 values less than (to_date('01-10-2012', 'dd-mm-yyyy'))
    , partition oct_2012 values less than (to_date('01-11-2012', 'dd-mm-yyyy'))
    , partition nov_2012 values less than (to_date('01-12-2012', 'dd-mm-yyyy'))
    , partition dec_2012 values less than (to_date('01-01-2013', 'dd-mm-yyyy'))
    , partition mmm_yyyy values less than (maxvalue)
    );

     

    insert into t
    (
        dt, val
    )
    select add_months(trunc(sysdate, 'year'), (level-1)-12) dt
          , 'abcd' val
       from dual
    connect by level <= 24;

     

    commit;

     

    select * from t partition (jan_2012);
    select * from t partition (feb_2012);
    select * from t partition (mar_2012);
    select * from t partition (apr_2012);
    select * from t partition (may_2012);
    select * from t partition (jun_2012);
    select * from t partition (jul_2012);
    select * from t partition (aug_2012);
    select * from t partition (sep_2012);
    select * from t partition (oct_2012);
    select * from t partition (nov_2012);
    select * from t partition (dec_2012);
    select * from t partition (mmm_yyyy);

     

    alter table t split partition mmm_yyyy at (to_date('01-02-2013', 'dd-mm-yyyy')) into (partition jan_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-03-2013', 'dd-mm-yyyy')) into (partition feb_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-04-2013', 'dd-mm-yyyy')) into (partition mar_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-05-2013', 'dd-mm-yyyy')) into (partition apr_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-06-2013', 'dd-mm-yyyy')) into (partition may_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-07-2013', 'dd-mm-yyyy')) into (partition jun_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-08-2013', 'dd-mm-yyyy')) into (partition jul_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-09-2013', 'dd-mm-yyyy')) into (partition aug_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-10-2013', 'dd-mm-yyyy')) into (partition sep_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-11-2013', 'dd-mm-yyyy')) into (partition oct_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-12-2013', 'dd-mm-yyyy')) into (partition nov_2013, partition mmm_yyyy);
    alter table t split partition mmm_yyyy at (to_date('01-01-2014', 'dd-mm-yyyy')) into (partition dec_2013, partition mmm_yyyy);

     

    select * from t partition (jan_2013);
    select * from t partition (feb_2013);
    select * from t partition (mar_2013);
    select * from t partition (apr_2013);
    select * from t partition (may_2013);
    select * from t partition (jun_2013);
    select * from t partition (jul_2013);
    select * from t partition (aug_2013);
    select * from t partition (sep_2013);
    select * from t partition (oct_2013);
    select * from t partition (nov_2013);
    select * from t partition (dec_2013);
    select * from t partition (mmm_yyyy);

  • 2. Re: Dynamic Partitioning in Oracle 10g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >--2012-1 (Jan-2012)

     

    Is this LIST Partitioning as it seems to be OR  is it RANGE Partitioning as it is supposed to be ?

     

    Can you show the CREATE TABLE statement so that the Partitioning definition can be viewed.

     

    With Range Partitioning, you'd have to recursively split the last partition (XYZ) as demonstrated by Karthick.

     

    Hemant K Chitale

  • 3. Re: Dynamic Partitioning in Oracle 10g
    84b09b47-0add-4854-b184-296b3903b009 Newbie
    Currently Being Moderated

    Yup, it is a RANGE partition. I am looking at ways if I can schedule it to happen dynamically in Oracle 10g

  • 4. Re: Dynamic Partitioning in Oracle 10g
    Karthick_Arp Guru
    Currently Being Moderated

    Structural change to database should not happen on the fly. It should be done with the concerns of a good DBA.

  • 5. Re: Dynamic Partitioning in Oracle 10g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You'd have to write PLSQL procedures to "generate" the new partitions by splitting the upper partitions.

    Such code is always custom to each organisation's partitioning implementation.

     

    Only the 11g INTERVAL Partitioning auto-generates new Partitions.

     

    Hemant K Chitale

  • 6. Re: Dynamic Partitioning in Oracle 10g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >Structural change to database should not happen on the fly. It should be done with the concerns of a good DBA.

     

    It depends.  A Datawarehouse could be executing such changes daily.

     

    Hemant K Chitale

  • 7. Re: Dynamic Partitioning in Oracle 10g
    84b09b47-0add-4854-b184-296b3903b009 Newbie
    Currently Being Moderated

    Yeah its true that structural changes to database should not happen on the fly and better be done with the blessings of a good DBA..

     

    I am only doing bit of a research to understand & suffice a requirement if there can be any similar alternative of INTERVAL Partitioning (Oracle 11g) auto-generating new partitions in Oracle 10g.

     

    Also, I would also like to know what all are the overheads or risk point (Index, Performance) etc to implement such a fix. The database is huge and contains million records.

  • 8. Re: Dynamic Partitioning in Oracle 10g
    84b09b47-0add-4854-b184-296b3903b009 Newbie
    Currently Being Moderated

    Hi Hemant, Karthick & everyone,

     

    Thanks for your info.

     

    I am also looking at the risk points while doing such a dynamic partition in Oracle 10g: Can u both please throw light on the risk points to be taken care while doing so, such as :

    1. How it can affect the Indexes earlier attached to the default Partition which I split. How to handle it?

    2. What other overheads it can bring in? Like Table Locks, Row movement feature etc.

     

    The space allocated to Default Partition is huge in Production (32 GB) and carries lot of data. What will be the best feature to perform this task, so that I can avoid downgrading of performance.

    Kindly advice. Thanks in advance.

  • 9. Re: Dynamic Partitioning in Oracle 10g
    Solomon Yakobson Guru
    Currently Being Moderated

    Just pre-create monthly partitions till your retirement date - that's what I did . Or upgrade to 11g and use interval partitioning.

     

    SY.

  • 10. Re: Dynamic Partitioning in Oracle 10g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    1. How it can affect the Indexes earlier attached to the default Partition which I split. How to handle it?

    If your indexes are defined as LOCAL (equi-partitioned with the table), a SPLIT PARTITION against the table automatically splits the corresponding Index Partition as well.  When defining a LOCAL index, do NOT name each partition -- Oracle automatically then names each Index Partition to be the same name as the Table Partition.  It also  then follows this rule when doing a SPLIT PARTITION.

     

    2. What other overheads it can bring in? Like Table Locks, Row movement feature etc.

    Yes, it prevents concurrent DDL against the table.  SPLIT PARTITION is done during maintenance windows. A SPLIT PARTITION doesn't require you to ENABLE ROW MOVEMENT.  (Row Movement is required if you UPDATE the Partition Key columns such that the updated row has to move to another partition).

     

     

    Hemant K Chitale

  • 11. Re: Dynamic Partitioning in Oracle 10g
    84b09b47-0add-4854-b184-296b3903b009 Newbie
    Currently Being Moderated

    Thanks for info. It is really helpful.

    And how about Global Indexes? Are they required to be rebuit in all the cases during SPLIT activity?

    What can be the risks while performing index rebuilding in PROD with live data and storage of around 32 GB? Please advice.

  • 12. Re: Dynamic Partitioning in Oracle 10g
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    If you have Global indexes, you would add the UPDATE GLOBAL INDEXES clause to the ALTER TABLE ... SPLIT PARTITION .... statement.

    If you don't include the UPDATE GLOBAL INDEXES, you need to REBUILD the Global Indexes.

     

    Rebuilding indexes (unless done with the ONLINE clause) lock the table for the duration of the index rebuild.

    You should see the documentation :

    http://docs.oracle.com/cd/B19306_01/server.102/b14223/parpart.htm#sthref287

    and

    http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#sthref2865

    and

    http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#sthref2700

     

     

    Hemant K Chitale

Legend

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