fragment by parent table

Posted in 2007

Topics: Triggers, Constraints & Referential Integrity

FRANK — Mon, 20 Aug 2007 19:53:49 — source: IIUG Forums & Mailing Lists

HI, Folks, We face this problem, We have two big tables : tab_p and tab_c tab_p( id serial, create_dt datetime year to seconds, ...... ) tab_c( id integer , /*foreign key reference tab_p's id*/ ...... ) Challenges, (1) the tables grow fast, 50 million rows/year, it will keep growing forever. Fragmentation is considered to be used, any experience for this volume and speed? (2) create_dt (in tab_p) is the only column that we will probably use to fragment, we hope the the rows in its child table tab_c can be fragmented together with its parent tab_p, any idea? Thanks a lot, Frank

↪ replying to FRANK

Tilman Model-Bosch — Wed, 22 Aug 2007 20:00:27 — source: IIUG Forums & Mailing Lists

ids-bounces@iiug.org wrote on 08/20/2007 09:53:50 PM: > HI, Folks, > > We face this problem, > > We have two big tables : tab_p and tab_c > > tab_p( > id serial, > create_dt datetime year to seconds, > ....... > > ) > > tab_c( id integer , /*foreign key reference tab_p's id*/ > ....... > > ) > > Challenges, > > (1) the tables grow fast, 50 million rows/year, it will keep growing > forever. Fragmentation is considered to be used, any experience for this > volume and speed? > > (2) create_dt (in tab_p) is the only column that we will probably use to > fragment, we hope the the rows in its child table tab_c can be > fragmented together with its parent tab_p, any idea? > Not that I know that such a thing is possible . Wouldn't it be possible to add create_dt to tab_c and then use create_dt for frgamentation ? BTW I recommend to think about a solution to archive off data from the table e.g. by year . In that case indexes should be created attached to fragments. Then deleting archived data is easy - just detach the fragment with unsued data. Rgds Tilman

↪ replying to Tilman Model-Bosch

FRANK — Fri, 24 Aug 2007 13:59:20 — source: IIUG Forums & Mailing Lists

> Wouldn't it be possible to add create_dt to tab_c and then use create_dt for frgamentation ? Thanks Tilman! I believe this is the best workaround at this moment. Frank On 8/22/07, Tilman Model-Bosch <tilman.model-bosch@de.ibm.com> wrote: > > ids-bounces@iiug.org wrote on 08/20/2007 09:53:50 PM: > > > HI, Folks, > > > > We face this problem, > > > > We have two big tables : tab_p and tab_c > > > > tab_p( > > id serial, > > create_dt datetime year to seconds, > > ....... > > > > ) > > > > tab_c( id integer , /*foreign key reference tab_p's id*/ > > ....... > > > > ) > > > > Challenges, > > > > (1) the tables grow fast, 50 million rows/year, it will keep growing > > forever. Fragmentation is considered to be used, any experience for this > > volume and speed? > > > > (2) create_dt (in tab_p) is the only column that we will probably use to > > fragment, we hope the the rows in its child table tab_c can be > > fragmented together with its parent tab_p, any idea? > > > Not that I know that such a thing is possible . > > Wouldn't it be possible to add create_dt to tab_c and then use create_dt > for > frgamentation ? > > BTW I recommend to think about a solution to archive off data from the > table > e.g. by year . In that case indexes should be created attached to > fragments. > Then deleting archived data is easy - just detach the fragment with unsued > data. > > Rgds > Tilman > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Glossary

fragmentation