Monthly Archives: December 2013

More Views and Foreign Keys

Posted on by
Reply

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.

Views and Foreign Keys

Posted on by
Reply

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.