#Records estimation for Derived tables. " Too many rows" error
We are facing a very frustrating problem in our reporting warehouse. We have given an adhoc BO universe to the business users. Users make their choices and execute the reports. Some of the metrics are derived using derived tables. If we have a static query we can always have the stats collected on the joined tables and the optimiser will have the high confidence records estimation and we can still control it somehow analysing the used filters. Now the problem we are facing is that since we can not collect stats on the derived tables so the optimiser is estimating records counts in millions which in turns fails the DBA set criteria in terms of the maximum records allowed hence we are getting "Too Many Rows" error and the queries are not executing.
Does anybody else faced the same issue. If yes how do we go about resloving this. Is it really suggestible to put a limit on the no of rows rather than on the time taken ?