RE: Char and Varchar

(Tiptoe in to not disturb the over-worked FAQ-meister. Lots of moaning and groaning from same carefully shifted to the big bit bucket in the sky.) Before you go forth and throw stuff like that into the FAQ, would it perhaps make sense to have one person volunteer to create/update that specific section and others edit it - sort of like Andrew did with his lovely recap and I did with my rebuttal? In the end Andrew winds up with the contributing piece which is clean, any misstatements he's made get jumped on by everybody else so if Andrew missed something he learns from it, the end-user of the FAQ doesn't have to wind through umpteen sections to get the gist, and the FAQ-meister doesn't spend all night drinking coffee. And of course many thanks for taking on the onerous task of dealing with the FAQ. (Tiptoes quietly out) cheers j. Evidently the Supreme Court has decided to, in a reverse clone operation, merge the two people known as Al Gore and George Bush as the only fair way to resolve the US Presidential election tie. The only remaining point is what to call the new person. Various nominations have been made, it is suggested that you go to www.palmbeach.fla/vote to cast your preferences for: Bore [ ] Gush [ ] Gosh [ ] Gorsh [ ] (ala Goofey) If have been to the website - it is, of course, a 404. > > > > > >You make some excellent points, this sort of wrap up should > be included in > >the FAQ. > > My FAQUpdates directory now contains two entries - > > Andrews and yours... >

Parker, Jack wrote in message <900r2s$b9o$1@news.xmission.com>... > >You make some excellent points, this sort of wrap up should be included in >the FAQ. Although a light scan has a slightly different definition than the >one you offer. While it is true that one strategy to get faster reads is to >load an index with only the columns you wish to read, this is not >(necessarily, although I believe you can get a light scan on an index) a >light scan. Ooops yes - I mistook the point your were talking about. I'm talking about Key-Only Scans. Basically if the Index can be read to get all the interesting fields then it will do that instead of touching the table. Key-only scans are described in the published performance guide manual from informix. The effects of varchars on key-only scans are mentioned as an errata to that book, in the file PERFDOC_7.3 (and probably similar) within the release notes directory for the engine. No doubt later prints of the book will contain the paragraph shown in the file. No reason is given for the inability to exploit varchars in indexes for key-only scans, but I would hazard a guess that it's due to some performance optimisation in the way varchars are stored in indexes. I believe the problem is that in indexes, the full varchar is effectively stored with it's full possible length so that the index records are the same size and all "fields" of the index are at the same byte position, and because of a few compromises made in the name of performance, an index-only read cannot work with varchars - possibly because the true length of the varchar is not stored or not regarded? I'm starting to guess here and go outside what I know for a fact, so I'd better get into the manuals and confirm this - they are at home so I can't grab them now. (I need them to help me fall asleep...) However, lack of understanding of the why's does not detract from the fact. Thru scanning the releases directory for varchar to find this note, I've also seen quite a few more uglies regarding varchars. Many are bugs in the engine in peculiar circumstances, so in principle these bugs should not be worried about unduly (just ensure you have an engine version which fixes the ones that concern you). However there was an interesting one which relates back to the "full-of-blanks" varchar issue I mentioned. An unload of a zero-length varchar leaves an empty field in the .unl file - ie crap|nonsense||rubbish| see the empty field in there? And when it is subsequently loaded, it loads up as a NULL field. Damn. Sounds like it should be considered a bug also. But what can you do? I suppose the real problem is that unload files do not have a safe symbol to use for NULL. Anyway, it appears that the engine can successfully store zero-length varchars even though 4GL does not support them - it has always treated them as NULL strings every time I've played with them. To show the behaviour of 4GL with varchars, witness this little test program: define v varchar(10) main let v = "abc" display "<abc> <", v, ">" let v = "abc " display "<abc > <", v, ">" let v = NULL display "NULL <", v, ">" let v = "" display "<> <", v, ">" let v = " " display "< > <", v, ">" let v = " " display "< > <", v, ">" let v = v clipped display "v clipped <", v, ">" let v = "a " display "<a > clipped <", v clipped, ">" let v = " " display "< > clipped <", v clipped, ">" end main and the resultant output, which has some expected results, and a few surprises, in the results for NULL, a single blank, an "empty" string. <abc> <abc> <abc > <abc > NULL < > <> < > < > < > < > < > v clipped < > <a > clipped <a> < > clipped <> Please merge up the points from Jack, fix the bad grammar and appallingly weak humour and shove into said FAQs.