Link to home
Start Free TrialLog in
Avatar of sunilbains
sunilbains

asked on

Adding partition dynamically

Hi All,
Can you Please advise on implementing below logic.

I need to create partition dynamically through script like adding partition to a table for that sysdate such as add partition to table A and partition name has convention like P1_yyyymmdd where yyyymmdd will be the date when script is running. After partition is created, then I need to load data to the table A for that day's data. Once data is loaded, I need to extract partition name which is equal to (sysdate - 2weeks) from Table A and load that partition's data(sysdate-2 weeks) to historical table. Once the count of rows for 2 weeks older data is matched from historical table (A_HIST ) with Regular table A, then drop that 2 week older partition in regular table A. This way retention of table A will have only 10 days of data at a time and older data will be archived to historical table(A_HIST).
Eg- Script run on 23rd oct 2009, so partition name will be P1_20091023 and data will be loaded for that day into table A. Now the date older than 2 weeks from this day(which is 9th oct) will be loaded from Table A to historical table(A_HIST).
Say 100 rows belong to 9thoct2009 partition(which is P1_20091009) which we checked from Table A and now the same 100 rows should be loaded to historical table from Table A. Once the number of rows 100 are loaded to historical table (A_HIST),the older partition P1_20091009 should be dropped from regular table A.
This way, we will always keep 10 days of data in Table A and historical data in Table A_HIST.
 Thanks in advance
Avatar of Geert G
Geert G
Flag of Belgium image

if you only want the last 10 days of data, why not create a view ?

create view last10days as
select * from table where datefield >= sysdate-10

put a index on that datefield

as for the moving of the data to a other hist table
will any select happen on the hist table ?
if so and there is over 10mill records, you could consider partitioning the hist table
and use a nightly job to move the data to the other table

it is of no use and only causes a lot of overhead to keep partitioning like this
besides that you will have to keep creating new datafiles on the server
you will not gain speed with this approach
your approach will give a very high maintenance
Ok.
You've asked collections of question in a single thread. I would like to handle this one by one.
First., lets just create the partitions.
1) Is that going to e a new Partition table from scratch or, already exists?
2) if Yes, do you want to do ADD PART or SPILT PART from max values?
3) can you have the date, owner and table_name as the parameters to create the partitions?
all you have to do is create a dynamic sql  by writing plsql sp.
alter table tab,ename add partitions partiton_name -- if you want to add
--if you want to spilit
alter table tablename SPLIT PARTITION ptn_name AT (value, value...)
      INTO (ptn_spec, ptn_spec)
      {UPDATE | INVALIDATE} GLOBAL INDEXES
 
Avatar of sunilbains
sunilbains

ASKER

Hello Sventhan,
Here are my responses-
1) New partition to create from scratch for this table as at present it is unpartitioned table.
2) Add partition daily based on sysdate when sql is executed such as , if it is execute on 1st Nov,2009, the partition name should be created as A1_20091101 (convention tablename_YYYYMMDD)
3) Yes ,I need to have date , owner and table name as parameter.

Thanks
please modify the attached script for your needs.
-Thanks.

part-maint-sql.txt
ASKER CERTIFIED SOLUTION
Avatar of sventhan
sventhan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial