Views and Foreign Keys

One strategy developers can adopt to write queries against a complex, normalised data model is to use views. The views will join together all the related tables, exposing a “flat” representation of the data that is much like a dimensional model in reporting systems. Another strategy developers can adopt with a complex, normalised data model is to conveniently forget about declaring referential integrity constraints, generally under the assertion that “the object model handles it”. Unfortunately when these two strategies are combined then it can lead to some notable inefficiencies. Let’s take a look…

For this demonstration I’ll use two tables, PARENT_TABLE and CHILD_TABLE, to represent the main data tables. Rather obviously there is a one to many relationship between them. Furthermore, there are 5 references tables; FK_1 through FK_5. Three of the references tables are related to PARENT_TABLE and two related to CHILD_TABLE. All foreign key columns are indexed. The FK tables have 10 rows each, PARENT_TABLE has 100 rows and CHILD_TABLE has 10,000 rows. The DDL for these tables can be found at the end of this post.

The view that displays all the necessary details of the table set-up is:

CREATE OR REPLACE VIEW parent_child
   (parent_id
   ,child_id
   ,val_1
   ,val_2
   ,fk_1_id
   ,fk_1_descr
   ,fk_2_id
   ,fk_2_descr
   ,fk_3_id
   ,fk_3_descr
   ,fk_4_id
   ,fk_4_descr
   ,fk_5_id
   ,fk_5_descr)
AS
SELECT p.id
,      c.id
,      p.val_1
,      c.val_2
,      f1.fk_1_id
,      f1.fk_1_descr
,      f2.fk_2_id
,      f2.fk_2_descr
,      f3.fk_3_id
,      f3.fk_3_descr
,      f4.fk_4_id
,      f4.fk_4_descr
,      f5.fk_5_id
,      f5.fk_5_descr
FROM   parent_table p
,      child_table  c
,      fk_1         f1
,      fk_2         f2
,      fk_3         f3
,      fk_4         f4
,      fk_5         f5
WHERE  p.id = c.parent_id
AND    p.fk_1_id = f1.fk_1_id
AND    p.fk_2_id = f2.fk_2_id
AND    p.fk_3_id = f3.fk_3_id
AND    c.fk_4_id = f4.fk_4_id
AND    c.fk_5_id = f5.fk_5_id

In the initial instance let’s see what our query execution plan looks like with foreign key constraints in place. The query is:

SELECT child_id
,      val_2
FROM   parent_child
WHERE  parent_id = 1

Running this on an 11.2 database with Autotrace enabled gives us the following:

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT child_id
  2  ,      val_2
  3  FROM   parent_child
  4  WHERE  parent_id = 1
  5  /

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3194067673

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   100 |  1000 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CHILD_TABLE     |   100 |  1000 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | CHILD_TABLE_IX1 |   100 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("C"."PARENT_ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       2533  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

Since out query only involves details that can be found in the CHILD_TABLE Oracle has chosen to access that table via the index on the PARENT_ID column. No other table was access as they were not required.

Now let’s repeat the query but this time without the foreign key integrity constraints:

SQL> ALTER TABLE parent_table
  2     DROP CONSTRAINT parent_table_fk1
  3  /

Table altered.

SQL> ALTER TABLE parent_table
  2     DROP CONSTRAINT parent_table_fk2
  3  /

Table altered.

SQL> ALTER TABLE parent_table
  2     DROP CONSTRAINT parent_table_fk3
  3  /

Table altered.

SQL> ALTER TABLE child_table
  2     DROP CONSTRAINT child_table_fk1
  3  /

Table altered.

SQL> ALTER TABLE child_table
  2     DROP CONSTRAINT child_table_fk2
  3  /

Table altered.

SQL> ALTER TABLE child_table
  2     DROP CONSTRAINT child_table_fk3
  3  /

Table altered.

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT child_id
  2  ,      val_2
  3  FROM   parent_child
  4  WHERE  parent_id = 1
  5  /

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1074497947

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |   100 |  4300 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                     |                 |   100 |  4300 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                    |                 |   100 |  4000 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                 |   100 |  3700 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                 |     1 |    21 |     1   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                 |     1 |    18 |     1   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                 |     1 |    15 |     1   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| PARENT_TABLE    |     1 |    12 |     1   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | PARENT_TABLE_PK |     1 |       |     0   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN          | FK_3_PK         |    10 |    30 |     0   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN           | FK_2_PK         |    10 |    30 |     0   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN            | FK_1_PK         |    10 |    30 |     0   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID   | CHILD_TABLE     |   100 |  1600 |     2   (0)| 00:00:01 |
|* 13 |      INDEX RANGE SCAN             | CHILD_TABLE_IX1 |   100 |       |     1   (0)| 00:00:01 |
|* 14 |    INDEX UNIQUE SCAN              | FK_4_PK         |     1 |     3 |     0   (0)| 00:00:01 |
|* 15 |   INDEX UNIQUE SCAN               | FK_5_PK         |     1 |     3 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   8 - access("P"."ID"=1)
   9 - access("P"."FK_3_ID"="F3"."FK_3_ID")
  10 - access("P"."FK_2_ID"="F2"."FK_2_ID")
  11 - access("P"."FK_1_ID"="F1"."FK_1_ID")
  13 - access("C"."PARENT_ID"=1)
  14 - access("C"."FK_4_ID"="F4"."FK_4_ID")
  15 - access("C"."FK_5_ID"="F5"."FK_5_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         42  consistent gets
          0  physical reads
          0  redo size
       2533  bytes sent via SQL*Net to client
        566  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

This is the same query against the same data but without the foreign key constraints Oracle is now accessing all the tables contained in the view instead of just the CHILD_TABLE. The cost of our query in terms of logical IO has more than doubled, going from 17 consistent gets to 42.

Why does this happen? Neither the data nor the table structure changed. The reason is that with the integrity constraints in place Oracle is able to deduce that the joins contained in the view do not perform any filtering of the data, i.e. if CHILD_TABLE contains a PARENT_ID of 1 then this ID value must exist in PARENT_TABLE so there is no need to run a check against PARENT_TABLE to confirm this. Likewise the foreign keys to the FK reference tables do not filter the data so there is no need to reference them unless the query explicitly calls for data from those tables. Once the foreign keys were removed then Oracle needs to check that the data returned satisfies the join criteria as specified in the view.

So, the next time you find yourself writing queries against views check the resulting execution plan to see if the query is accessing tables that it doesn’t need to. You may find that it’s better to simply bypass the views and query the underlying table direct.


Download the set-up script for the tables used in this demonstration.

Advertisements

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