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