Re: inline views & unions & other schemas; ora 9.2.0.6 bug?
- From: cosmin ioan <cosmini@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 6 Oct 2006 03:59:41 -0700 (PDT)
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@xxxxxxxxxxxxxxxxxx> 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
Other related posts:
