QUERY Optimizer

Christian Hansen write: >I have observed that the optimizer (SQL 4.0 and an On-Line 4.0 DB) is >very often guessing wrong, even if we run update statistics. > >The estimated number of rows returned does very much depend on the >sequence in which you enter the SELECT statement. In one case we had >3 different SELECT statements, which should all return the same number >of rows, but the optimizer estimited the number of rows returned to >250, 170894 and 262477 !!!! > >Besides running update statistics frequently, are there any rules/hints >which will help the optimizer to optimize instead of pure guessing. The Estimated # of Rows Returned shown in sqexplain.out isn't a very good statistic and should probably be ignored. The Estimated Cost and the access paths that are shown are much more important. Because it's not able to guess the number of rows doesn't mean that it will not optimize correctly. It uses the total number of rows in each table being joined to determine things like which index to use and which table to read first. Warren J. Saccente Bellcore, Piscataway, NJ