Re: update statistics not recommended!

Paul Brown wrote: > > Neil Truby (ntruby@netcomuk.co.uk) wrote: > : Well, sounds like bollocks to me, but does anyone else have any views > : or, better still, experience of this software packeage running with > : Informix? > > OK. It's true. There was a presentation at IWUC that went into > details about why this was the case, but I'll do a synopsis below. > > Basicly, Lawson's architecture wraps the DBMS inside their own > layer of services. All of the joins and aggregation are > done in this layer. So far as it is concerned the DBMS is > just a bit bucket. > > For example, they run the following kind of query a lot; > > SELECT * FROM Table > WHERE Order_Num > 100;> > Now, by default the optimizer is designed to get the total > query back and to use the least number of resources. It looks > at this query and it says (with good stats) "Well, I don't > want to scan on the index 'cos the selectivity of this query > is actually about 60% and that will incur lots and > lots of IO. I'll just scan the table." However, the query > is **really**. > > SELECT * FROM Table > WHERE Order_Num > 100 AND Order_Num < 120 > ORDER BY Order_Num;> > The middle-ware layer closes the cursor once it gets to > Order_Nums that are outside the range. Recall that the > software needs to deal with a variety of DBMS technologies, > some of which do **really bad things** with the range query. > > So instead, the idea is to no gather stats, which means the > optimizer says "Ah. I'll assume 10% on the predicate and use the > index!" This gets 'em the rows back in the right order. > > And before y'all hoot 'n holler too much, this is pretty much > standard practice in the ERP Vendor community. The > entire world is hamstrung by the requirement of running against > the 'lowest common demoninator' - O. And that's just the > way they like it . . . Let's don't forget Lawson's own database structure -- LADB. John Carlson Informix DBA WH Smith, Inc. #include std_disclaimer.h /* my opinions are my own */