Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: CBO & different execution plans
On Mar 12, 6:16 pm, DA Morgan <damor..._at_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..._at_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' );
SELECT
*
FROM
T1
WHERE
C2=5;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SQL_ID 7q30ztw0h1w4t, child number 1
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
Predicate Information (identified by operation id):
1 - access("C2"=5)
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Mar 12 2007 - 17:44:03 CDT