This content has been marked as final. Show 16 replies
Maybe with this?
Dirty, but it should work.
Important: I can't alter the query, because it's executed by an independent software.In that case you lose...your independent software vendor should have been smarter than using this kind of queries.
I'm afraid you're forced to work with some 'generic/dynamic model' here?
I just realized...
There is a restriction in that package: it won't do rewrites of queries that involve SYS objects.
(last time I played with it)
So Martijn is right.
Too bad they have the 'SYS. ' prefix in there, otherwise you could have redirected the query to an ALL_OBJECTS you write yourself, using a private synonym.
But I really can't modify the query. Is the first step executed by Crystal Reports 2008 to obtain the objects then I have acess...
I don't think there's a way to tune Oracle views that are owned by SYS...
I suggest you create materialized views with DISTINCT clause you need. You can then query the view.
I tried this but it is slower ( about 30%) than DISTINCT
WITH dist_owner AS(
,row_number() over(PARTITION BY aob.owner ORDER BY aob.owner) AS rownumber
FROM all_objects aob
WHERE rownumber = 1;
You can't. Most likely you have a version of the Oracle database with a bug that has been fixed in the most current version and backported into some of the most recent patches.
One possibility is to create an object in the current schema with the same name. Based on how Oracle does its name resolution it will find the local object.
SQL> SELECT owner FROM all_views WHERE view_name = 'ALL_OBJECTS'; OWNER ------------------------------ SYS SQL> create view all_objects as select * from dual; View created. SQL> SELECT owner FROM all_views WHERE view_name = 'ALL_OBJECTS'; OWNER ------------------------------ SYS UWCLASS SQL>
Well, sorry, but it looks like it's out of your hands then...
What DB version are you on, by the way?
My DB version is: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
The bug is definitely fixed in 18.104.22.168 ... I was told it was in the 10.2.0.4 patch but apparently not if you are still having the issue which was caused by recursing through XML schema objects. Name resolution should solve the problem. Since you are working with Crystal Reports one easy solution for you is a table in the current schema named "ALL_OBJECTS" that you load as required. I demonstrated the technique above.
ALL_OBJECTS is prefixed with SYS. in his query...
Your 'independent third party software vendor' made this mistake, imo, and you can't change/aren't allowed to touch it:
where username not in ( 'SYS', 'SYSTEM' )See also:
and there's much more if you search on it.
They're causing your system way overhead.
SYS is not to be (ab)used, especially from 10G and onwards...
But the Crystal is using SYS.ALL_OBJECTS internally.
An approach was alter the internal code of the view, adding Oracle hints.
I will try to convince the DBA...
Exactly Hoek. In this case, I'm hands tied.