Re: Select with view join takes too long

Steve Cosner <stevec_at_zimmer.csufresno.edu> wrote in article <859567729.14264_at_dejanews.com>...
> I have created a view which sums units for a few rows within a large
> table. This view was created especially to use with selects for
> reports. However, we have found that when the view is used, the
> selects take much longer than expected.

When a view contains a GROUP BY, Oracle can't push any of the WHERE clause conditions which appear in a query on view into the SQL which gets passed to the server. In other words, the SQL statement which defines the view is executed and then the WHERE clause is applied as a filter to the result set. This is why you have a full table scan on your underlying table and such poor performance.

I don't know of any work around other than to avoid the view and code the GROUP BY directly in your SQL statement - as in your second SQL statement.

-- 
Guy Harrison

gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964