Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

August 24, 2010

Alternative statement, rewrite query

Filed under: Uncategorized — orawiss @ 3:40 pm

Alternative statement

1. General overview :

Most of time, DBA does not have access to the application modules. Most of application developers do not have the adequate knowledge to write sql queries. Most of time, DBA receives calls during the night to fix slow instance response time.
What I suggest, DBA can sort out during his work hours “long common running queries”, try to optimize them and send the report to developers so they can do the changes. But, sometimes, nobody from development team has the knowledge to modify, test and maybe compile the application module with your “good” query. Oracle always cares about you, DBA; don’t worry.
The DBMS_ADVANCED_REWRITE package allows you to intercept specific SQL statements and replace them with alternative statements.

2. The Tip :

First, we will use our “wissem” oracle user to test the package. Let’s give the below privileges to wissem;

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO wissem;
Grant succeeded.
SQL> GRANT CREATE MATERIALIZED VIEW TO wissem;
Grant succeeded.
SQL>

We will start by creating our test logic model:

CREATE TABLE department (
id NUMBER,
name VARCHAR2(45)
)
/

ALTER TABLE department
ADD CONSTRAINT pk_department PRIMARY KEY (ID)
USING INDEX
PCTFREE 0;

BEGIN

FOR i IN 1..10000 LOOP
INSERT INTO department(ID,NAME) VALUES(i, ‘department’||i);

END LOOP;

COMMIT;

END;

CREATE TABLE employee (
ID NUMBER,
NAME VARCHAR2(45),
department_id NUMBER,
DEPARTMENT_SUB_ID NUMBER)
/
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (ID, department_id)
USING INDEX
PCTFREE 0;
ALTER TABLE employee
ADD CONSTRAINT fk_employee
FOREIGN KEY (department_id)
REFERENCES department(ID);

create index indx_department_id on EMPLOYEE (department_sub_id);

BEGIN

FOR i IN 1..10000 LOOP
FOR j IN 1..10 LOOP
INSERT INTO employee(ID,NAME,department_id, DEPARTMENT_SUB_ID) VALUES(i, ’employee’||i, j, j);
END LOOP;
END LOOP;

COMMIT;

END;

Application module is performing the below query on our database with “wissem” user :
SQL> Explain plan for
2 SELECT COUNT(d.ID), d.name AS dept_name, e.name AS employee_name
3 FROM department d , employee e
4 WHERE d.id = 1
5 AND d.id = e.department_id
6 GROUP BY d.name, e.name
7 ;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2260082286
——————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc|
——————————————————————————–
| 0 | SELECT STATEMENT | | 4630 | 153K| |
| 1 | HASH GROUP BY | | 4630 | 153K| 656K|
| 2 | NESTED LOOPS | | 10025 | 332K| |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 18 | |
|* 4 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | | |
|* 5 | TABLE ACCESS FULL | EMPLOYEE | 10025 | 156K| |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – access(“D”.”ID”=1)
5 – filter(“E”.”DEPARTMENT_ID”=1)

18 rows selected

SQL>
From the explain plan, the application query is performing a full scan on employee table. Let’s tune our query now. We can use the indexed column DEPARTMENT_SUB_ID, see below:
SQL> Explain plan for
2 SELECT COUNT(d.ID), d.name AS dept_name, e.name AS employee_name
3 FROM department d , employee e
4 WHERE d.id = 1
5 AND d.id = e.DEPARTMENT_SUB_ID
6 GROUP BY d.name, e.name
7 ;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1506887749
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cos
——————————————————————————–
| 0 | SELECT STATEMENT | | 667 | 24679 |
| 1 | HASH GROUP BY | | 667 | 24679 |
| 2 | NESTED LOOPS | | 984 | 36408 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 18 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 984 | 18696 |
|* 6 | INDEX RANGE SCAN | INDX_DEPARTMENT_ID | 400 | |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – access(“D”.”ID”=1)
6 – access(“E”.”DEPARTMENT_SUB_ID”=1)

19 rows selected

SQL>
Now, we instruct Oracle to replace the primary query with the second alternative statements;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as wissem

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session altered

SQL>
SQL> BEGIN
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
3 name => ‘dept_emp_mytest’,
4 source_stmt =>
5 ‘SELECT COUNT(d.ID), d.name AS dept_name, e.name AS employee_name
6 FROM department d , employee e
7 WHERE d.id = 1
8 AND d.id = e.department_id
9 GROUP BY d.name, e.name’,
10 destination_stmt =>
11 ‘SELECT COUNT(d.ID), d.name AS dept_name, e.name AS employee_name
12 FROM department d , employee e
13 WHERE d.id = 1
14 AND d.id = e.DEPARTMENT_SUB_ID
15 GROUP BY d.name, e.name’,
16 validate => false,
17 rewrite_mode => ‘text_match’);
18 END;
19 /

PL/SQL procedure successfully completed

SQL>

Now let’s see which query is running when application module submits the first query;
SQL> Explain plan for
2 SELECT COUNT(d.ID), d.name AS dept_name, e.name AS employee_name
3 FROM department d , employee e
4 WHERE d.id = 1
5 AND d.id = e.department_id
6 GROUP BY d.name, e.name
7 ;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1506887749
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cos
——————————————————————————–
| 0 | SELECT STATEMENT | | 667 | 24679 |
| 1 | HASH GROUP BY | | 667 | 24679 |
| 2 | NESTED LOOPS | | 984 | 36408 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 18 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 984 | 18696 |
|* 6 | INDEX RANGE SCAN | INDX_DEPARTMENT_ID | 400 | |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – access(“D”.”ID”=1)
6 – access(“E”.”DEPARTMENT_SUB_ID”=1)

19 rows selected

SQL>

to delete :

EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => ‘dept_emp_mytest’);
Happy advanced rewrite package!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: