Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question on stored outlines

Re: Question on stored outlines

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 06 Nov 2005 18:50:00 +0100
Message-ID: <dklfo9$s5k$02$1@news.t-online.com>


dbaplusplus_at_hotmail.com schrieb:
> I am using Oracle 9.2.0.5 on HP UNIX 11i. I am supporting EMC
> Documentum's
> canned application. I am using cost based optimizer. There is a query
> which is taking 7 hours to run (yes I have updated statistics on all
> table/indexes - used COMPUTE STATISTICS).
> I have isolated this query and when I run in sqlplus by adding a hint:
> /*+ RULE */, query takes less than a minute.
>
> Since I do not have access to Documentum's code, STORED OUTLINES seem
> like good candidate to add this hint.
>
> Based on my reading, to use ORACLE's stored outlines, one has to set
> a parameter in the session, for example ...
> ALTER Session SET USE_STORED_OUTLINES=true
> I do not see USE_STORED_OUTLINES as an init.ora parameter
>
> Since Documentum's code cannot be modified how STORED OUTLINES can
> really be used? Am I missing something? Why all the articles on Stored
> Outlines claim no code changes are required.
>
> Only option seems to me is to use a logon trigger and set this
> parameter.
> Are there any other options, will like to know from the experts.
>
>
> Thanks a lot.
>

Well, it is clearly documented in SQL Reference: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_64a.htm#2064651

<quote>
Specify the schema containing the materialized view log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the materialized view log in the schema of its master table. You cannot create a materialized view log for a table in the schema of the user SYS.
</quote>

And materialized view log is obviously needed for fast refresh. However according the documentation i would expect an error by attempt to create materialized view log as user sys ( at least on the 9.2.0.6 ), but i could as well reproduce your test.
It seems also *you cannot create* should be interpreted as *you should not create ... because it will not work anyway...*

Best regards

Maxim Received on Sun Nov 06 2005 - 11:50:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US