spidereddy Posted December 8, 2014 Report Posted December 8, 2014 I have Table_A which is truncate n populate daily and holds 2 mil records per day.. I have another table, Table_B, which should hold 60 days of data from Table_A.. Table_B is a partition table partitioned on day with 60 partitions Whenever, Table_A gets data..it should also be inserted into Table_B and it should continue for 60 days.. 61st day the last partition data from Table_B should be purged and 59th day data should go into 60th partition..and today’s data from Table_A should go into first partition of Table_B.
Chitralekha Posted December 8, 2014 Report Posted December 8, 2014 Table_A daily truncate and load annaru.. Table_A lo 30days data ela Table _B lo untundhi??
spidereddy Posted December 8, 2014 Author Report Posted December 8, 2014 Table_A daily truncate and load annaru.. Table_A lo 30days data ela Table _B lo untundhi?? Table_A lo always only today data untadhi... Table_B is like a history preserving table.. everyday table_A lo entry ainapudu table_B lo kuda entry aithadhi.. table_A lo next day load ki truncate ayyi fresh data osthadhi.. table_B lo truncate avadhu,,fresh data append avthadhi..atla 60 days data append aithadhi.. 60 above unte purge aithadh
pjung88 Posted December 8, 2014 Report Posted December 8, 2014 Bhaya, look for slidingwindow partition concept.. partition concept. Key Points - SWITCH IN, SWITCH OUT,MERGE Here is some idea. I will explain the concept with 4 partitions( it should be same for 60 partitions as well) 12/05/2014 - partition 1 - FILEGROUP1 12/06/2014 - partition 2 - FILEGROUP2 12/07/2014 - partition 3 - FILEGROUP3 12/08/2014 - partition 4 - FILEGROUP4 12/09/2014 - partition 5 -- FILEGROUP5 (this is next used FILEGROUP) Adding new data to the partition table ---------------------------------------- KEY STEPS ---------- Now create a new Table with same structure as main table(Table B,as per your example) in the same filegroup as PARTITION 5,i.e create the table in File group 5 , i think this table will be TABLE A, per your example. Now create check constraints to make sure you can enter or load only 12/09/2014 data. Now create indexes on the new table similar to how they are defined on the main table. Now,you should be able to switch IN the new table(Table A) into the main table -- keyword SWITCH and the date will appear in the main table(table B). New table(table a) will not have any data. this is a meta data operation,hence will be very fast. Removing old data --------------------- Similar to above, create a table in the partition 1 file group,since thats the old data you want to remove. Now create check constraints to make sure you can enter or load only 12/09/2014 data I do not think you will need to create indexes on the table as you are only pulling the data. Now, you should be able to switch out the old data into the new table.so, the old data will not exist in the main table. you can let drop the new table or move to different place.... this is also a meta data operation,so, will be very fast. Now you have to automate this process....so, it will be a circular process where for everyday new day, you will add a new FG and delete the 1st FG as you merge the boundaries. So, you will not move the data between the file groups. www.brentozar.com velli search for partition -- you should get a video ...
ronitreddy Posted December 8, 2014 Report Posted December 8, 2014 Table_A lo always only today data untadhi... Table_B is like a history preserving table.. everyday table_A lo entry ainapudu table_B lo kuda entry aithadhi.. table_A lo next day load ki truncate ayyi fresh data osthadhi.. table_B lo truncate avadhu,,fresh data append avthadhi..atla 60 days data append aithadhi.. 60 above unte purge aithadh U might want to write a trigger man.. let me know if you need help with this.
MegaPowerRockstar Posted December 8, 2014 Report Posted December 8, 2014 easy ga.....aythe insert records on both tables and create a trigger to check for 60 days old and delete them......
spidereddy Posted December 8, 2014 Author Report Posted December 8, 2014 partition table concept in oracle telsa evarkaina.. hash partition tho same table_A structure tho Table_B create cheshna with 60 partitions..so p1,p2,p3....p60 partitions unai table ki.. now i can simply do a insert into Table_b from Table_A..like insert into Table_B select * from Table_A; na doubt entante.. when i try to insert the data again into that table..to which partition will the data go into? first insert cheshna data p2 lo poyi..second insert cheshna data p1 lo untadha.. can i direct the data to go into specific partition everytym.. this is the first tym m working with partition table..so conceptual ga emana miss aithe pls help me..
spidereddy Posted December 8, 2014 Author Report Posted December 8, 2014 Bhaya, look for slidingwindow partition concept.. partition concept. Key Points - SWITCH IN, SWITCH OUT,MERGE Here is some idea. I will explain the concept with 4 partitions( it should be same for 60 partitions as well) 12/05/2014 - partition 1 - FILEGROUP1 12/06/2014 - partition 2 - FILEGROUP2 12/07/2014 - partition 3 - FILEGROUP3 12/08/2014 - partition 4 - FILEGROUP4 12/09/2014 - partition 5 -- FILEGROUP5 (this is next used FILEGROUP) Adding new data to the partition table ---------------------------------------- KEY STEPS ---------- Now create a new Table with same structure as main table(Table B,as per your example) in the same filegroup as PARTITION 5,i.e create the table in File group 5 , i think this table will be TABLE A, per your example. Now create check constraints to make sure you can enter or load only 12/09/2014 data. Now create indexes on the new table similar to how they are defined on the main table. Now,you should be able to switch IN the new table(Table A) into the main table -- keyword SWITCH and the date will appear in the main table(table B). New table(table a) will not have any data. this is a meta data operation,hence will be very fast. Removing old data --------------------- Similar to above, create a table in the partition 1 file group,since thats the old data you want to remove. Now create check constraints to make sure you can enter or load only 12/09/2014 data I do not think you will need to create indexes on the table as you are only pulling the data. Now, you should be able to switch out the old data into the new table.so, the old data will not exist in the main table. you can let drop the new table or move to different place.... this is also a meta data operation,so, will be very fast. Now you have to automate this process....so, it will be a circular process where for everyday new day, you will add a new FG and delete the 1st FG as you merge the boundaries. So, you will not move the data between the file groups.www.brentozar.com velli search for partition -- you should get a video ... office lo open aithale,..intikellaka ee method try chestha..thanks bro
pjung88 Posted December 8, 2014 Report Posted December 8, 2014 office lo open aithale,..intikellaka ee method try chestha..thanks bro Menu SQL server anukonna...nenu chipincha concept SQL server dhi... Oracle ki google chesuko or may be others can helps... Imho, triggers are not good as you have to insert millions of rows..trigger will fire for each insert... Nee issue datawarrhouse lo data load chesinapudu chestharu sliding window partition anni...look. For similar in oracle..
.thumb.jpg.ce42bb16ec102475a78c2087ce269c7d.jpg)
Recommended Posts