 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / July 2008LIKE with oversize pattern forces table scan?
One of my colleagues is dealing with a Unicenter Helpdesk site and has a call open with CA (remember them?). The problem is that he's noticed that when a query includes a LIKE condition where the pattern contains no wild-card characters but the pattern is longer than the declared length of the column being tested, Ingres invariably does a table scan, even though there's a usable index. CA tech support have responded "Ingres has always behaved this way, and as far as we know, it always will, because of the way the architecture is designed." I can buy the first bit of that, but the second part sounds like baloney. Can anyone support or refute that? (Privately, it seems to me that it would be more correct to report an error when the pattern is too long to match any data. But that is another rant for another day.) Roy  SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. Hi Roy, The problem is not really LIKE. In this situation the keybld code determines that a match cannot occur and returns key no-match. Unfortunately, at the moment that causes just that query plan to be dropped instead of doing the better option of saying choose this qp as we need not even touch the data. I think this case is so infrequent that no-one has followed through the implications of making use of this strange case - it gets treated as key all match hence the table scan. Personally I'd consider this sort of problem more of a developer error and unlikely to happen in practice but if you think this likely to cause too much hassle then we could look at this further. Regards, Ian -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Roy Hann Sent: 08 July 2008 08:42 To: info-ingres@kettleriverconsulting.com Subject: [Info-Ingres] LIKE with oversize pattern forces table scan? One of my colleagues is dealing with a Unicenter Helpdesk site and has a call open with CA (remember them?). The problem is that he's noticed that when a query includes a LIKE condition where the pattern contains no wild-card characters but the pattern is longer than the declared length of the column being tested, Ingres invariably does a table scan, even though there's a usable index. CA tech support have responded "Ingres has always behaved this way, and as far as we know, it always will, because of the way the architecture is designed." I can buy the first bit of that, but the second part sounds like baloney. Can anyone support or refute that? (Privately, it seems to me that it would be more correct to report an error when the pattern is too long to match any data. But that is another rant for another day.) Roy SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres > Hi Roy, > The problem is not really LIKE. In this situation the keybld code > determines that a match cannot occur and returns key no-match. > Unfortunately, at the moment that causes just that query plan to be > dropped instead of doing the better option of saying choose this qp as > we need not even touch the data. Well that makes more sense. It isn't an "architectural" problem--which I took to mean they think "Ingres is deliberately designed to do it that way"--it is just a flat out bug. > I think this case is so infrequent that no-one has followed through the > implications of making use of this strange case - it gets treated as key > all match hence the table scan. Personally I'd consider this sort of > problem more of a developer error and unlikely to happen in practice but > if you think this likely to cause too much hassle then we could look at > this further. Thanks Ian. Unfortunately the developer error is deeply buried because the code is generated "on the fly" by Unicenter. I imagine dynamically construct queries will only get more common, and it is probably easy to miss the problem with smaller tables. The reason it came to attention in this case is because the table being scanned has 4 million 8kb pages, at a site with ~1,000 users likely to query it. This will need to go through channels (i.e. CA), but you've given me a much better explanation than they did. Roy > -----Original Message----- > From: info-ingres-bounces@kettleriverconsulting.com [quoted text clipped - 23 lines] > > Roy SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. [SNIP] > Thanks Ian. Unfortunately the developer error is deeply buried > because the code is generated "on the fly" by Unicenter. [\SNIP] Roy, Don't hold your breath about this bug being fixed anytime soon. CA announced last January that they are dropping Ingres with Unicenter:- http://supportconnectw.ca.com/public/uniservplus/infodocs/uni_thdparprod ann.asp Kind of makes it interesting to see what database Ingres Corp. will be using to handle our support calls! http://servicedesk.ingres.com/CAisd/pdmweb.ingres This is now going to cost my company a lot of money to migrate away from Ingres. Just one more reason to hate CA I guess.... ;-p Richard ***************************************************************************** DISCLAIMER The information contained in this e-mail is confidential and is intended for the recipient only. If you have received it in error, please notify us immediately by reply e-mail and then delete it from your system. Please do not copy it or use it for any other purposes, or disclose the content of the e-mail to any other person or store or copy the information in any medium. The views contained in this e-mail are those of the author and not necessarily those of AAH Pharmaceuticals Ltd. AAH Pharmaceuticals Ltd is a company incorporated in England and Wales under company number 123458 and whose registered office is at Sapphire Court, Walsgrave Triangle, Coventry, CV2 2TX ***************************************************************************** > [SNIP] >> [quoted text clipped - 8 lines] > http://supportconnectw.ca.com/public/uniservplus/infodocs/uni_thdparprod > ann.asp Yes, I've been aware of that for over a year. I laughed. However it is a big customer with a big problem and I am sure a sufficient amount of hissing and yelling will eventually get CA to request a fix. (It's a 2.6 system.) > Kind of makes it interesting to see what database Ingres Corp. will be > using to handle our support calls! > http://servicedesk.ingres.com/CAisd/pdmweb.ingres I doubt that replacing ServiceDesk would be much of a job compared with some of the other work that has already been done (and is still being done). Roy SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. [SNIP] > I doubt that replacing ServiceDesk would be much of a job > compared with some of the other work that has already been > done (and is still being done). [/SNIP] Roy, Well, when they do, I'd like to see what Ingres Corp. use and what their migration path was like. If there is such a thing as an Open Source ServiceDesk clone then I'd like to hear about it! Richard ************************************************************************ DISCLAIMER The information contained in this e-mail is confidential and is intended for the recipient only. If you have received it in error, please notify us immediately by reply e-mail and then delete it from your system. Please do not copy it or use it for any other purposes, or disclose the content of the e-mail to any other person or store or copy the information in any medium. The views contained in this e-mail are those of the author and not necessarily those of AAH Pharmaceuticals Ltd. AAH Pharmaceuticals Ltd is a company incorporated in England and Wales under company number 123458 and whose registered office is at Sapphire Court, Walsgrave Triangle, Coventry, CV2 2TX ************************************************************************ > [SNIP] >> I doubt that replacing ServiceDesk would be much of a job [quoted text clipped - 9 lines] > If there is such a thing as an Open Source ServiceDesk clone then I'd > like to hear about it! Somewhere I've got a note of a company with something that might be suitable. I tried to get them to exhibit at the 2007 IUA conference because they claimed their product would run on Ingres. I'll try to dig out the details. Roy SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. > Somewhere I've got a note of a company with something that might be > suitable. I tried to get them to exhibit at the 2007 IUA conference > because they claimed their product would run on Ingres. I'll try to > dig out the details. Here it is: http://www.livetime.com They've got a UK office. SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. May be something for the next OpenRoad sprint? :-) Ok - marathon maybe... -----Original Message----- From: info-ingres-bounces@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of Roy Hann Sent: 08 July 2008 11:38 To: info-ingres@kettleriverconsulting.com Subject: Re: [Info-Ingres] LIKE with oversize pattern forces table scan? David Richard wrote: > [SNIP] >> [quoted text clipped - 6 lines] > Don't hold your breath about this bug being fixed anytime soon. > CA announced last January that they are dropping Ingres with Unicenter:- http://supportconnectw.ca.com/public/uniservplus/infodocs/uni_thdparprod > ann.asp Yes, I've been aware of that for over a year. I laughed. However it is a big customer with a big problem and I am sure a sufficient amount of hissing and yelling will eventually get CA to request a fix. (It's a 2.6 system.) > Kind of makes it interesting to see what database Ingres Corp. will be > using to handle our support calls! > http://servicedesk.ingres.com/CAisd/pdmweb.ingres I doubt that replacing ServiceDesk would be much of a job compared with some of the other work that has already been done (and is still being done). Roy SignatureUK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres We've seen something similar, but not with a 'LIKE'. A query based on an INT field would table scan if the value was over the 32 bit limit. We had an issue open for a long time and they just ended up saying 'sorry, we can't do anything about that'. Andre ________________________________ From: info-ingres-bounces@kettleriverconsulting.com on behalf of Roy Hann Sent: Tue 08/07/2008 3:42 AM To: info-ingres@kettleriverconsulting.com Subject: [Info-Ingres] LIKE with oversize pattern forces table scan? One of my colleagues is dealing with a Unicenter Helpdesk site and has a call open with CA (remember them?). The problem is that he's noticed that when a query includes a LIKE condition where the pattern contains no wild-card characters but the pattern is longer than the declared length of the column being tested, Ingres invariably does a table scan, even though there's a usable index. CA tech support have responded "Ingres has always behaved this way, and as far as we know, it always will, because of the way the architecture is designed." I can buy the first bit of that, but the second part sounds like baloney. Can anyone support or refute that? (Privately, it seems to me that it would be more correct to report an error when the pattern is too long to match any data. But that is another rant for another day.) Roy -- UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009 Go to http://www.iua.org.uk/join to get on the mailing list. _______________________________________________ Info-Ingres mailing list Info-Ingres@kettleriverconsulting.com http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.