Home > DBA, Oracle, SQL > A SQL Minute: Injudicious Use of Views (SQL AntiPatterns)

A SQL Minute: Injudicious Use of Views (SQL AntiPatterns)


From a book that I am writing called Practical SQL Using Oracle Database 11g:

Another example of a SQL AntiPattern is the injudicious use of views when we do have access to the underlying tables. If we only need the data in a subset of columns in the view, Oracle may nevertheless perform joins with tables that don’t concern us. The latest versions of Oracle Database can eliminate unnecessary inner joins and outer joins if certain conditions are met but, in my tests on the HR sample schema in Oracle Database 11g Release 2, the join elimination feature does not always work as advertised. In theory at least, if no data is required from the joined table, an outer join can be eliminated if there is a primary key constraint on the joined table which ensures that at most one row is found in the joined table corresponding to the joining value while an inner join can be eliminated if, in addition to the primary key constraint on the joined table, there is a referential integrity constraint between the two tables which ensures that at least one row is always found in the joined table when the joining value is not null. Semijoins and antijoins can also be eliminated under similar considerations.

Suppose that we need a few items from the Employees table and decided to get the data from the view Emp_details_view instead. The query plan shows that Oracle performed joins with the Departments, Locations, and Countries tables.

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 21 06:14:50 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

select first_name, last_name, salary
from emp_details_view
where department_id = 50;

select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL IOSTATS LAST'));

SQL_ID  g26hr6qqzmat6, child number 0
-------------------------------------
select first_name, last_name, salary from emp_details_view where
department_id = 50

Plan hash value: 3784967870

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |       |     5 (100)|          |     45 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS                  |                   |      1 |     45 |  1845 |     5   (0)| 00:00:01 |     45 |00:00:00.01 |      14 |
|   2 |   NESTED LOOPS                 |                   |      1 |      1 |    19 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   3 |    NESTED LOOPS                |                   |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN         | DEPT_ID_PK        |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS         |      1 |     23 |   138 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  7 |      INDEX UNIQUE SCAN         | LOC_ID_PK         |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|*  8 |    INDEX UNIQUE SCAN           | COUNTRY_C_ID_PK   |      1 |     25 |   150 |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES         |      1 |     45 |   990 |     3   (0)| 00:00:01 |     45 |00:00:00.01 |       9 |
|* 10 |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |      1 |     45 |       |     0   (0)|          |     45 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("D"."DEPARTMENT_ID"=50)
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   8 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
       filter("C"."REGION_ID" IS NOT NULL)
  10 - access("E"."DEPARTMENT_ID"=50)

Here is the text of the view Emp_details_view. We see that it involves the Departments, Locations, Countries, Regions, and Jobs tables. Oracle was successfully able to eliminate the joins with the Regions and Jobs tables but not the joins with the Departments, Locations, and Countries tables.

select text from user_views where view_name='EMP_DETAILS_VIEW';

TEXT
--------------------------------------------------------------------------------
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY

Observe that when Oracle eliminated the join with the Regions table, it added the check C.REGION_ID IS NOT NULL to the query plan indicating that C.REGION_ID is a nullable column. This check is not in the definition of the view but is implied by the join operation. Unfortunately, Oracle cannot perform the check without actually performing a join with the Countries table. But a join with the Countries table cannot be done without first going through the Departments table and then the Locations table. Therefore Oracle cannot eliminate the join with the Departments, Locations, and Countries tables. Also observe that Oracle did not add a similar check for D.JOB_ID when it eliminated the join with the Jobs table; this indicates that D.JOB_ID is not a nullable column.

I tried to eliminate the other joins by ensuring that all the foreign keys in the query were non-nullable. Oracle then eliminated the join with the Countries table but continued to perform the join with the Departments and Location tables. Notice that Oracle did not need to add the check C.REGION_ID IS NOT NULL—or a similar check for L.COUNTRY_ID—to the query plan.

-- Confirm that each table has a primary key

select ucc.table_name, ucc.constraint_name, ucc.column_name, utc.nullable
from user_cons_columns ucc
join user_tab_columns utc on (utc.table_name = ucc.table_name and utc.column_name = ucc.column_name)
where ucc.table_name in ('EMPLOYEES','DEPARTMENTS','JOBS','LOCATIONS','COUNTRIES','REGIONS')
and (ucc.owner, ucc.constraint_name, ucc.table_name) in (select owner, constraint_name, table_name from user_constraints where constraint_type='P')
order by ucc.table_name, ucc.constraint_name, ucc.position;

TABLE_NAME                     CONSTRAINT_NAME                COLUMN_NAME                    N
------------------------------ ------------------------------ ------------------------------ -
COUNTRIES                      COUNTRY_C_ID_PK                COUNTRY_ID                     N
DEPARTMENTS                    DEPT_ID_PK                     DEPARTMENT_ID                  N
EMPLOYEES                      EMP_EMP_ID_PK                  EMPLOYEE_ID                    N
JOBS                           JOB_ID_PK                      JOB_ID                         N
LOCATIONS                      LOC_ID_PK                      LOCATION_ID                    N
REGIONS                        REG_ID_PK                      REGION_ID                      N

-- Identify foreign keys that are nullable; that is, columns for which NULLABLE = 'N'

select ucc.table_name, ucc.constraint_name, ucc.column_name, utc.nullable, uc.r_constraint_name
from user_cons_columns ucc
join user_tab_columns utc on (utc.table_name = ucc.table_name and utc.column_name = ucc.column_name)
join user_constraints uc on (uc.owner = ucc.owner and uc.constraint_name = ucc.constraint_name and uc.table_name = ucc.table_name)
where ucc.table_name in ('EMPLOYEES','DEPARTMENTS','JOBS','LOCATIONS','COUNTRIES','REGIONS')
and (ucc.owner, ucc.constraint_name, ucc.table_name) in (select owner, constraint_name, table_name from user_constraints where constraint_type='R')
order by ucc.table_name, ucc.constraint_name, ucc.position;

TABLE_NAME                     CONSTRAINT_NAME                COLUMN_NAME                    N R_CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------ - ------------------------------
COUNTRIES                      COUNTR_REG_FK                  REGION_ID                      Y REG_ID_PK
DEPARTMENTS                    DEPT_LOC_FK                    LOCATION_ID                    Y LOC_ID_PK
DEPARTMENTS                    DEPT_MGR_FK                    MANAGER_ID                     Y EMP_EMP_ID_PK
EMPLOYEES                      EMP_DEPT_FK                    DEPARTMENT_ID                  Y DEPT_ID_PK
EMPLOYEES                      EMP_JOB_FK                     JOB_ID                         N JOB_ID_PK
EMPLOYEES                      EMP_MANAGER_FK                 MANAGER_ID                     Y EMP_EMP_ID_PK
LOCATIONS                      LOC_C_ID_FK                    COUNTRY_ID                     Y COUNTRY_C_ID_PK

-- Ensure that all foreign keys are non-nullable

SQL> alter table employees modify department_id not null;
alter table employees modify department_id not null
*
ERROR at line 1:
ORA-02296: cannot enable (HR.) - null values found

SQL> delete from employees where department_id is null;

1 row deleted.

SQL> alter table employees modify department_id not null;

Table altered.

SQL> alter table departments modify location_id not null;

Table altered.

SQL> alter table locations modify country_id not null;

Table altered.

SQL> alter table countries modify region_id not null;

Table altered.

--  Confirm that the above procedures were successful by checking that the foreign keys are no longer nullable

TABLE_NAME                     CONSTRAINT_NAME                COLUMN_NAME                    N R_CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------ - ------------------------------
COUNTRIES                      COUNTR_REG_FK                  REGION_ID                      N REG_ID_PK
DEPARTMENTS                    DEPT_LOC_FK                    LOCATION_ID                    N LOC_ID_PK
DEPARTMENTS                    DEPT_MGR_FK                    MANAGER_ID                     Y EMP_EMP_ID_PK
EMPLOYEES                      EMP_DEPT_FK                    DEPARTMENT_ID                  N DEPT_ID_PK
EMPLOYEES                      EMP_JOB_FK                     JOB_ID                         N JOB_ID_PK
EMPLOYEES                      EMP_MANAGER_FK                 MANAGER_ID                     Y EMP_EMP_ID_PK
LOCATIONS                      LOC_C_ID_FK                    COUNTRY_ID                     N COUNTRY_C_ID_PK

--  Repeat the query and check the query plan

SQL_ID  g26hr6qqzmat6, child number 0
-------------------------------------
select first_name, last_name, salary from emp_details_view where
department_id = 50

Plan hash value: 1401300344

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |       |     4 (100)|          |     45 |00:00:00.01 |      12 |
|   1 |  NESTED LOOPS                 |                   |      1 |     45 |  1575 |     4   (0)| 00:00:01 |     45 |00:00:00.01 |      12 |
|   2 |   NESTED LOOPS                |                   |      1 |      1 |    13 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK        |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|*  5 |    INDEX UNIQUE SCAN          | LOC_ID_PK         |      1 |     23 |   138 |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |      1 |     45 |   990 |     3   (0)| 00:00:01 |     45 |00:00:00.01 |       9 |
|*  7 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      1 |     45 |       |     0   (0)|          |     45 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"=50)
   5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   7 - access("E"."DEPARTMENT_ID"=50)

I then tried redefining the view using outer joins but that did not eliminate the unnecessary joins either. Go figure.

create or replace view emp_details_view2 as
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e
  LEFT OUTER JOIN departments d ON d.department_id = e.department_id
  LEFT OUTER JOIN locations l ON l.location_id = d.location_id
  LEFT OUTER JOIN countries c ON c.country_id = l.country_id
  LEFT OUTER JOIN regions r on r.region_id = c.region_id
  INNER JOIN jobs j ON j.job_id = e.job_id;

select first_name, last_name, salary
from emp_details_view2
where department_id = 50;

SQL_ID  ajgp3ndq10wn4, child number 0
-------------------------------------
select first_name, last_name, salary from emp_details_view2 where
department_id = 50

Plan hash value: 1998533517

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |       |     8 (100)|          |     45 |00:00:00.01 |      23 |
|   1 |  NESTED LOOPS OUTER            |                 |      1 |     45 |  2250 |     8  (13)| 00:00:01 |     45 |00:00:00.01 |      23 |
|*  2 |   HASH JOIN OUTER              |                 |      1 |     45 |  1980 |     8  (13)| 00:00:01 |     45 |00:00:00.01 |      17 |
|*  3 |    HASH JOIN OUTER             |                 |      1 |     45 |  1710 |     5  (20)| 00:00:01 |     45 |00:00:00.01 |       9 |
|*  4 |     TABLE ACCESS FULL          | EMPLOYEES       |      1 |     45 |  1395 |     3   (0)| 00:00:01 |     45 |00:00:00.01 |       7 |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS     |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  6 |      INDEX UNIQUE SCAN         | DEPT_ID_PK      |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   7 |    TABLE ACCESS FULL           | LOCATIONS       |      1 |     23 |   138 |     3   (0)| 00:00:01 |     23 |00:00:00.01 |       8 |
|*  8 |   INDEX UNIQUE SCAN            | COUNTRY_C_ID_PK |     45 |      1 |     6 |     0   (0)|          |     45 |00:00:00.01 |       6 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
   3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   4 - filter("E"."DEPARTMENT_ID"=50)
   6 - access("D"."DEPARTMENT_ID"=50)
   8 - access("C"."COUNTRY_ID"="L"."COUNTRY_ID")

Using a Table To Provide Query Parameters (SQL AntiPatterns)

Categories: DBA, Oracle, SQL
  1. January 24, 2011 at 8:36 am

    While your academic exercise is thoughtful, you must understand that in many cases, one would not have the option of making DDL changes, adding indexes, etc.

    I do believe views have their place. It’s an important place, in that it simplifies and secures the underlying data structures from those who may not need or be permitted to know them. Views can also assure predictable results when common joins are used. Using views incorrectly is about as unavoidable as using tables incorrectly. In the hands of a novice user, bad things can happen when the data is not fully understood. Overall, the use of views to join tables appropriately is better than leaving this step to those who don’t understand the relationships.

  2. Iggy Fernandez
    January 24, 2011 at 8:54 am

    Michael Fontana :

    I do believe views have their place.

    Thanks for the comments, Michael. As you imply, understanding is key.

  1. No trackbacks yet.

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

%d bloggers like this: