Update statistics

Hi 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. Thanks in advance: Martyn martyn.hodgson@zurich.co.uk

Perhaps changing your OPTCOMPIND value (I assume you have 0 now) to 1 or 2 would dis-favor indexes and prefer hashes. Doug. Martyn Hodgson wrote in message <7vmptn$dhu$1@news.xmission.com>... > >Hi > >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. > >Thanks in advance: > >Martyn >martyn.hodgson@zurich.co.uk >