Missing FK Indexes
|
Table of Contents
|
Background & Overview
A common issue that DBAs come across are missing FK indexes causing issues with delete statements. The following example illustrates this issue.
Description
Create two tables, testing_table and testing_table1. Testing_table1 has a primary key on id and testing_table2 has a foreign key called id that references the primary key of testing_table.
The tables are populated with data and a test delete performed with and without a FK index on the id column of testing_table1.
When the table has no FK index the delete takes 58 minutes. With the FK index the delete takes 40 seconds.
Code
ACCEPT pad PROMPT "Enter the length of your padding value?"
ACCEPT ran PROMPT "Enter the length of your random string value? "
ACCEPT rnums PROMPT "Enter the number of rows in the table? (Max Values is limited by the rows in all_objects) :"
CREATE TABLE testing_table PCTFREE 99 PCTUSED 1 TABLESPACE users AS
SELECT
rownum id,
dbms_random.string('X',&ran*dbms_random.value) strval,
rpad('x',&pad,'x') padding
FROM
all_objects
WHERE
rownum <=&rnums
/
CREATE TABLE testing_table1 PCTFREE 99 PCTUSED 1 TABLESPACE users AS
SELECT
rownum id
FROM testing_table
/
INSERT INTO testing_table1(id) SELECT id FROM testing_table
/
COMMIT
/
ALTER TABLE testing_table
ADD PRIMARY KEY(id)
USING INDEX TABLESPACE users
ENABLE VALIDATE
/
ALTER TABLE testing_table1
ADD(CONSTRAINT testing_table1_fk
FOREIGN KEY(id)
REFERENCES testing_table(id)
ON DELETE CASCADE
ENABLE VALIDATE
)
/
TEST 1 NO FK INDEX
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:58:42.60
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 191 (1)|
| 1 | DELETE | TESTING_TABLE | | | |
| 2 | INDEX FULL SCAN| SYS_C00142723 | 1 | 13 | 191 (1)|
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
87891 recursive calls
847612 db block gets
2244796030 consistent gets
12660 physical reads
145525560 redo size
824 bytes sent via SQL*Net to client
782 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
84770 rows processed
TEST 2 WITH FK INDEX
SQL> -- Create an index on the child table (FK)
SQL> create index testing_table1_idx(id) on testing_table1 tablespace users;
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:00:40.91
SQL> delete from testing_table;
84770 rows deleted.
Elapsed: 00:00:35.72
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 13 | 191 (1)|
| 1 | DELETE | TESTING_TABLE | | | |
| 2 | INDEX FULL SCAN| SYS_C00142723 | 1 | 13 | 191 (1)|
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
85437 recursive calls
1102357 db block gets
170504 consistent gets
126026 physical reads
181334020 redo size
838 bytes sent via SQL*Net to client
782 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
84770 rows processed
