Re: Stored Outline Not Used

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 19 Sep 2006 13:34:09 -0700
Message-ID: <1158698049.224884.54990@d34g2000cwd.googlegroups.com>

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
>
>
> DA Morgan a écrit :
>
> > thtsang_yh_at_yahoo.com.hk wrote:
> > > I am maintaining an old system. One stupid SQL statement used an "IN
> > > list" to select some records and relies on the output order, but it
> > > does not use an order by clause! It worked with the RBO. However, if
> > > CBO is used, the order is different and cause program error. I don't
> > > want to modify the source code.
> > >
> > > OS: Redhat Enterprise 4.0
> > > Oracle: 10g Release 2, 32 bit
> >
> > Modify the source code.
> > --
> > Daniel Morgan
> > University of Washington
> > Puget Sound Oracle Users Group

This was really interesting, as I have not used this. I could not get it to work with the ORDER BY, even with different validate and rewrite_mode parameters. What am I missing?

SQL> create table t0919(c number);

Table created.

SQL> insert into t0919 values(1);

1 row created.

SQL> insert into t0919 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> begin
  2 sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ('test_it_out',

  3                                                           'select *
from t0919',
  4                                                           'select
2*c from t0919',
  5                                                           false);

  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select * from t0919;

         C

---

         2
         4

SQL> exec
sys.dbms_advanced_rewrite.drop_rewrite_equivalence('test_it_out');

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2 sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ('test_it_out',

  3                                                           'select *
from t0919',
  4                                                           'select c
from t0919 order by c',
  5                                                           false);

  6 end;
  7 /
begin
*
ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination statement

ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

SQL>
SQL> select * from t0919;

         C

---

         1
         2

SQL> Thanks!

Steve Received on Tue Sep 19 2006 - 15:34:09 CDT