This discussion is archived
8 Replies Latest reply: Sep 29, 2013 10:26 PM by Hemant K Chitale RSS

How to partition sequence values for interval partitioning?

supersen Newbie
Currently Being Moderated

HI gurus,

 

Please help me on this.  I am going to partition the table based on number column   DATASEQ (number 20)

when each time the table updated, it will have new sequence value in DATASEQ.  it is need to be partitioned.  i decided to go for INTERVAL partitioning .

 

PARTITION BY RANGE (DATASEQ)

INTERVAL(1)

(PARTITION  P1 VALUE LESS THAN

 

)

 

will it be good for partitioning the sequence?

 

Pls help

 

S

  • 1. Re: How to partition sequence values for interval partitioning?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >when each time the table updated, it will have new sequence value in DATASEQ

    If you update the DATASEQ column and it is a Partition Key column, you are asking Oracle to move a row from one partition to another.  That, generally, isn't good design.

     

    >INTERVAL(1)

    Do you mean that you want a *new partition* for each incremented DATASEQ value ?  Why ?  How many rows would each DATASEQ value have ?

     

    Hemant K Chitale


  • 2. Re: How to partition sequence values for interval partitioning?
    supersen Newbie
    Currently Being Moderated


    I am really sorry for my late reply. I was in vocation.

    Yes hemant. 

     

    Each time when we insert data into the table, based on DATASEQ, if it already there(For increment)  then we need to delete the rows of the table where dataseq=dataseq(whichwe run) if the record not there then we will insert.  This is the logic.

     

    Client is asking us to have latest run dataseq( from sequence value) in the new partition which will have 1 million records.. How can i proceed for this?

     

    Please help

    S

  • 3. Re: How to partition sequence values for interval partitioning?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    If each distinct DATASEQ is a different partition, you are asking for 1million partitions.  Highly inadvisable.

    Range partition with each partition for 10,000 DATASEQ values.

     

     

    Hemant K Chitale

  • 4. Re: How to partition sequence values for interval partitioning?
    supersen Newbie
    Currently Being Moderated


    Hi hemant,

     

    I think you misunderstood. Dataseq is the value taken from sequence.  we have seperate table called PERIOD which have two columns DATASEQ and Month.

     

    consider 123 is the dataseq then  01012013  will be the data for month

                  124  is the dataseq then  01022013  will be data for month.

     

    But our new table BILLING  if it have run for jan 2013 month  then dataseq will be updated with 123  which is taken from the table PERIOD.  if it is 124 means  then billing is ran for february month of  2013 likewise.

     

    My requirement each month when we run the billing...  DATASEQ need to be partioned. 123  shoould have seperate partition and 124 also  like wise it should go.

     

    Please advise how can i proceed for this?

     

    can i use interval partioning for this INTERVAL (1) . ?

     

    Please advise

     

    S

  • 5. Re: How to partition sequence values for interval partitioning?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The table definition would not "know" DATASEQ.

     

    01012013  seems to be a NUMBER and not a DATE.

     

    The INTERVAL between 01012013 and 01022013 is not (1) but 10000.

     

     

    Hemant K Chitale


  • 6. Re: How to partition sequence values for interval partitioning?
    supersen Newbie
    Currently Being Moderated


    Hi hemanth,,

     

    Read my requirement carefully. I am not adding the column month in my new table billing.  only dataseq number column is taken. based on the dataseq value  we will internally identify ( verifying from PERIOD table)  whether this is run for jan or feb like that.

     

    We are updating only  dataseq column in billing table.  which is already number.

     

     

    S

  • 7. Re: How to partition sequence values for interval partitioning?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You start with this definition :

    PARTITION BY RANGE (DATASEQ)

     

    I give up.  I do not understand your month.  Hope that someone else understands your definitions.

     

     

    Hemant K Chitale

  • 8. Re: How to partition sequence values for interval partitioning?
    supersen Newbie
    Currently Being Moderated


    oh okay thanks.

     

    Hi gurus,

     

    My requirement is as below

    1) My new table  BILLING will have the column DATASEQ which is number  taken from sequence.  we will update this column from another  pre defined table

    called  PERIOD which will have only two columns

    a) DATASEQ  which has number  data type

    b)  MONTH  which has data columns  as below

    consider 123 is the dataseq then  01012013  will be the data for month

                  124  is the dataseq then  01022013  will be data for month.

     

    But our new table BILLING  if it have run for jan 2013 month  then dataseq will be updated with 123  which is taken from the table PERIOD.DATASEQ.  if it is 124 means  then billing is ran for february month of  2013 likewise.

     

    My requirement each month when we run the billing...  DATASEQ need to be partioned. 123  shoould have seperate partition and 124 also  like wise it should go.

     

    Please advise

    S

Legend

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