On Mar 12, 6:16 pm, DA Morgan <damor...@psoug.org> wrote:

It looks like you might have a good work around, as long as the extra
predicate does not significantly affect the cost.  Here is an example:
BEGIN
  SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
  ( NAME             => 'T1_EQ2',
    SOURCE_STMT      => 'SELECT * FROM T1 WHERE C2=5',
    DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5
AND SYSDATE=SYSDATE',
    VALIDATE         => FALSE,
    REWRITE_MODE     => 'TEXT_MATCH' );
END;
/

SELECT
  *
FROM
  T1
WHERE
  C2=5;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));

SQL_ID  7q30ztw0h1w4t, child number 1
-------------------------------------
SELECT   * FROM   T1 WHERE   C2=5

Plan hash value: 3332582666

--------------------------------------------------------------------------- ----------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-
Time   | Buffers |
--------------------------------------------------------------------------- ----------
|*  1 |  FILTER            |      |      1 |        |    200 |
00:00:00.01 | 37 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    200 |    200 |
00:00:00.01 | 37 |
--------------------------------------------------------------------------- -----

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYSDATE@!=SYSDATE@!)
   2 - filter("C2"=5)

And now to put the system back to normal:
EXEC SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('T1_EQ2');

SQL_ID  7q30ztw0h1w4t, child number 0
-------------------------------------
SELECT   * FROM   T1 WHERE   C2=5

Plan hash value: 3586113557

--------------------------------------------------------------------------- -----------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-
Time   | Buffers |
--------------------------------------------------------------------------- -----------
|*  1 |  INDEX RANGE SCAN| IND_JT2 |      1 |    200 |    200 |
00:00:00.01 |  16 |
--------------------------------------------------------------------------- -----------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C2"=5)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.