Re: Stored Outline Not Used

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 20 Sep 2006 13:00:26 -0700
Message-ID: <1158782425.928932.225950@m7g2000cwm.googlegroups.com>

Steve Howard wrote:
> Steve Howard wrote:
> > 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?
> >
>
> I could however, get it to work when I passed the ORDER BY to the query
> itself (i.e., the re-written one), with no modifications...
>

OK, this works, but seems really kludgy, as I had to nest the ORDER BY in a subquery to get it to work...

SQL> truncate table t0919;

Table truncated.

SQL> insert into t0919 values(1);

1 row created.

SQL> insert into t0919 values(2);

1 row created.

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

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

PL/SQL procedure successfully completed.

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

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

  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select * from t0919;

         C

---

         2
         1

SQL> I am still missing something I'm sure... Received on Wed Sep 20 2006 - 15:00:26 CDT