Re: Tetra and Update Statistics

Andrew wrote: > Mark D. Stock wrote: > >>As Andrew said, running UPDATE STATISTICS LOW will update things >>like record counts, so shouldn't deteriorate any query, but may not >>help either. > > > I would expect that if they *have* defined indexes, then the engine must > know that there are some rows, or it will ignore the indexes even when the > erstwhile PK index is available and being targeted. I mean, even a flat-file > style access will surely say "gimme the record with serial number 207683" so > an index on the serial number should be known to contain rows or the engine > will think it's quicker to scan serially. I don't think it will ignore a direct unique key filter, and will assume a row count of one. I don't think UPDATE STATS will affect the use of an index for single key values, it does for multiple key values, storing something like the 2nd min & max values for a LOW, and obviously data distribution details for a MEDIUM and HIGH. I've not seen problems hitting an index in Baan for example. > As for distribution stats, I'm still boggled that entire applications like > BAAN can be written so that it uniformly dies in the ar5e when the engine is > given some clue about the data stored in all the tables :- can anyone > explain this in words of less than 5 syllables? Not really, no. I can tell you what I know though. Baan creates about 10 unique indexes per table based on columns that get added using their own hashing algorithm to generate the key values. Whenever they need a record they choose the appropriate key to use and return a single record. Then to join to another table they do the same..., until all records are returned. Whilst the optimiser has no problem using indexes, it has no control over the overall query process and therefore path. Another consideration is the time to run UPDATE STATS, even a LOW. Each Baan company has about 2200 tables, and each site might have about 15 or more companies set up with dev & test ones. Yes, that is 30000 or more tables in a single database! As there is no noticeable benefit to running UPDATE STATS, most sites don't waste the CPU. Having said that, I have run UPDATE STATS on Baan systems to help the performance of external queries, but been very careful to select individual tables. Things like data extractions for DW builds. I guess it's one of those things that has to be seen to be believed. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list