I'm hoping someone here can help me out with this problem. This is on Oracle 8.1.7 on a WindowsNT server.

I have defined a view on a table that uses an analytic function to return a subset of rows from the table. I chose to use the analytic function because it is much more efficient than the approach that was used before, which consisted of joining the table back to itself and was found to be somewhat non-performant with certain queries against large tables. I have checked the logic of the new view and it does return the correct answers that the application requires.

In my initial test, the analytic query approach was astoundingly better in both speed and in reducing logical/physical I/O. So I was happy. However, the view using the analytic function turned out to be much more inefficient in many cases than the old approach. :( I have been looking at this with some people and we have determined that the problem seems to be in how the CBO pushes the predicates, or doesn't push the predicates, when evaluating queries with this view. It seems that the CBO will only push a predicate based upon the columns in the PARTITION clause of the analytic function. When a query is written with a WHERE clause based upon the partitioning row, I can see that that predicate gets pushed "inside" the analytic function, the index on that column is used to select just the rows that we are interested in, the partitioning is applied to that small subset of rows, and the result is returned very efficiently.

But when I query this view using a WHERE clause based upon a column that is not part of the PARTITION clause of the analytic function, the performance is horrible. We have determined that this prediciate is NOT pushed "inside" the analytic function, so the analytic function is processing the WHOLE TABLE and then applying the WHERE clause predicate. This is quite disappointing because it is actually much worse than what was done in the application beforehand.

My questions are:

    1. Is this just how the CBO works with the analytic functions? Only predicates on the same column(s) as used in the partitioning clauses will be pushed "inside" of the analytic function to limit the number of rows processed?

    2. Or is my view construction just wrong and there is a better way to ask the same question so that the CBO will push the predicates as I hope?

I'll post a reply containing some SQL for the view, test data and queries that demonstrate what I am seeing. This should help if I didn't explain things well in this post.



[Edited by Heath on 04-16-2002 at 10:32 AM]