Hello Masters,
I am testing the two packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR and they work fine : for exemple I can remove an ORDER BY from a SELECT.
But, there is always an exception, I have problems with Hints : I want to remove them and it failed...
Here are my test cases.
A table with 10 000 rows, 9 900 with the name DUPONT and 100 with the name MARTIN.
SQL> create table TEST_HINT (ID NUMBER, NAME VARCHAR2(30 CHAR));
SQL> begin
for i in 1..10000
loop
insert into test_hint values (i, 'DUPONT');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> update test_hint set name = 'MARTIN' where mod(id, 100) = 0;
100 rows updated.
SQL> commit;
SQL> select count(*) from test_hint where name = 'MARTIN';
COUNT(*)
----------
100
I create the index and collect the stats.
SQL> create index idx_test_name on test_hint(name);
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'TEST_HINT', CASCADE=>true);
If I do a SELECT with NAME = 'MARTIN', Oracle use the index : it's OK.
SQL> set autotrace traceonly
SQL> select * from test_hint where name = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3244087319
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT | 100 | 1100 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 100 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='MARTIN')
If I do a SELECT with NAME = 'DUPONT', Oracle do a FTS : it's OK.
SQL> select * from test_hint where name = 'DUPONT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HINT | 9900 | 106K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='DUPONT')
If I use an INDEX hint, there is a problem because, with that SELECT, the cost raise from 9 to 72.
SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3244087319
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 72 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT | 9900 | 106K| 72 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 9900 | | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='DUPONT')
So, if I have a SELECT order generated by a vendor with a hint, I want to remove this one. But it doesn't work...
First test with DBMS_SQL_TRANSLATOR.
SQL> EXEC DBMS_SQL_TRANSLATOR.CREATE_PROFILE (profile_name =>'translate_hint');
SQL> EXEC DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION ( profile_name => 'translate_hint', sql_text => 'select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = ''DUPONT''', translated_text => 'select * from test_hint where name = ''DUPONT''', enable => TRUE);
SQL> alter session set sql_translation_profile = translate_hint;
Here, it is OK, the hint is removed.
SQL> DECLARE
translated_text CLOB;
BEGIN
DBMS_SQL_TRANSLATOR.TRANSLATE_SQL(
sql_text => 'select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = ''DUPONT''',
translated_text => translated_text);
DBMS_OUTPUT.PUT_LINE(translated_text );
END;
/
select * from test_hint where name = 'DUPONT'
SQL> alter session set events = '10601 trace name context forever, level 32';
SQL> set autotrace traceonly
Oops, problem, the index is used. Why?
SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3244087319
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 72 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT | 9900 | 106K| 72 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 9900 | | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='DUPONT')
What I want is that :
SQL> select * from test_hint where name = 'DUPONT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HINT | 9900 | 106K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='DUPONT')
OK, now I try with DBMS_ADVANCED_REWRITE to remove this hint.
My user has the role DBA.
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FORCE;
SQL> SHOW PARAMETER REWRITE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FORCE
query_rewrite_integrity string TRUSTED
Ouch, impossible again to remove it. Oracle says that both orders are equals.
SQL> exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT']', false);
BEGIN sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT']', false); END;
*
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 1
OK, I change the second order by adding ' and 1=1' to force Oracle to rewrite the order.
SQL> exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', q'[select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT']', q'[select * from test_hint where name = 'DUPONT' and 1= 1]', false);
PL/SQL procedure successfully completed.
SQL> select * from DBA_REWRITE_EQUIVALENCES;
OWNER
--------------------------------------------------------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------
SOURCE_STMT DESTINATION_STMT REWRITE_MO
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
HR
NEW_QUERY
select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPON select * from test_hint where name = 'DUPONT' and 1= 1 TEXT_MATCH
SQL> set autotrace trace
And now, if I use the hint... It failed, the index is still used!
SQL> select /*+ index(test_hint idx_test_name)*/ * from test_hint where name = 'DUPONT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3244087319
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 72 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_HINT | 9900 | 106K| 72 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 9900 | | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='DUPONT')
If I execute the rewrited order : it is OK.
SQL> select * from test_hint where name = 'DUPONT' and 1= 1;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 11101196
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 106K| 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HINT | 9900 | 106K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='DUPONT')
Well, can you tell me how to remove a hint from a SELECT with the both packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR?
Thank you very much for your answer.
David D from Paris (France)