Re: update statistics not recommended!

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 . . .