Re: ref cursor slow compared to sql query

From: Allan Streib <streib_at_cs.indiana.edu>
Date: 30 Jun 2004 09:27:03 -0700
Message-ID: <e334e4a7.0406300827.b00f9a3@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1088558340.690002_at_yasure>...

> Look at cast multiset.
> http://www.psoug.org/reference/cast.html

Thanks very much -- that's something I never really knew about. Interesting stuff.

I my case it did not work because my inline view query contains a union and that appears to be unsupported (in 9i R2); I get an "unimplemented feature" unless I remove the union.

Moreover it did not really resolve the performance problem.

A couple of things finally helped -- at the asktom.oracle.com site I found mention of a person with a similar problem, in which he got improved performance by *deleting* the statistics on a table. I tried this on the largest table in my view and the performance improvement was dramatic.

I obtained further improvement by adding additional predicates to my "resourceavailabilty" view which logically have no effect on the result but seem to guide the optimizer to a better choice of query plan.

Many thanks to the suggestions; although they did not help directly they did lead me to some other things that did.

Allan Received on Wed Jun 30 2004 - 11:27:03 CDT