Re: Update statistics...anytime?

'yvind Gjerstad wrote: > > Clem Akins <clem@informix.com> writes: > > > John Carlson answers: > > >Update statistics will lock the table in exclusive mode. It should> > >really take place when no one is on the system. We run our scripts > > >overnight. > > > > As of OnLine version 7.2? the UPDATE STATISTICS command places a > > much less restrictive lock on the table. There is still a penalty > > in performance as the engine reads the data, and the small possibility > > that your application will try to use the statistics at the exact > > instant they are being updated (in which case the app will fail and > > should re-try.) > > This seems to be the same for 7.14 > The table is _not_ locked exclusively. In VERY OLD versions (4.0), it used to place a lock (sorry, can't remember if it was exclusive or shared) on the table and hold it while it read the table. As of 4.10.UD1, it does not place any locks on the table until it has finished reading it, then it locks the system catalogs to update them. Provided you execute UPDATE STATISTICS outside of a transaction (this can be a problem if your database is mode ANSI), the locks are released as soon as it finishes updating the system catalogs for that table (this is true even if you run UPDATE STATISTICS on the entire database, and not just a table at a time). If you are in a transaction, the locks are held until you commit the transaction. I don't think this has changed in any version of ODS. -- June ---- June Tong Informix Software ---- ---- Senior Consultant (650) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ----