Re: Update Statistics - 7.23
Art S. Kagel wrote: > > Mark Taylor wrote: > > > > Hi, > > > > We are looking for input on running Update Statistics on some > reasonably > > large tables (approximately 80 million rows) and are looking for a > > combination of reasonable speed and reasonably accruate results. We > > have the methods in the Informix Documentation and are not getting > the > > results we need. Anybody have any suggestions. The DBs are 7.23 on > Sun > > Solaris 2.5 platform. > > What do you mean you are not getting the results that you need. > Comared > to 5.0x? Compared to what? What are your needs? Need to force the > server to not sort? What? > > In general the recommendations in the release notes file SERVERS_7.2 > are the most up to date and are USUALLY good enough. However, the > 7.2x > optimizer is MUCH smarter than the 5.0x optimizer (and even improved > over the optimizer in 7.1x) and will often decide that, based on the > statistics and distributions, using a different index and sorting will > fetch the total select set more quickly and it is usually correct. If > you, rather, need the first few rows quickly this can be death. When > this is the case sometimes updating statistics HIGH on all columns > helps > but usually you will have to drop distributions and update statistics > LOW so that the optimizer behaves like the 5.0x optimizer did. If you only want the first few rows quickly, then you can also try: SET OPTIMIZATION FIRST_ROWS Use: SET OPTIMIZATION ALL_ROWS to switch back to the default. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+