Re: Update statistics

From: "Martyn Hodgson" <martyn.hodgson@eaglestar.co.uk> > >This is going to sound heretical, but here goes. I've recently changed the >statistics collected in our 50GByte database, from basically: > >high on first column on each index >medium on other indexed columns >low on the table > >We now collect the stats recommended for our version of ids (7.30 UC7 on >hp-ux). The application is a Data Warehouse, with (pretty well) a star >schema design. As seems typical of such designs, some of the queries have >to >join up to 10 tables. Most of these being small fact tables. > >Since we changed the stats collected, the optimiser has started to make >some >*very* bad choices when performing large joins. There is a clear tendancy >to >try to access tables via indexes and nested loop joins, rather than table >scans and hash joins. In one case this means peforming 27,000,000 index >accesses to find the 625 qualifying rows. The index is 4 levels deep. The >query now takes over 3 hours. Previously, it started by scanning the table >(7 way fragmented on fast EMC disk) and the whole thing took less than 15 >minutes. > >The queries mostly come via a client application, which won't allow >Optimiser hints. Running via dbaccess I can solve most problems with them, >using the AVOID_INDEX clause. > >The indexes we have are few, but enforce uniqueness and are used by smaller >queries, so I can't drop them. > >So, we're in the brown and smelly - unless anyone has some bright >suggestions. I'm loathed to go back to the simplistic stats collection >regeme we had before, as it meakes sense to me that some ad hoc queries >will >benefit from the greater stats detail. However, doing as the release >notes/performance guide suggest is causing real problems. If OPTCOMPIND is set to 0 in your onconfig file, try setting it to 2. HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com