Re: Update Statistics - 7.23

Mark D. Stock wrote: > > 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. Okay, I'll re-post this for those of you that missed it the first time and those that sent me private email on the subject: -------------------------------------------------------------------- Dianne Pendleton wrote: > > I don't think SET OPTIMIZATION FIRST_ROWS is available until 7.3. Can > someone verify this? I think that's what I heard at the conference. This is available in version 7.22 as follows: SET OPTIMIZATION [ALL_ROWS | FIRST_ROWS] You must set OPTCOMPIND=2 in order to switch on the optimiser. But.... (We must have a "but" to make it more plausable :-) to quote from the blurb: " This feature was developed using the "Siebel client benchmark" queries as test cases. This feature is initially provided in OnLine 7.22 and has been tested to address all the Siebel queries. Initially, this feature is only supported for a select number of VARs managed by ATG - ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Technology Partners. There may be queries written that are not addressed in this initial offering. A fuller solution has been slated for 7.3. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ " -------------------------------------------------------------------- And "no", I haven't done any benchmarks to determine which types of query benefit from this feature. ;-) 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"!|/ ////////| +----------------------+-----------------------------------+-----------+