Update Statistics

I am using INFORMIX-SQL Version 7.20.UD1 running on a Sun-ultra enterprise. The problem I am having is with the update statistics high whenever it is done on a table of the following configuration for example Table Number name char(30), number char(15) Table Account number integer, account char(30), address char(30) The number field (Number table) is the important thing. It will have a constant structure of "00000048912-001" where the first 11 characters will always be numeric. It can them be linked in the following way select * from number, account where number.number[1, 11] = account.number This would work fine and the records will be retrieved. The moment I do an update statistics high on the number table the same select statement would generate error number -1213 (A character to numeric conversion process failed). If an update statistics low or medium is done then the statement works fine. The database does have transaction logging on if that is a help. Any help would be greatly appreciated.

The reason is that the higher quality stats the HIGH is generating is causing the optimizer to select a query plan that uses an index or a hash table and to make the table account the primary table in the query plan. Either way the Number table's number column substring is being converted to an integer to look it up in the index on the number column in hte account table. However there must be some NULLs of blanks in the number table that are not convertable to integer and this is where the problem comes in. With stats low or medium it looks to the optimizer that it is better to convert the account table's number column to string and compare that to the number substring in the number table. Work arounds to try: o add a "number.number IS NOT NULL" clause o add a "number.number[1,11] != " " clause o break the number.number column into two integer columns and forget this match integer to string nonsense. Art S. Kagel "Alvan Rowland Jr." wrote: > > I am using INFORMIX-SQL Version 7.20.UD1 running on a Sun-ultra enterprise. > > The problem I am having is with the update statistics high whenever it is done > on a table of the following configuration for example > > Table Number > name char(30), > number char(15) > > Table Account > number integer, > account char(30), > address char(30) > > The number field (Number table) is the important thing. It will have a > constant structure of "00000048912-001" where the first 11 characters will > always be numeric. It can them be linked in the following way > > select * > from number, account > where number.number[1, 11] = account.number> > This would work fine and the records will be retrieved. The moment I do an > update statistics high on the number table the same select statement would> generate error number -1213 (A character to numeric conversion process > failed). > > If an update statistics low or medium is done then the statement works fine. > > The database does have transaction logging on if that is a help. > > Any help would be greatly appreciated.

First of all, I already checked the entire table for nulls and field structure consistency. There were no nulls blanks or anything other than 0's and numbers from 1 - 9. I even unloaded the table an ran a nawk script for just those checks. Second, Please do not be too critical of the reason why I chose to link in this way. There is a logical hierarchy that would eventually link the two tables. There would be a third table for instance that would contain both the character and numeric fields and that would be used as the bridge. However, the direct link was more efficient. Personally I still do not see the reason why this should fail at such a point regardless of the index. This would mean that Informix needs to post a warning to tell its user's that once the statistics have been updated high the character to numeric conversion no longer works even if the character is a number. "Art S. Kagel" wrote: > The reason is that the higher quality stats the HIGH is generating is > causing the optimizer to select a query plan that uses an index or a > hash table and to make the table account the primary table in the query > plan. Either way the Number table's number column substring is being > converted to an integer to look it up in the index on the number column > in hte account table. However there must be some NULLs of blanks in > the number table that are not convertable to integer and this is where > the problem comes in. With stats low or medium it looks to the > optimizer that it is better to convert the account table's number > column to string and compare that to the number substring in the number > table. > > Work arounds to try: > > o add a "number.number IS NOT NULL" clause > o add a "number.number[1,11] != " " clause > o break the number.number column into two integer columns and forget > this match integer to string nonsense. > > Art S. Kagel > > "Alvan Rowland Jr." wrote: > > > > I am using INFORMIX-SQL Version 7.20.UD1 running on a Sun-ultra enterprise. > > > > The problem I am having is with the update statistics high whenever it is done > > on a table of the following configuration for example > > > > Table Number > > name char(30), > > number char(15) > > > > Table Account > > number integer, > > account char(30), > > address char(30) > > > > The number field (Number table) is the important thing. It will have a > > constant structure of "00000048912-001" where the first 11 characters will > > always be numeric. It can them be linked in the following way > > > > select * > > from number, account > > where number.number[1, 11] = account.number> > > > This would work fine and the records will be retrieved. The moment I do an > > update statistics high on the number table the same select statement would> > generate error number -1213 (A character to numeric conversion process > > failed). > > > > If an update statistics low or medium is done then the statement works fine. > > > > The database does have transaction logging on if that is a help. > > > > Any help would be greatly appreciated.

There is a long running argument amoung DBAs about whether to make keys intelligent, as you apparently have, or not and what the pitfalls of doing so are. You have run into one of the most common pitfalls. Your scheme to add a 'join' table, as you have already stated, is inefficient. The best solution to this problem is to use serial numbers as primary and foreign keys and let the intelligent key and its parts be lookup only secondary keys for interfacing with whatever external records contain them. I'm not being critical, just practical. If I found you beating your head against a wall literally instead of figuratively it would not be critical of me to mention that it might feel good to stop! Art S. Kagel "Alvan Rowland Jr." wrote: > > First of all, I already checked the entire table for nulls and field structure > consistency. There were no nulls blanks or anything other than 0's and numbers from > 1 - 9. I even unloaded the table an ran a nawk script for just those checks. > > Second, Please do not be too critical of the reason why I chose to link in this > way. There is a logical hierarchy that would eventually link the two tables. There > would be a third table for instance that would contain both the character and > numeric fields and that would be used as the bridge. However, the direct link was > more efficient. Personally I still do not see the reason why this should fail at > such a point regardless of the index. This would mean that Informix needs to post a > warning to tell its user's that once the statistics have been updated high the > character to numeric conversion no longer works even if the character is a number. > > "Art S. Kagel" wrote: > > > The reason is that the higher quality stats the HIGH is generating is > > causing the optimizer to select a query plan that uses an index or a > > hash table and to make the table account the primary table in the query > > plan. Either way the Number table's number column substring is being > > converted to an integer to look it up in the index on the number column > > in hte account table. However there must be some NULLs of blanks in > > the number table that are not convertable to integer and this is where > > the problem comes in. With stats low or medium it looks to the > > optimizer that it is better to convert the account table's number > > column to string and compare that to the number substring in the number > > table. > > > > Work arounds to try: > > > > o add a "number.number IS NOT NULL" clause > > o add a "number.number[1,11] != " " clause > > o break the number.number column into two integer columns and forget > > this match integer to string nonsense. > > > > Art S. Kagel > > > > "Alvan Rowland Jr." wrote: > > > > > > I am using INFORMIX-SQL Version 7.20.UD1 running on a Sun-ultra enterprise. > > > > > > The problem I am having is with the update statistics high whenever it is done > > > on a table of the following configuration for example > > > > > > Table Number > > > name char(30), > > > number char(15) > > > > > > Table Account > > > number integer, > > > account char(30), > > > address char(30) > > > > > > The number field (Number table) is the important thing. It will have a > > > constant structure of "00000048912-001" where the first 11 characters will > > > always be numeric. It can them be linked in the following way > > > > > > select * > > > from number, account > > > where number.number[1, 11] = account.number> > > > > > This would work fine and the records will be retrieved. The moment I do an > > > update statistics high on the number table the same select statement would> > > generate error number -1213 (A character to numeric conversion process > > > failed). > > > > > > If an update statistics low or medium is done then the statement works fine. > > > > > > The database does have transaction logging on if that is a help. > > > > > > Any help would be greatly appreciated.

Specifically to Art, let me first say that I appreciate your help in this matter and if I was a bit harsh I sincerely apologize. You hit the nail on the head when you said I was beginning to beat my head on the wall. Well its seems that the final answer is that unless we never update our tables high, I will have to find another way to do this table join. I was hoping that this was just a bug that informix had overlooked but since you mentioned that this is on ongoing debate, then the prospect of it getting settled will not be soon enough for me. Thanks for you help anyway and to the people of informix let me side with those who would prefer the intelligence of the database be determined by the DBA or programmers. I think the very least you guys should do, as mentioned before, is inform the user's of this short coming. "Art S. Kagel" wrote: > There is a long running argument amoung DBAs about whether to make keys > intelligent, as you apparently have, or not and what the pitfalls of > doing so are. You have run into one of the most common pitfalls. Your > scheme to add a 'join' table, as you have already stated, is > inefficient. The best solution to this problem is to use serial > numbers as primary and foreign keys and let the intelligent key and its > parts be lookup only secondary keys for interfacing with whatever > external records contain them. > > I'm not being critical, just practical. If I found you beating your > head against a wall literally instead of figuratively it would not be > critical of me to mention that it might feel good to stop! > > Art S. Kagel > > "Alvan Rowland Jr." wrote: > > > > First of all, I already checked the entire table for nulls and field structure > > consistency. There were no nulls blanks or anything other than 0's and numbers from > > 1 - 9. I even unloaded the table an ran a nawk script for just those checks. > > > > Second, Please do not be too critical of the reason why I chose to link in this > > way. There is a logical hierarchy that would eventually link the two tables. There > > would be a third table for instance that would contain both the character and > > numeric fields and that would be used as the bridge. However, the direct link was > > more efficient. Personally I still do not see the reason why this should fail at > > such a point regardless of the index. This would mean that Informix needs to post a > > warning to tell its user's that once the statistics have been updated high the > > character to numeric conversion no longer works even if the character is a number. > > > > "Art S. Kagel" wrote: > > > > > The reason is that the higher quality stats the HIGH is generating is > > > causing the optimizer to select a query plan that uses an index or a > > > hash table and to make the table account the primary table in the query > > > plan. Either way the Number table's number column substring is being > > > converted to an integer to look it up in the index on the number column > > > in hte account table. However there must be some NULLs of blanks in > > > the number table that are not convertable to integer and this is where > > > the problem comes in. With stats low or medium it looks to the > > > optimizer that it is better to convert the account table's number > > > column to string and compare that to the number substring in the number > > > table. > > > > > > Work arounds to try: > > > > > > o add a "number.number IS NOT NULL" clause > > > o add a "number.number[1,11] != " " clause > > > o break the number.number column into two integer columns and forget > > > this match integer to string nonsense. > > > > > > Art S. Kagel > > > > > > "Alvan Rowland Jr." wrote: > > > > > > > > I am using INFORMIX-SQL Version 7.20.UD1 running on a Sun-ultra enterprise. > > > > > > > > The problem I am having is with the update statistics high whenever it is done > > > > on a table of the following configuration for example > > > > > > > > Table Number > > > > name char(30), > > > > number char(15) > > > > > > > > Table Account > > > > number integer, > > > > account char(30), > > > > address char(30) > > > > > > > > The number field (Number table) is the important thing. It will have a > > > > constant structure of "00000048912-001" where the first 11 characters will > > > > always be numeric. It can them be linked in the following way > > > > > > > > select * > > > > from number, account > > > > where number.number[1, 11] = account.number> > > > > > > > This would work fine and the records will be retrieved. The moment I do an > > > > update statistics high on the number table the same select statement would> > > > generate error number -1213 (A character to numeric conversion process > > > > failed). > > > > > > > > If an update statistics low or medium is done then the statement works fine. > > > > > > > > The database does have transaction logging on if that is a help. > > > > > > > > Any help would be greatly appreciated.

Art S. Kagel wrote: > The reason is that the higher quality stats the HIGH is generating is > causing the optimizer to select a query plan that uses an index or a > hash table and to make the table account the primary table in the query > plan. Either way the Number table's number column substring is being > converted to an integer to look it up in the index on the number column > in hte account table. However there must be some NULLs of blanks in > the number table that are not convertable to integer and this is where > the problem comes in. With stats low or medium it looks to the > optimizer that it is better to convert the account table's number > column to string and compare that to the number substring in the number > table. I disagree. I don't think the Informix optimizer ever decides to convert the number column to string. This decision to always convert char to numeric is what caused certain other "strange" behavior that people have complained about on this forum, and why I recommend against doing implicit datatype conversions in queries (see my FAQ at http://www.geocities.com/SiliconValley/Bridge/4578). I would first verify that the first 11 characters of the field are always numeric. Then I would make a small reproducible test case and report this as a bug to Informix. June -- june_t@hotmail.com Still alive -- just when you thought it was safe to come out with the chocolate... Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.