thtsang_yh_at_yahoo.com.hk wrote:
> Steve Howard 寫道：
>
>> Steve Howard wrote:
>>> Shamallow wrote:
>>>> Instead of using outlines to achieve your goal, you should use
>>>> DBMS_ADVANCED_REWRITE package (on 10gR2) to add an "hidden" order by
>>>> clause to the faulty query
>>>>
>
> Thanks everybody. Shamallow's suggestion is interesting.
>
> But I even can't run Steve's sample. Error occurs when I define the
> query rewrite.
>
> 1 begin
> 2 sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
> 3 ('test_it_out',
> 4 'select * from t0919',
> 5 'select 2*c from t0919',
> 6 false);
> 7* end;
> SQL> /
> sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
> *
> ERROR 在行 2:
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
> ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
> ORA-06512: at line 2
>
> Any idea? I really don't know what the invalid character is.
>
> By the way, after I truncate the table and insert the data again, the
> stored outline worked as desired. I guess that the desired result will
> be returned by RBO only if the index exists when inserting data. This
> means the index cannot be re-created... But the SQL_TRACE is
> mysterious.
>
> May be I will need to take Morgan's suggestion.
What NLS Character Set? You can not use DBMS_ADVANCED_REWRITE with
UTF8: For example. It will generate the above error if you try.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Wed Sep 20 2006 - 00:10:25 CDT
