index updates

Hi, If i have several indexes on a table and i perform a update on the table, does all the indexes get updated parallely or they get updated one after the other( sequentially). Bye,

When you run update/delete/inserts on your table indexes will not be updated automatically in Informix. You will have to run update statistics command (for command usage see SQL reference guide) on the target table or Database. Normally DBA's run update statistics on database/Table periodically(weekly/monthly..etc). Thanks, Krishna Mohan Fidelity India. Any comments or statements made in this email are not necessarily those of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity Investments group companies. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you have received this in error, please contact the sender and delete the material from any computer. All e-mails sent from or to Fidelity Business Services India Pvt. Ltd. may be subject to our monitoring procedures. -----Original Message----- From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] Sent: Monday, March 14, 2005 11:45 AM To: ids@iiug.org Subject: index updates [4489] Hi, If i have several indexes on a table and i perform a update on the table, does all the indexes get updated parallely or they get updated one after the other( sequentially). Bye,

Of course the indexes will be updated automatically, immediately and with any insert, amend or delete. The DBS would be totally unusable otherwise !!! Update Statistics only allows the engine to determine the number of records in a table, or the distribution of values within a column. These values areused to determine the best index to use to fulfil a query. Not sure whether the index updates happen serially or in parallel, could depend on the fragmentation of the table and whether the indexes are detached or not. Not sure it really matters, if any part of the update fails the whole update will fail and all changes will rollout. Why do you ask? Keith -> -----Original Message----- -> From: Mohan, Krishna [mailto:krishna.mohan@fmr.com] -> Sent: Monday, March 14, 2005 8:32 AM -> To: ids@iiug.org -> Subject: RE: index updates [4490] -> -> -> When you run update/delete/inserts on your table indexes will not be -> updated automatically in Informix. -> You will have to run update statistics command (for command usage see -> SQL reference guide) on the target table or Database. -> -> Normally DBA's run update statistics on database/Table -> periodically(weekly/monthly..etc). -> -> Thanks, -> -> Krishna Mohan -> Fidelity India. -> -> -> Any comments or statements made in this email are not -> necessarily those -> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity -> Investments group companies. The information transmitted is intended -> only for the person or entity to which it is addressed and -> may contain -> confidential and/or privileged material. If you have received this in -> error, please contact the sender and delete the material from any -> computer. All e-mails sent from or to Fidelity Business -> Services India -> Pvt. Ltd. may be subject to our monitoring procedures. -> -> -> -> -----Original Message----- -> From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] -> Sent: Monday, March 14, 2005 11:45 AM -> To: ids@iiug.org -> Subject: index updates [4489] -> -> -> Hi, -> If i have several indexes on a table and i perform a update on the -> table, does -> all the indexes get updated parallely or they get updated -> one after the -> other( -> sequentially). -> -> -> -> Bye, -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

Not exactly. The indices are updated, but statistics for them are not. As to parallel or sequential, I'm not sure - I would think sequential. j. ----- Original Message ----- From: "Mohan, Krishna" <krishna.mohan@fmr.com> To: <ids@iiug.org> Sent: Monday, March 14, 2005 4:32 AM Subject: RE: index updates [4490] > When you run update/delete/inserts on your table indexes will not be > updated automatically in Informix. > You will have to run update statistics command (for command usage see > SQL reference guide) on the target table or Database. > > Normally DBA's run update statistics on database/Table > periodically(weekly/monthly..etc). > > Thanks, > > Krishna Mohan > Fidelity India. > > > Any comments or statements made in this email are not necessarily those > of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity > Investments group companies. The information transmitted is intended > only for the person or entity to which it is addressed and may contain > confidential and/or privileged material. If you have received this in > error, please contact the sender and delete the material from any > computer. All e-mails sent from or to Fidelity Business Services India > Pvt. Ltd. may be subject to our monitoring procedures. > > > > -----Original Message----- > From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] > Sent: Monday, March 14, 2005 11:45 AM > To: ids@iiug.org > Subject: index updates [4489] > > > Hi, > If i have several indexes on a table and i perform a update on the > table, does > all the indexes get updated parallely or they get updated one after the > other( > sequentially). > > > > Bye, > >

Generally people percieve that by adding additional indexes, the response times would go up. This is true but what would be the imapact on response times.Is there any method of of calculating the response times by adding additional indexes. Eg: Transaction T1(insert) using index I1 responds at r1. When the same transaction with additional index I2, what would be the response time?. Is there any method to estimate the T1 response time. Bye. "Simmons, Keith" <keith.simmons@office2office.biz> wrote: Of course the indexes will be updated automatically, immediately and with any insert, amend or delete. The DBS would be totally unusable otherwise !!! Update Statistics only allows the engine to determine the number of records in a table, or the distribution of values within a column. These values areused to determine the best index to use to fulfil a query. Not sure whether the index updates happen serially or in parallel, could depend on the fragmentation of the table and whether the indexes are detached or not. Not sure it really matters, if any part of the update fails the whole update will fail and all changes will rollout. Why do you ask? Keith -> -----Original Message----- -> From: Mohan, Krishna [mailto:krishna.mohan@fmr.com] -> Sent: Monday, March 14, 2005 8:32 AM -> To: ids@iiug.org -> Subject: RE: index updates [4490] -> -> -> When you run update/delete/inserts on your table indexes will not be -> updated automatically in Informix. -> You will have to run update statistics command (for command usage see -> SQL reference guide) on the target table or Database. -> -> Normally DBA's run update statistics on database/Table -> periodically(weekly/monthly..etc). -> -> Thanks, -> -> Krishna Mohan -> Fidelity India. -> -> -> Any comments or statements made in this email are not -> necessarily those -> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity -> Investments group companies. The information transmitted is intended -> only for the person or entity to which it is addressed and -> may contain -> confidential and/or privileged material. If you have received this in -> error, please contact the sender and delete the material from any -> computer. All e-mails sent from or to Fidelity Business -> Services India -> Pvt. Ltd. may be subject to our monitoring procedures. -> -> -> -> -----Original Message----- -> From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] -> Sent: Monday, March 14, 2005 11:45 AM -> To: ids@iiug.org -> Subject: index updates [4489] -> -> -> Hi, -> If i have several indexes on a table and i perform a update on the -> table, does -> all the indexes get updated parallely or they get updated -> one after the -> other( -> sequentially). -> -> -> -> Bye, -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** ** __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

Hi, There is no way to estimate the response time since having the extra index will push the engine to update it (update the btree) every time an update/delete/insert is executed. At on point in time, adding a element (item) to an index will be fast if the engine does not need to rebalance the btree, but at certain time the engine will have to rebalance the btree and will take longer. It also depends on the depth of the btree (number of levels) and the type of the data that btree contains; the more rows you have the longer it takes to update the btree. So you cannot count on a linear extention of time if you add one index. Remember, having an extra index might help a certain query or queries, but might degrade the performance of some other parts of the applications that contain a lot of insert/delete/update statements since the extra index will have to be updated. Moreover, the extra index might also contaminate other selects since the optimizer will have to consider this extra index if present and the optimized will change not always for the best path (even though in general we suppose that the optimizer will find the best possible path). A good advice is to start by looking at the SQL statement that causes performance problems and try to analyse it and rewrite it if possible before considering adding an extra index. As I said in a previous mail, the indexes will get updated along the sql statement (UPDATE/INSERT/DELETE) sequentially in the order of the indexes for that tables. But as Keith said in one of the mails included, this does not matter and in is not important since the sum of the index updates is an integral part of the SQL statement itself, and if on fails, the whole thing will fail. Regards, Khaled Bentebal ConsultiX Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "Chakravarth...." <pcdudyala@yahoo.com> To: <ids@iiug.org> Sent: Thursday, March 17, 2005 6:27 AM Subject: RE: index updates [4529] > Generally people percieve that by adding additional indexes, the response times would go up. This is true but what would be the imapact on response times.Is there any method of of calculating the response times by adding additional indexes. > Eg: > Transaction T1(insert) using index I1 responds at r1. When the same transaction with additional index I2, what would be the response time?. Is there any method to estimate the > T1 response time. > > > Bye. > > "Simmons, Keith" <keith.simmons@office2office.biz> wrote: > Of course the indexes will be updated automatically, immediately and with > any insert, amend or delete. The DBS would be totally unusable otherwise !!! > Update Statistics only allows the engine to determine the number ofrecords > in a table, or the distribution of values within a column. These values are > used to determine the best index to use to fulfil a query. > Not sure whether the index updates happen serially or in parallel, could > depend on the fragmentation of the table and whether the indexes are > detached or not. Not sure it really matters, if any part of the update fails > the whole update will fail and all changes will rollout. > Why do you ask? > > Keith > > -> -----Original Message----- > -> From: Mohan, Krishna [mailto:krishna.mohan@fmr.com] > -> Sent: Monday, March 14, 2005 8:32 AM > -> To: ids@iiug.org > -> Subject: RE: index updates [4490] > -> > -> > -> When you run update/delete/inserts on your table indexes will not be > -> updated automatically in Informix. > -> You will have to run update statistics command (for command usage see > -> SQL reference guide) on the target table or Database. > -> > -> Normally DBA's run update statistics on database/Table > -> periodically(weekly/monthly..etc). > -> > -> Thanks, > -> > -> Krishna Mohan > -> Fidelity India. > -> > -> > -> Any comments or statements made in this email are not > -> necessarily those > -> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity > -> Investments group companies. The information transmitted is intended > -> only for the person or entity to which it is addressed and > -> may contain > -> confidential and/or privileged material. If you have received this in > -> error, please contact the sender and delete the material from any > -> computer. All e-mails sent from or to Fidelity Business > -> Services India > -> Pvt. Ltd. may be subject to our monitoring procedures. > -> > -> > -> > -> -----Original Message----- > -> From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] > -> Sent: Monday, March 14, 2005 11:45 AM > -> To: ids@iiug.org > -> Subject: index updates [4489] > -> > -> > -> Hi, > -> If i have several indexes on a table and i perform a update on the > -> table, does > -> all the indexes get updated parallely or they get updated > -> one after the > -> other( > -> sequentially). > -> > -> > -> > -> Bye, > -> > -> > > **************************************************************************** ****** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this message > is free from such problems. > **************************************************************************** ****** > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > >

Response time for transaction T1 will not be affected if at all by adding index I2 if the optimizer decides to continue using index I1 to process that query. As far as time to update or insert a row? Insert/Delete time will increase roughly by the ratio of the original number of indexes to the new number of indexes (actually you'll probably get closer if you add one (1) to both counts) on average. Node splitting and extent additions within individual indexes and the data area of the table will also affect any measurments but over time they average out. Update times will depend on how many keyed columns are modified for a particular row and how many indexes contain those columns, so that's harder to calculate without knowing the exact update statement. Art S. Kagel ----- Original Message ----- From: Chakravarth.... <pcdudyala@yahoo.com> At: 3/17 0:45 > Generally people percieve that by adding additional indexes, the response times > would go up. This is true but what would be the imapact on response times.Is > there any method of of calculating the response times by adding additional > indexes. > Eg: > Transaction T1(insert) using index I1 responds at r1. When the same transaction > with additional index I2, what would be the response time?. Is there any method > to estimate the > T1 response time. > > > Bye. > > "Simmons, Keith" <keith.simmons@office2office.biz> wrote: > Of course the indexes will be updated automatically, immediately and with > any insert, amend or delete. The DBS would be totally unusable otherwise !!! > Update Statistics only allows the engine to determine the number of records > in a table, or the distribution of values within a column. These values are> used to determine the best index to use to fulfil a query. > Not sure whether the index updates happen serially or in parallel, could > depend on the fragmentation of the table and whether the indexes are > detached or not. Not sure it really matters, if any part of the update fails > the whole update will fail and all changes will rollout. > Why do you ask? > > Keith > > -> -----Original Message----- > -> From: Mohan, Krishna [mailto:krishna.mohan@fmr.com] > -> Sent: Monday, March 14, 2005 8:32 AM > -> To: ids@iiug.org > -> Subject: RE: index updates [4490] > -> > -> > -> When you run update/delete/inserts on your table indexes will not be > -> updated automatically in Informix. > -> You will have to run update statistics command (for command usage see > -> SQL reference guide) on the target table or Database. > -> > -> Normally DBA's run update statistics on database/Table > -> periodically(weekly/monthly..etc). > -> > -> Thanks, > -> > -> Krishna Mohan > -> Fidelity India. > -> > -> > -> Any comments or statements made in this email are not > -> necessarily those > -> of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity > -> Investments group companies. The information transmitted is intended > -> only for the person or entity to which it is addressed and > -> may contain > -> confidential and/or privileged material. If you have received this in > -> error, please contact the sender and delete the material from any > -> computer. All e-mails sent from or to Fidelity Business > -> Services India > -> Pvt. Ltd. may be subject to our monitoring procedures. > -> > -> > -> > -> -----Original Message----- > -> From: PARAMESHWAR.... [mailto:pcdudyala@yahoo.com] > -> Sent: Monday, March 14, 2005 11:45 AM > -> To: ids@iiug.org > -> Subject: index updates [4489] > -> > -> > -> Hi, > -> If i have several indexes on a table and i perform a update on the > -> table, does > -> all the indexes get updated parallely or they get updated > -> one after the > -> other( > -> sequentially). > -> > -> > -> > -> Bye, > -> > -> > > ******************************************************************************** > ** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this message > is free from such problems. > ******************************************************************************** > ** > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com