Re: Stored Outline Not Used

From: <thtsang_yh_at_yahoo.com.hk>
Date: 19 Sep 2006 21:16:38 -0700
Message-ID: <1158725798.312319.163940@i42g2000cwa.googlegroups.com>

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. Received on Tue Sep 19 2006 - 23:16:38 CDT