As you know we have hardly below options to tune the query which actually we don't have control on it, might be generating third party pools like OBIEE etc.
| #
Modify Statistics # Add or Remove indexes # Modify Instance Parameter # Modify degree of parallelism of a table/index. # Use Stored Outlines. # Create Materialized Views. # Create View with embedded hints. # Advanced Query Rewrite (DBMS_ADVANCED_REWRITE ) |
The last one – advanced query rewrite – is the topic today. This technique is especially useful under following situations.
- Oracle 10g+
- When stored outline and sql profile do not help – they use hints to control the execution plan, but there are cases when hints are useless.
- Select, not DML
- With no bind variables
Advanced query rewrite is designed as an assistance to mview query rewrite, but with above conditions met, we can enjoy it’s power with non-mview queries.
Prof of concept:
I have below 2 queries...
#1. SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';
#2. SELECT ENAME FROM EMP WHERE JOB IN ('CLERK','SALESMAN');
I wanted to execute #2 query whenever I execute #1 query.
SQL> BEGIN
2 SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
3 name => 'test_rewrite',
4 source_stmt => 'SELECT ENAME FROM EMP WHERE JOB=''CLERK''
5 UNION
6 SELECT ENAME FROM EMP WHERE JOB=''SALESMAN''',
7 destination_stmt => 'SELECT ENAME FROM EMP WHERE JOB IN (''CLERK'',''SALESMAN'')',
8 validate => FALSE,
9 rewrite_mode => 'TEXT_MATCH');
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';
ENAME
----------
ADAMS
ALLEN
JAMES
MARTIN
MILLER
SMITH
TURNER
WARD
8 rows selected.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0arr3v0yafy3c, child number 0
-------------------------------------
SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP
WHERE JOB='SALESMAN'
Plan hash value: 3774834881
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | SORT UNIQUE | | 6 | 84 | 8 (63)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 42 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB"='CLERK')
4 - filter("JOB"='SALESMAN')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
23 rows selected.
SQL> ALTER SESSION SET query_rewrite_integrity = trusted;
Session altered.
SQL> SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP WHERE JOB='SALESMAN';
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
8 rows selected.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0arr3v0yafy3c, child number 1
-------------------------------------
SELECT ENAME FROM EMP WHERE JOB='CLERK' UNION SELECT ENAME FROM EMP
WHERE JOB='SALESMAN'
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 6 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB"='CLERK' OR "JOB"='SALESMAN'))
19 rows selected.
SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test_rewrite');
PL/SQL procedure successfully completed.
SQL>
This is how you can get control on non-modified queries. Enjoy:)
-Asifkhan P.
No comments:
Post a Comment