A Single Query with Many Filter Combinations – Part 3

In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns.
Here is the query from that post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id
    union all
    select rowid rid from employees where last_name = :last_name)
  group by rid
  having count(*) = nvl2(:department_id,1,0) + 
                    nvl2(:job_id,1,0) + 
                    nvl2(:manager_id,1,0) + 
                    nvl2(:last_name,1,0)
);

This is actually a simplification of a real task I handled some time ago.
In today’s post I’d like to look at a variation of the problem, which is closer to the real issue I handled.

In the previous example we had 4 columns that each one of them may, or may not, be filtered by. Now, let’s think of another case, where, in addition to the 4 optional filters, there is one mandatory filter.

For example, let’s extend the HR data model, so it will include companies, and each company has its own departments, jobs and employees. There should be no dependency between records from different companies.
So I’ll add a new table – COMPANIES, and a mandatory column – COMPANY_ID – to each of the other tables (DEPARTMENTS, JOBS, and EMPLOYEES). COMPANY_ID will become part of the primary key in every table, and all the foreign key constraints from EMPLOYEES to the other tables will include the COMPANY_ID column as well.

Here are the DDL statements for this model:

create table companies (
  company_id   number(4)    not null,
  company_name varchar2(30) not null,
  --
  constraint companies_pk primary key (company_id)
); 
  
create table departments (
  company_id      number(4)    not null, 
  department_id   number(4)    not null,
  department_name varchar2(30) not null,
  manager_id      number(6),
  location_id     number(4),
  --
  constraint departments_pk primary key (company_id,department_id)
);

create table jobs(
  company_id     number(4)    not null, 
  job_id         varchar2(10) not null,
  job_title      varchar2(35) not null,
  min_salary     number(6),
  max_salary     number(6),
  --
  constraint jobs_pk primary key(company_id,job_id)
);

create table employees (
  company_id     number(4)    not null, 
  employee_id    number(6)    not null,
  first_name     varchar2(20),
  last_name      varchar2(25) not null,
  email          varchar2(25) not null,
  phone_number   varchar2(20),
  hire_date      date         not null,
  job_id         varchar2(10) not null,
  salary         number(8,2),
  commission_pct number(2,2),
  manager_id     number(6),
  department_id  number(4),
  --
  constraint emp_pk primary key (company_id,employee_id),
  constraint emp_salary_min check (salary > 0),
  constraint emp_email_uk unique (company_id,email),
  constraint emp_dept_fk foreign key (company_id,department_id)
      references departments (company_id,department_id),
  constraint emp_job_fk foreign key (company_id,job_id)
      references jobs (company_id,job_id),
  constraint emp_manager_fk foreign key (company_id,manager_id)
      references employees (company_id,employee_id)
);

create index emp_department_ix on employees (company_id,department_id);
create index emp_job_ix on employees (company_id,job_id);
create index emp_manager_ix on employees (company_id,manager_id);
create index emp_name_ix on employees (company_id, last_name, first_name);

And now we want to modify the SQL query, so it will filter the employees by COMPANY_ID (always), and by zero or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME. For that I’ll make 3 changes in the original query:

  • One change is adding the condition on COMPANY_ID. Note how we can add it once, and let the optimizer push the predicate into each one of the UNION ALL branches.
  • A second change is adding another branch to the UNION ALL, to handle the case of zero optional filters (the original query handled one or more optional filters).
  • And finally we need to change the HAVING clause. The original expression in the HAVING clause is evaluated to the number of non-null variable values – between 1 and 4. Now, if all the optional variables are nulls it is evaluated to 0, but then (and only then) the new branch of the UNION ALL is executed, so we’d like to count exactly 1 result per rowid. Hence, we take the greatest number between 1 and the original expression.
  • Here is the revised query:

    select * from employees where rowid in (
      select rid from (
        select rowid rid,company_id from employees
            where department_id = :department_id
        union all
        select rowid rid,company_id from employees 
            where job_id = :job_id
        union all
        select rowid rid,company_id from employees 
            where manager_id = :manager_id
        union all
        select rowid rid,company_id from employees
            where last_name = :last_name
        union all
        select rowid rid,company_id from employees
            where :department_id is null
              and :job_id is null
              and :manager_id is null
              and :last_name is null)
      where company_id = :company_id
      group by rid
      having count(*) = greatest (1, nvl2(:department_id,1,0) + 
                                     nvl2(:job_id,1,0) + 
                                     nvl2(:manager_id,1,0) + 
                                     nvl2(:last_name,1,0) )
    );
    

    And here is the execution plan:

    ---------------------------------------------------------
    | Id  | Operation                   | Name              |
    ---------------------------------------------------------
    |   0 | SELECT STATEMENT            |                   |
    |   1 |  NESTED LOOPS               |                   |
    |   2 |   VIEW                      | VW_NSO_1          |
    |*  3 |    FILTER                   |                   |
    |   4 |     HASH GROUP BY           |                   |
    |   5 |      VIEW                   |                   |
    |   6 |       UNION-ALL             |                   |
    |*  7 |        INDEX RANGE SCAN     | EMP_DEPARTMENT_IX |
    |*  8 |        INDEX RANGE SCAN     | EMP_JOB_IX        |
    |*  9 |        INDEX RANGE SCAN     | EMP_MANAGER_IX    |
    |* 10 |        INDEX RANGE SCAN     | EMP_NAME_IX       |
    |* 11 |        FILTER               |                   |
    |* 12 |         INDEX RANGE SCAN    | EMP_DEPARTMENT_IX |
    |  13 |   TABLE ACCESS BY USER ROWID| EMPLOYEES         |
    ---------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter(COUNT(*)=GREATEST(1,NVL2(:DEPARTMENT_ID,1,0)+NVL2(:JOB_ID,1,0)+NVL2(:MANAGE
                  R_ID,1,0)+NVL2(:LAST_NAME,1,0)))
       7 - access("COMPANY_ID"=TO_NUMBER(:COMPANY_ID) AND
                  "DEPARTMENT_ID"=TO_NUMBER(:DEPARTMENT_ID))
       8 - access("COMPANY_ID"=TO_NUMBER(:COMPANY_ID) AND "JOB_ID"=:JOB_ID)
       9 - access("COMPANY_ID"=TO_NUMBER(:COMPANY_ID) AND
                  "MANAGER_ID"=TO_NUMBER(:MANAGER_ID))
      10 - access("COMPANY_ID"=TO_NUMBER(:COMPANY_ID) AND "LAST_NAME"=:LAST_NAME)
      11 - filter(:LAST_NAME IS NULL AND :MANAGER_ID IS NULL AND :JOB_ID IS NULL AND
                  :DEPARTMENT_ID IS NULL)
      12 - access("COMPANY_ID"=TO_NUMBER(:COMPANY_ID))
    

Leave a Reply

Your email address will not be published.