On Mar 12, 6:16 pm, DA Morgan <damor
...@psoug.org> wrote:
> Charles Hooper wrote:
> > *
> > ERROR at line 1:
> > ORA-30394: source statement identical to the destination statement
> > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
> > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
> > ORA-06512: at line 2
> > It would be nice to use DBMS_ADVANCED_REWRITE to add a hint, such as
> > (for the OP):
> > /*+ LEADING (HTS_ASSAY HTS_COMPOUND_LOT HTS_ASSAY_RESULT) */
> > And leave the rest of the SQL statement alone.
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
> I agree. Maybe you could fool the CBO by making some inconsequential
> change so that it would see the statement as different. For example:
> For example:
> WHERE C2+0 = x+0;
> Though, of course, that can cause unintended collateral damage.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
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.
