Missing FK Indexes

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
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License