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.
Mark Hoxey 