strange view performance....

Hi,
I'm working with an Oracle 8.0.6 server (we are going to migrate to a 9.2 version) and i found something very strange. I defined a view composed of 3 UNION ALL, every union all has 3-4 tables in joins. I known that oracle discourages the usage of complex view but i tried, the whole view performance looks good, a simple query like this: selec count(*) from myView
takes only 3 seconds to execute on 50.000 records, moreover the execution plan looks good (only index access on tables no full scan....). The very firste development was also having good performances, so i decided to exetend the usage of such view to others applications and i found a strange thing. In the new applications i defined a cursor like this: cursor myCur is

    select v.* from myView v where v.FieldName like 'NNN%N\_NN' escape '\' if i execute such query into an sql session, the only one record is found in about 5 seconds, but into an pl/sql package, the open of the cursor takes about 8 minutes !!!!!!!!!!
I solved the problen in this way:
cursor myCursor is

   select v.* from myView v
..
..
..

open myCursor
fetch myCursor into xxx
if xxx.fieldName like 'NNN%N\_NN' escape '\' then

    ....
end;

The performance now looks good, about 5 seconds to execute.

I'm trying to understand why the first cursor has suc poor performance....someone can help me????

thanks a lot
Stefano. Received on Tue Jun 15 2004 - 12:14:06 CDT