Re: Optimizer

Hi,

There are 2 types of views.
Oracle always tries to resolve your view into the original tables before executing the statement. In most views this works ok (views type 1). However there are some views, Oracle can't resolve. This group (views type 2) include views with a group function like 'sum' or an inline pl/sql function.
I think you created a view of the second type. How can you overcome this problem ? It depends on the data you want to select. I see 2 kind of solutions:
Try to rewrite the view using in-line pl/sql functions (functions created in a package where wnps, wnds pragma ) or use e temporary table.

Success,

Matthieu
In article <t3iheu5p54de15_at_corp.supernews.com>,   "SHRIKE" <tdeseamus_at_impacthire.com> wrote:
> I have some PL/SQL code that retrieves records based on user supplied
> criteria that runs against a view. The optimizer forces the view to
 be run
> before the index and a full table scan to be run against the view.
 This has
> caused the operation to slow down considerably. Does anyone know of
 a way
> to force the index to be run before the view? We have tested
 replacing the
> view with imbedded sql statements, and this works, however this
 course would
> involve a great deal of code re-write.
>
> Thanks,
> Tom
>
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Dec 18 2000 - 11:18:04 CET