Change fragmentation strategy
Hello: we are planing to change fragmentation strategy due an error in the initial desing and I'm worried about this change. How shall I change it? Shall I add new dbspaces? Which one is the impact in the ids? We are talking to alter a table which store about 100 billons of rows? Thank you.
It will be processed fastest if the new fragmentation scheme is being written into new chunks that are on separate physical SAN structures from the current ones so that reads and writes can happen in parallel. This will be painful, there's no way around it. The entire table has to be rewritten. Drop all indexes, alter the table to RAW, alter the fragmentation schema all at once, then alter the table back to STANDARD, and rebuild the indexes and constraints. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 17, 2014 at 2:04 AM, jfrancisco.navarro@ethalides.com < jfrancisco.navarro@ethalides.com> wrote: > Hello: > > we are planing to change fragmentation strategy due an error in the > initial desing and I'm worried about this change. How shall I change > it? Shall I add new dbspaces? Which one is the impact in the ids? > > We are talking to alter a table which store about 100 billons of rows? > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013c6ae47d726504f29871b0
Hi, There is more than one way to do this. Another approach could be: 1. Create a new table that is a copy of your existing one with the new storage schema. 2. Foreign key checking can be slow. I'd recommend having any foreign keys and their supporting indices in place before the load unless you're on 11.70.FC8 which has a novalidate option. There is an unsupported hack for earlier versions to load with a disabled foreign key and then enable it without validation at the end. 3. Create HPL unload/load jobs to unload the data to a named pipe and then simultaneously load from the same pipe. You'd have to be on Unix/Linux for this to be viable. For any replication to work, you'll need to use deluxe mode. 4. Run the HPL jobs. 5. Run some checks to make sure your load was successful. 6. Create remaining indices and enable any foreign keys. If you have logged index builds switched on, be aware of the number of logical logs that may be needed. 7. Switch tables over in a renaming exercise. This approach can be better than other approaches if you have a table that is only inserted into, which is common with many "log" tables, because you can do a bulk load while the system is online and then just top-up while access to the table is blocked. If you do go for this, I'd definitely practise on a test system first :) Ben.
Just curious, How about the performance of the table with 100 billons of rows? read, write, maintain etc... How long will it take to do a simple update statistics? ...... Thanks, Frank On Mon, Feb 17, 2014 at 2:04 AM, jfrancisco.navarro@ethalides.com < jfrancisco.navarro@ethalides.com> wrote: > Hello: > > we are planing to change fragmentation strategy due an error in the > initial desing and I'm worried about this change. How shall I change > it? Shall I add new dbspaces? Which one is the impact in the ids? > > We are talking to alter a table which store about 100 billons of rows? > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c24e0249443d04f2b7b6d0
Frank: A newer feature in the Informix Server is Fragment Level Statistics. This means that only when a fragment distributions becomes out of date only that fragment is read and new distributions are recalculated (all fragments do NOT have to be read to recalculate the distributions of only a few fragments). John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/18/2014 04:46:06 PM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org, > Date: 02/18/2014 04:47 PM > Subject: Re: Change fragmentation strategy [32532] > Sent by: ids-bounces@iiug.org > > Just curious, > How about the performance of the table with 100 billons of rows? read, > write, maintain etc... > How long will it take to do a simple update statistics? > ....... > > Thanks, > Frank > > On Mon, Feb 17, 2014 at 2:04 AM, jfrancisco.navarro@ethalides.com < > jfrancisco.navarro@ethalides.com> wrote: > > > Hello: > > > > we are planing to change fragmentation strategy due an error in the > > initial desing and I'm worried about this change. How shall I change > > it? Shall I add new dbspaces? Which one is the impact in the ids? > > > > We are talking to alter a table which store about 100 billons of rows? > > > > Thank you. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c24e0249443d04f2b7b6d0 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >