More Views and Foreign Keys

Last post noted the performance impact that foreign key constraints can have when querying views, allowing Oracle to avoid accessing tables not required by the query. When dealing with large tables and bulk data operations the enforcement of foreign keys can impose such a performance penalty that it’s simply not feasible to have them. What should we do then if the optimiser can utilise them to arrive at better performing execution plans?

Constraints in Oracle can exist in a “no validate” state, meaning that the constraint exists but Oracle has not checked the existing data to see if it satisfies the constraint. If the constraint is enabled then any future data changes are required to satisfy the constraint.

Using our previous scenario, with 2 data tables, 5 reference type tables and a view superimposed over the top of them, let’s see what happens when we play with the foreign keys. First we see what the execution plan of run our query is like with the constraints in a NOVALIDATE state (using an 11.2.0.3 database):

SQL>ALTER TABLE parent_table
  2     ADD CONSTRAINT parent_table_fk
  3     FOREIGN KEY (fk_1_id)
  4     REFERENCES fk_1 (fk_1_id)
  5     ENABLE NOVALIDATE
  6  /

Table altered.

SQL>ALTER TABLE parent_table
  2     ADD CONSTRAINT parent_table_fk
  3     FOREIGN KEY (fk_2_id)
  4     REFERENCES fk_2 (fk_2_id)
  5     ENABLE NOVALIDATE
  6  /

Table altered.

SQL>ALTER TABLE parent_table
  2     ADD CONSTRAINT parent_table_fk
  3     FOREIGN KEY (fk_3_id)
  4     REFERENCES fk_3 (fk_3_id)
  5     ENABLE NOVALIDATE
  6  /

Table altered.

SQL>ALTER TABLE child_table
  2     ADD CONSTRAINT child_table_fk1
  3     FOREIGN KEY (parent_id)
  4     REFERENCES parent_table (id)
  5     ENABLE NOVALIDATE
  6  /

Table altered.

SQL>ALTER TABLE child_table
  2     ADD CONSTRAINT child_table_fk2
  3     FOREIGN KEY (fk_4_id)
  4     REFERENCES fk_4 (fk_4_id)
  5     ENABLE NOVALIDATE
  6  /

Table altered.

SQL>ALTER TABLE child_table
  2     ADD CONSTRAINT child_table_fk3
  3     FOREIGN KEY (fk_5_id)
  4     REFERENCES fk_5 (fk_5_id)
  5     ENABLE NOVALIDATE
  6  /

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

As it might have been guessed, with our constraints in a NOVALIDATE state our query now accesses all tables referenced in the view, just as it did when the constraints were not present at all. It’s not hard to guess that with the constraints in a NOVALIDATE state Oracle cannot make the assumptions about the data that it did when the constraints were validated. This means that it is possible that the joins between parent and child tables could actually be filtering data on the child table side so Oracle is forced to access the data in the parent tables to check for this situation.

This doesn’t bode well for our very large tables where we might very well want to avoid having to validate our constraints but still have Oracle optimise our queries as if they are true. Thankfully the good folks at Oracle have foreseen this and have provided us with a mechanism to leave our constraints in a NOVALIDATE state but allow Oracle to treat them as if they were validated; it’s the RELY property. Let’s repeat the example after modifying the constraints to be in the RELY state:

SQL>ALTER TABLE parent_table
  2     MODIFY CONSTRAINT parent_table_fk
  3     RELY
  4  /

Table altered.

SQL>ALTER TABLE parent_table
  2     MODIFY CONSTRAINT parent_table_fk
  3     RELY
  4  /

Table altered.

SQL>ALTER TABLE parent_table
  2     MODIFY CONSTRAINT parent_table_fk
  3     RELY
  4  /

Table altered.

SQL>ALTER TABLE child_table
  2     MODIFY CONSTRAINT child_table_fk1
  3     RELY
  4  /

Table altered.

SQL>ALTER TABLE child_table
  2     MODIFY CONSTRAINT child_table_fk2
  3     RELY
  4  /

Table altered.

SQL>ALTER TABLE child_table
  2     MODIFY CONSTRAINT child_table_fk3
  3     RELY
  4  /

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: 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
        565  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

With the constraints in a RELY state Oracle has assumed them to be true and has optimised the query just as it did when the constraints were in a validated state. This is exactly what we wanted.

However, what needs to be remembered here is that there is no protection against bad data in this scenario. With the constraints in a NOVALIDATE RELY state Oracle is making an assumption that the data satisfies the constraint criteria. If data exists that violates the constraint criteria then the query may very well return incorrect results. It is the responsibility of the developer to ensure that the data in the tables is correct.


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