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
>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
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
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
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
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
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
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
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