RE: Char and Varchar

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. When joining two tables with an equality statement: select a.col, b.col from tab1 a, tab2 b where a.key=b.key; The optimizer has the choice of using a nested loop (if there is an index on either key - although you may wind up with the dreaded auto-index) or in performing a dynamic hash join - the love of all DSS types. A hash join in this situation can be one to two orders of magnitude faster than a nested loop. At issue with the hash join is that each table is fully scanned. With a normal scan, each page scanned will be dumped into the buffers along with the associate buffer management that entails - forcing out perhaps more usefully cached data. With a light scan, the hash join gets it's own personal light scan buffer pools (how many depends on RA_PAGES and RA_THRESHOLD) which comes out of DS_TOTAL_MEMORY. Hence you don't muck up the cache, you don't have the same buffer management issues, and you can get a whole lot more memory for your join - depending on your DS_TOTAL_MEMORY setting. I have seen light scans work anywhere from 1.5 to 3 times faster than normal buffered reads. You can get light scans whenever you need to scan an entire table. They can monitored with onstat -g lsc (under IDS) and onstat -g scn (undex XPS). Pre-requisites to a light scan include: - table to be scanned must be larger than BUFFERS - isolation dirty read OR ( committed or repeatable) WITH a shared table table lock. - (undocumented) no varchars. (although XPS will do a light scan on varchars). I take issue with the ICP Performance exam (self-assessment) Item 4 - where they cloud up one of the answers "Light scans will be performed only for sequential scans that meet certain criteria" - answer 'c' is 'The operation being performed is a read-only operation or a DDL operation'. While you certainly would not expect to have any scanning during table creation, you most certainly can get a light scan during an index build. Sloppy. I would also (shameless self-plug) direct you to an (excellent) article on this topic in Lester's recent WAIUG newsletter entitled 'Tuning Informix DSS Queries'. cheers j. > -----Original Message----- > From: Andrew Hamm [mailto:ahamm@sanderson.net.au] > Sent: Monday, November 27, 2000 6:26 PM > To: informix-list@iiug.org > Subject: Re: Char and Varchar > > > > *) Jack Parker mentions that varchars cannot participate in > "light scans". > If that doesn't mean anything to you, this is it: > > IF you have an index on columns A, B ... AND you issue a > select which> only selects some of the columns in the index AND the > nature of the query > means the engine can use the index to get those results > (perhaps because of > suitable joining or filtering, or no better reason NOT to use > a light scan) > THEN the engine will merely get the "rows" from the index > without having to > look into the table data itself. Hence the scan is "lighter". > >

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. My FAQUpdates directory now contains two entries - Andrews and yours... and I had just updated the FAQ and cleared the backlog the other night! Arrgh!...my work is never done...stomp,stomp... back to the dark corner to bang out another FAQ update.. And don't complain that Section 8.61 How do I identify Server Versions from SQL? is duplicated somewhere in Section 6, I've already been informed. Guess I'll merge them into a section 5 Common SQL Questions section after all this seems to less an engine thing or just a DBA thing (for generic admin tools) , more something even DEVLOPERS might need to know.to get their software to handle differnet Informix versions. Grumpy axe-wielding old dwarven crone, David. PS "And my bones ache"..mutter,mutter. PPS Expect new FAQ update at www.smooth1.demon.co.uk soon!