Here is an example SQL query used to calculate the average salary of employees on the remote database @richdb in each department in the local database whose department name starts with the letter “D”.
SELECT Avg(emp_salary),
emp_dept
FROM employee@richdb
WHERE emp_dept IN (SELECT dpt_id
FROM department
WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept
Here the following is the query plan of this SQL, it takes 9.16 seconds to finish. The query plan shows a Nested Loops from DEPARTMENT in local to EMPLOYEE in the remote database. Due to the size of the EMPLOYEE table being much larger than that of the DEPARTMENT table, the nested loop join path is not optimal in this case.
To ask Oracle to consider doing the join operation in the remote database @richdb, I added a Hint /*+ DRIVING_SITE(employee) */ to tell Oracle to use EMPLOYEE table’s database @richdb as the driving site for the distributed query.
SELECT /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
emp_dept
FROM employee@richdb
WHERE emp_dept IN (SELECT dpt_id
FROM department
WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept
The following query shows the driving site is changed to @richdb and remote retrieves DEPARTMENT data from the “local” database. Now the speed is improved to 5.94 seconds. But the query plan shows a little bit complicated, there is a view that is construed by a Hash Join of two “index fast full scan” of indexes from EMPLOYEE and DEPARTMENT.
I further change the SQL and added a dummy operation Coalesce(dpt_id,dpt_id) in the select list of the subquery to block the index fast full scan of the DEMPARTMENT table.
SELECT /*+ DRIVING_SITE(employee) */ Avg(emp_salary),
emp_dept
FROM employee@richdb
WHERE emp_dept IN (SELECT Coalesce(dpt_id,dpt_id)
FROM department
WHERE dpt_name LIKE ‘D%’)
GROUP BY emp_dept
The change gives the SQL a new query plan shown in the following, the performance significantly improved to 0.71 seconds. You can learn how the dummy operation Coalesce(dpt_id,dpt_id) affected the Oracle SQL optimizer decision in this example.
This kind of rewrite can be achieved by Tosska DB Ace for Oracle automatically, it shows that the rewrite is almost 13 times faster than the original SQL.
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited