create indexes before or after inserting rows?

Hello all, I'm somewhat of a newby with Informix. I’m trying to figure out the point of building indexes after inserting a bunch of rows as opposed to creating them on an empty table. Creating indexes on tables with no rows takes an instant, and creating an index on a table with a lot of rows takes a significant amount of time. We have some tables with excessive extents I'm trying to optimize, and want to make the process fairly automated, and use Server Studio as little as possible. This is what I intend to do: - Calculate optimum extent size with server studio. - Unload table. - Make a backup of the schema. - Truncate the table. - Modify first and next extent size to optimum. - Reload table. - Update statistics. When Server Studio reorganizes a table, it drops the table entirely, recreates the schema without the indexes, loads the data, then builds the indexes. The index building takes the majority of the time for the entire process. I’ve tested my mostly command line process on some test tables, and they show the indexes. Does my proposed method leave the table with inaccurate indexes, since it doesn’t rebuild indexes on the reloaded data? What role does updating the statistics play?

After -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BEN KOPACZ Sent: Friday, May 29, 2015 3:15 PM To: ids@iiug.org Subject: create indexes before or after inserting rows? [35177] Hello all, I'm somewhat of a newby with Informix. I'm trying to figure out the point of building indexes after inserting a bunch of rows as opposed to creating them on an empty table. Creating indexes on tables with no rows takes an instant, and creating an index on a table with a lot of rows takes a significant amount of time. We have some tables with excessive extents I'm trying to optimize, and want to make the process fairly automated, and use Server Studio as little as possible. This is what I intend to do: - Calculate optimum extent size with server studio. - Unload table. - Make a backup of the schema. - Truncate the table. - Modify first and next extent size to optimum. - Reload table. - Update statistics. When Server Studio reorganizes a table, it drops the table entirely, recreates the schema without the indexes, loads the data, then builds the indexes. The index building takes the majority of the time for the entire process. I've tested my mostly command line process on some test tables, and they show the indexes. Does my proposed method leave the table with inaccurate indexes, since it doesn't rebuild indexes on the reloaded data? What role does updating the statistics play? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

In the version 12.10 of Informix there are commands to consolidate the extents inside the server while leaving the data accessible to the users. 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 05/29/2015 12:15:03 PM: > From: "BEN KOPACZ" <ben.kopacz@gmail.com> > To: ids@iiug.org > Date: 05/29/2015 12:15 PM > Subject: create indexes before or after inserting rows? [35177] > Sent by: ids-bounces@iiug.org > > Hello all, > > I'm somewhat of a newby with Informix. I’m trying to figure out the point of > building indexes after inserting a bunch of rows as opposed to creating them > on an empty table. Creating indexes on tables with no rows takes an instant, > and creating an index on a table with a lot of rows takes a > significant amount > of time. > > We have some tables with excessive extents I'm trying to optimize, > and want to > make the process fairly automated, and use Server Studio as little as > possible. This is what I intend to do: > > - Calculate optimum extent size with server studio. > - Unload table. > - Make a backup of the schema. > - Truncate the table. > - Modify first and next extent size to optimum. > - Reload table. > - Update statistics. > > When Server Studio reorganizes a table, it drops the table entirely,recreates > the schema without the indexes, loads the data, then builds the indexes. The > index building takes the majority of the time for the entire process. > > I’ve tested my mostly command line process on some test tables, and they show > the indexes. Does my proposed method leave the table with inaccurateindexes, > since it doesn’t rebuild indexes on the reloaded data? What role > does updating > the statistics play? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I think that means he wants you to translate all of your data into Klingon, then load it. You should still build your indexes afterward, though. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > John Miller iii > Sent: Friday, May 29, 2015 14:28 PM > To: ids@iiug.org > Subject: Re: create indexes before or after inserting rows? [35179] > > In the version 12.10 of Informix there are commands to consolidate the > extents > inside the server while leaving the data accessible to the users. > > > 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 05/29/2015 12:15:03 PM: > > > From: "BEN KOPACZ" <ben.kopacz@gmail.com> > > To: ids@iiug.org > > Date: 05/29/2015 12:15 PM > > Subject: create indexes before or after inserting rows? [35177] > > Sent by: ids-bounces@iiug.org > > > > Hello all, > > > > I'm somewhat of a newby with Informix. I’m trying to figure out the point > of > > building indexes after inserting a bunch of rows as opposed to creating > them > > on an empty table. Creating indexes on tables with no rows takes an > instant, > > and creating an index on a table with a lot of rows takes a > > significant amount > > of time. > > > > We have some tables with excessive extents I'm trying to optimize, > > and want to > > make the process fairly automated, and use Server Studio as little as > > possible. This is what I intend to do: > > > > - Calculate optimum extent size with server studio. > > - Unload table. > > - Make a backup of the schema. > > - Truncate the table. > > - Modify first and next extent size to optimum. > > - Reload table. > > - Update statistics. > > > > When Server Studio reorganizes a table, it drops the table > entirely,recreates > > the schema without the indexes, loads the data, then builds the indexes. > The > > index building takes the majority of the time for the entire process. > > > > I’ve tested my mostly command line process on some test tables, and they > show > > the indexes. Does my proposed method leave the table with > inaccurateindexes, > > since it doesn’t rebuild indexes on the reloaded data? What role > > does updating > > the statistics play? > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

In your case after. But if you wanted to use the indexes to kick out = duplicate rows, you would do those first. If you are concerned about = the time to build indexes, you can modify your onconfig file to perform = better, but as a newbie, I wouldn=E2=80=99t go there. j. > On May 29, 2015, at 3:17 PM, Mueller, Daniel D. = <ddmueller@intercall.com> wrote: >=20 > After=20 >=20 > -----Original Message-----=20 > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of = BEN=20 > KOPACZ=20 > Sent: Friday, May 29, 2015 3:15 PM=20 > To: ids@iiug.org=20 > Subject: create indexes before or after inserting rows? [35177]=20 >=20 > Hello all,=20 >=20 > I'm somewhat of a newby with Informix. I'm trying to figure out the = point of=20 > building indexes after inserting a bunch of rows as opposed to = creating them=20 > on an empty table. Creating indexes on tables with no rows takes an = instant,=20 > and creating an index on a table with a lot of rows takes a = significant amount=20 > of time.=20 >=20 > We have some tables with excessive extents I'm trying to optimize, and = want to=20 > make the process fairly automated, and use Server Studio as little as=20= > possible. This is what I intend to do:=20 >=20 > - Calculate optimum extent size with server studio.=20 > - Unload table.=20 > - Make a backup of the schema.=20 > - Truncate the table.=20 > - Modify first and next extent size to optimum.=20 > - Reload table.=20 > - Update statistics.=20 >=20 > When Server Studio reorganizes a table, it drops the table entirely, = recreates=20 > the schema without the indexes, loads the data, then builds the = indexes. The=20 > index building takes the majority of the time for the entire process.=20= >=20 > I've tested my mostly command line process on some test tables, and = they show=20 > the indexes. Does my proposed method leave the table with inaccurate = indexes,=20 > since it doesn't rebuild indexes on the reloaded data? What role does = updating=20 > the statistics play?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Ben: Great set of questions and I have lots of answers. Here goes, last questions first: - Does my proposed .... What role does updating the statistics play? - You don't say what version you are running which is always a good idea since sometimes answers have to be version specific. In versions 11.70 and later Informix produces HIGH statistics for the leading column in the index as part of the index build. If the index is empty, those distributions will be empty. Of course, since you intend to run the update statistics after loading the data, this is not a big issue assuming that you run the recommended suite of update statistics commands from the Performance Guide (or use my dostats utility). - By creating the indexes after loading the data, the load will go faster and most of the time the savings will be more than the time to build the index on the full table assuming you can build the index using PDQPRIORITY or parallel sorting (PSORT_NPROCS). That is why Server Studio uses this method. - Your method as layed out will work fine. Make sure that the ALTER TABLE EXTENT SIZE is run before the TRUNCATE TABLE and that you do not include the REUSE STORAGE option or the initial extent's size will not be modified. - There is an alternative method to reorg the table and compress out the extents. It usually results in one or two extents in the resulting table but has the advantage that it can be performed online without any downtime. There will be some minor performance impact while the reorg is running, but otherwise it is low impact. It does like this: - Set the new first extent and next extent sizes. - Execute the DEFRAGMENT SQL API function for the target table: execute function sysadmin:task( 'DEFRAGMENT', 'database:tablename' ); - or - execute function sysadmin:task('DEFRAGMENT PARTITION', 'partnum1,partnum2, ...' ); - Monitor the execution (it runs in the background) using onmode -g defragment - Another option is to use alter fragment to reorg the table, that will lock the table but it is often faster than the unload - reload method: - Set the new first extent and next extent sizes. - ALTER FRAGMENT ON mytable INIT IN dbspace; The dbspace can be the same dbspace that the table already resides in or a different one. Since neither of the methods I offer instead of the one you are planning to use requires any indexes to be rebuilt nor update statistics to be run, they both will be faster than your method (or using Server Studio). The ALTER FRAGMENT method actually has to patch up the rowids in the indexeswhile the DEFRAGMENT API does not. The API function leaves the relative rowids for the rows unchanged so this method does not recover or compress out empty row slots left by deleted rows. You can use the REPACK or REPACK SHRINK API function to compress out deleted row space either before or after defragmenting. Both you method and the ALTER FRAGMENT will compress out deleted row slots. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com 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 Fri, May 29, 2015 at 3:15 PM, BEN KOPACZ <ben.kopacz@gmail.com> wrote: > Hello all, > > I'm somewhat of a newby with Informix. Im trying to figure out the point > of > building indexes after inserting a bunch of rows as opposed to creating > them > on an empty table. Creating indexes on tables with no rows takes an > instant, > and creating an index on a table with a lot of rows takes a significant > amount > of time. > > We have some tables with excessive extents I'm trying to optimize, and > want to > make the process fairly automated, and use Server Studio as little as > possible. This is what I intend to do: > > - Calculate optimum extent size with server studio. > - Unload table. > - Make a backup of the schema. > - Truncate the table. > - Modify first and next extent size to optimum. > - Reload table. > - Update statistics. > > When Server Studio reorganizes a table, it drops the table entirely, > recreates > the schema without the indexes, loads the data, then builds the indexes. > The > index building takes the majority of the time for the entire process. > > Ive tested my mostly command line process on some test tables, and they > show > the indexes. Does my proposed method leave the table with inaccurate > indexes, > since it doesnt rebuild indexes on the reloaded data? What role does > updating > the statistics play? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec516235d7cd45505173e54a3

John's gone alien on us again! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com 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. 2015-05-29 15:27 GMT-04:00 John Miller iii <miller3@us.ibm.com>: > > In the version 12.10 of Informix there are commands to consolidate the > extents > inside the server while leaving the data accessible to the users. > > > 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 05/29/2015 12:15:03 PM: > > > From: "BEN KOPACZ" <ben.kopacz@gmail.com> > > To: ids@iiug.org > > Date: 05/29/2015 12:15 PM > > Subject: create indexes before or after inserting rows? [35177] > > Sent by: ids-bounces@iiug.org > > > > Hello all, > > > > I'm somewhat of a newby with Informix. I’m trying to figure out the point > of > > building indexes after inserting a bunch of rows as opposed to creating > them > > on an empty table. Creating indexes on tables with no rows takes an > instant, > > and creating an index on a table with a lot of rows takes a > > significant amount > > of time. > > > > We have some tables with excessive extents I'm trying to optimize, > > and want to > > make the process fairly automated, and use Server Studio as little as > > possible. This is what I intend to do: > > > > - Calculate optimum extent size with server studio. > > - Unload table. > > - Make a backup of the schema. > > - Truncate the table. > > - Modify first and next extent size to optimum. > > - Reload table. > > - Update statistics. > > > > When Server Studio reorganizes a table, it drops the table > entirely,recreates > > the schema without the indexes, loads the data, then builds the indexes. > The > > index building takes the majority of the time for the entire process. > > > > I’ve tested my mostly command line process on some test tables, and they > show > > the indexes. Does my proposed method leave the table with > inaccurateindexes, > > since it doesn’t rebuild indexes on the reloaded data? What role > > does updating > > the statistics play? > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1140289c04aba205173e5716

Hello Art, Thank you for your expert answers! I was indeed planning on using dostats. I'd like to try your proposed method: - Set the new first extent and next extent sizes. - Execute the DEFRAGMENT SQL API function for the target table: execute function sysadmin:task( 'DEFRAGMENT', 'database:tablename' ); Provided that it would work with IDS Version 11.50.FC5, which is what we're using.

Hah! Looks like his email somehow got sent in base64. I was able to decode it here: http://www.motobit.com/util/base64-decoder-encoder.asp "In the version 12.10 of Informix there are commands to consolidate the extents inside the server while leaving the data accessible to the users. 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 05/29/2015 12:15:03 PM: > From: "BEN KOPACZ" <ben.kopacz@gmail.com> > To: ids@iiug.org > Date: 05/29/2015 12:15 PM > Subject: create indexes before or after inserting rows? [35177] > Sent by: ids-bounces@iiug.org > > Hello all, > > I'm somewhat of a newby with Informix. I’m trying to figure out the point of > building indexes after inserting a bunch of rows as opposed to creating them > on an empty table. Creating indexes on tables with no rows takes an instant, > and creating an index on a table with a lot of rows takes a > significant amount > of time. > > We have some tables with excessive extents I'm trying to optimize, > and want to > make the process fairly automated, and use Server Studio as little as > possible. This is what I intend to do: > > - Calculate optimum extent size with server studio. > - Unload table. > - Make a backup of the schema. > - Truncate the table. > - Modify first and next extent size to optimum. > - Reload table. > - Update statistics. > > When Server Studio reorganizes a table, it drops the table entirely,recreates > the schema without the indexes, loads the data, then builds the indexes. The > index building takes the majority of the time for the entire process. > > I’ve tested my mostly command line process on some test tables, and they show > the indexes. Does my proposed method leave the table with inaccurateindexes, > since it doesn’t rebuild indexes on the reloaded data? What role > does updating > the statistics play? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > "

Yeah, see that's why I mentioned that you should have stated your version! B^( The API functions were introduced in v11.50 but the DEFRAGMENT operation wasn't added until v11.70.xC1 so you will have to use either the REPACK SHRINK, the ALTER FRAGMENT, or your method. Sorry. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com 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 Fri, May 29, 2015 at 4:43 PM, BEN KOPACZ <ben.kopacz@gmail.com> wrote: > Hello Art, > > Thank you for your expert answers! I was indeed planning on using dostats. > I'd > like to try your proposed method: > > - Set the new first extent and next extent sizes. > > - Execute the DEFRAGMENT SQL API function for the target table: > > execute function sysadmin:task( 'DEFRAGMENT', 'database:tablename' );> > Provided that it would work with IDS Version 11.50.FC5, which is what we're > using. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f90ea2747cb05173eadc7

Hi, there are some things to take into consideration depending on the amount of data loaded. 1) If you load a lot data, and your goal is to optimize the load process for timing, I would say you have to add the index afterwards, best with the online keyword, since the data will be available at the earliest time (followed by a big checkpoint probably). Also (in case you do not need HDR or a Log Backup (data is restorable with the same load process)) best way would be to load without logging. 2) When loading data with the indexes already active, the load process will take longer, since each insert has to produce the index leaves. While loading, the index might become unbalanced and the background process might start re-balancing the index, which can take some time, but the index will be present immediately after load (which might be an advantage) 3) When loading a lot of data, consider using HPL, which does things in parallel. Also here, I would recommend to build the indexes after load, even if it seems to take long when the indexes are created. 4) There is no ideal solution, it depends on your actual data. For small tables, maybe creating the index before the load might not harm the performance, but there is a reason that dbimport creates all indexes after load .... 5) Table extends should be pre-calculated roughly when loading a lot of data in one run. Of course there are ways to re-organize tables online, but thinking about extends before the load just helps to avoid that. 6) Fragmentation can be used to help when loading very huge amount of data (load into different fragments of same structure in parallel and attach afterwards). This is only possible if the load data can be split using a well known mechanism, which can be described as expression (e.g. on a date base). Indexes will be smaller then (per fragment) but I did not measure timing when index is created before/after load/attach. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "Art Kagel" <art.kagel@gmail.com> An: ids@iiug.org Gesendet: Freitag, 29. Mai 2015 22:54:07 Betreff: Re: create indexes before or after inserting rows? [35186] Yeah, see that's why I mentioned that you should have stated your version! B^( The API functions were introduced in v11.50 but the DEFRAGMENT operation wasn't added until v11.70.xC1 so you will have to use either the REPACK SHRINK, the ALTER FRAGMENT, or your method. Sorry. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com 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 Fri, May 29, 2015 at 4:43 PM, BEN KOPACZ <ben.kopacz@gmail.com> wrote: > Hello Art, > > Thank you for your expert answers! I was indeed planning on using dostats. > I'd > like to try your proposed method: > > - Set the new first extent and next extent sizes. > > - Execute the DEFRAGMENT SQL API function for the target table: > > execute function sysadmin:task( 'DEFRAGMENT', 'database:tablename' );> > Provided that it would work with IDS Version 11.50.FC5, which is what we're > using. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f90ea2747cb05173eadc7 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.