Re-write oracle query to avoid scanning multiple tables
I hope everyone is okay and more will find out. I need advice on the choice and its fine tuning. I am using Oracle 11gR2. Below are the tables and data scenarios.
create table employee (emp_id number, emp_name varchar2(50), manager_id number);
create table department (dept_id number, dept_name varchar2(50), emp_name varchar2(50), manager_level varchar2(20));
create table manager_lookup (manager_level_id number, manager_level varchar2(20));
insert into employee values (1, 'EmpA',3);
insert into employee values (2, 'EmpB',1);
insert into employee values (3, 'EmpC',1);
insert into employee values (4, 'EmpD',2);
insert into employee values (5, 'EmpE',1);
insert into employee values (6, 'EmpF',3);
insert into department values (1, 'DeptA','EmpD','Level3');
insert into department values (2, 'DeptB','EmpC','Level2');
insert into department values (3, 'DeptC','EmpA','Level1');
insert into department values (4, 'DeptD','EmpF','Level1');
insert into department values (5, 'DeptD','EmpA','Level3');
insert into department values (6, 'DeptA',NULL,'Level3');
insert into manager_lookup values (1, 'Level1');
insert into manager_lookup values (2, 'Level2');
insert into manager_lookup values (3, 'Level3');
commit;
Below query returns me dept_id, passing in some name emp_name. I need those dept_id where manager_level is the same as emp_name, but don't need to have the same emp_ in the result dataset.
SELECT b.dept_id
FROM (SELECT DISTINCT manager_level
FROM department dpt
WHERE emp_name = 'EmpA'
and emp_name is not null) a,
department b
WHERE a.manager_level = b.manager_level
AND NVL (b.emp_name, 'ABC') <> 'EmpA';
A dataset is returned above the request, as shown below:
dept_id
--------
1
4
6
I want the same result set, but you need to rewrite the above query to avoid scanning the department table twice. This is just a sample query, but real-time scans of a large table twice gives performance issues. I want to rewrite this query in a way that works better and avoids the same table scan twice.
Can you help provide your great suggestions or solutions? I will be very grateful for all the answers.
Thank you for your question.
source to share
Also, you have redundant null check that can avoid indexes ...
SELECT b.dept_id
FROM (SELECT manager_level
FROM department dpt
WHERE emp_name = 'EmpA') a,
department b
WHERE a.manager_level = b.manager_level
AND NVL (b.emp_name, 'ABC') <> 'EmpA';
post your plan of explanation for more help
source to share
This should work:
SELECT a.*
FROM
(
SELECT d.*,
SUM(CASE WHEN emp_name = 'EmpA' THEN 1 ELSE 0 END)
OVER (PARTITION BY manager_level) AS hits
FROM department d
) a
WHERE hits > 0
AND NVL(emp_name, 'Dummy') <> 'EmpA'
ORDER BY dept_id
;
The request does the following:
- Calculate how many times
EmpA
appears in a givenmanager_level
- Save all entries with
manager_level
which has at least one occurrenceEmpA
in it - Excluding the records themselves
EmpA
SQL Query script in action: http://sqlfiddle.com/#!4/a9e03/7
You can verify that the execution plan contains only one full scan of the table.
source to share