Re: inline views & unions & other schemas; ora 9.2.0.6 bug?

thanks v. much Jonathan for your input. I shall try that when I get back in the office.   The workaround without the inline views for us would have been about 8-10 times slower so that was the last resort. Probably the no_merge should not be that bad.    

  Anyway, it was great seeing you at Lalandia ... and it's awesome seeing (the others I've met in Denmark) posting in this "underground" Oracle forum. Wow... this is fast becoming my second "AskTom", a virtual "Miracle" support forum, if I may.... ;-)    

  thanks for all the tips,
  Cosmin

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:   

If you can associate the problem with particular views in certain cases, then you may be able to bypass the problem by referencing that view with a no_merge hint. This is likely to make the query run more slowly - but slow may be better than crashing.

select /*+ no_merge(viewx) */ ..
from
... , viewx, ....
where ...

You may even find that a global view will work in this context (though I haven't tried it) , e.g. viewQ includes viewA

select /*+ no_merge( viewQ.viewA) */ ..
from
... , viewQ, ....
where ...

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "cosmin ioan" To: Sent: Friday, October 06, 2006 1:01 AM Subject: inline views & unions & other schemas; ora 9.2.0.6 bug?

hello all, I'm getting an ORA-03001 --"unimplemented feature" when I'm trying to select off a view based on other views (in turn based on other views... multiple levels, etc) containing UNIONS and INLINE VIEWS, in some pretty big ORA Apps modules.

I am **only** getting this error when querying from a different schema, and even then, it happens intermittently, on some instances, on some particular selects in a long UNION set.

I do not get the error when trying to select **from within** the owner's schema.
I've tried to reduce the problem to the simplest tables/queries but still cannot put my finger on the issue other than the fact that..the problem seems to go away if I do not use UNION to pull in more sets data.... however this is needed... I traced this to a Metalink article:

Subject: Ora-3001: "Unimplemented Feature" On Query Using "WITH" and FGAC Doc ID: Note:361345.1 Type: PROBLEM
Last Revision Date: 15-MAR-2006 Status: MODERATED

Has anyone seen anything like this before, ...how can this typically be circumvented?
(I have query_rewrite_enabled=true, etc)

my initial thought would be to create/populate some adhoc global temporary tables, do this preloading of a pretty huge ora apps summary beforehand.... or...fix this somehow (the part I'm interested).... or basically to turn
(simplistically speaking) a query such as
line1, 1,2,3,4,5,6 from dual (a horizontal layout) into a vertical one line1
1
2
3
4
5

in effect eliminating the current UNIONS which are causing me this current headache?

thx much,
Cosmin

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.13/463 - Release Date: 04/10/2006

--
http://www.freelists.org/webpage/oracle-l