jump to navigation

Indexing Foreign Keys (Helden) April 2, 2014

Posted by Richard Foote in Foreign Keys, Oracle Indexes.
4 comments

A recent question on an internal forum asked whether an index on a Foreign Key (FK) constraint designed to avoid locking issues associated with deletes on the parent tables needs to precisely match the columns in the FK. Could the columns in the index potentially be a different order or be appended with additional columns ?

The answer is basically the same as when using an index to police a Primary Key or Unique Key constraint. An index can be used providing the leading columns match those of the constraint (in any order). The index can indeed potentially have additional columns appended (or overloaded) to it.

Often the easiest way to find out these sorts of things is of course to just test it :) The point of this blog is not only to show candidate FK based indexes but also to highlight how easy it is to create simple test cases.

First, let’s create a couple of tables:

SQL> CREATE TABLE artists (id NUMBER,
                           code number,
                           artist_name VARCHAR2(30));

Table created.

SQL> CREATE TABLE albums (id NUMBER,
                          album_name VARCHAR2(30),
                          artist_id NUMBER ,
                          artist_code number,
                          format_id number);

Table created.

We populate the ARTISTS parent table with a few rows:

SQL> INSERT INTO artists VALUES (1, 1, 'DAVID BOWIE'); 

1 row created.

SQL> INSERT INTO artists VALUES (1, 2, 'ZIGGY STARDUST'); 

1 row created.

SQL> INSERT INTO artists VALUES (2, 1, 'MAJOR TOM');

1 row created.

SQL> INSERT INTO artists VALUES (2, 2, 'THIN WHITE DUKE');

1 row created.

We now populate the much larger ALBUMS child table with lots of rows:

SQL> insert into albums select rownum, 'BLAH', 1,  mod(rownum,2)+1, mod(rownum,100)
from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Now the tables are populated, we can add the necessary constraints. A concatenated PK based on the ID and CODE columns on the ARTISTS table and an associated FK constraint on the ALBUMS table:

SQL> alter table artists add primary key (id, code); 

Table altered.

SQL> alter table albums add constraint artists_fk foreign key (artist_id, artist_code)
references artists(id, code);

Table altered.

OK, note at this point there is no index based on the FK constraint columns on the ALBUMS table. Let’s look at the number of consistent gets generated when we try to delete just a single row from the tiny ARTISTS table:

SQL> delete artists where id=2 and code = 1; 

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          8  recursive calls
          7  db block gets
       3358  consistent gets
          0  physical reads
        640  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> rollback;

Rollback complete.

We notice our first issue. In order to delete just one row from the table via a Unique Index scan, we performed a massive 3358 consistent gets. Why? Because we can only successfully delete this row if there are no corresponding FKs based on this parent row. Without an associated index, the only way Oracle can perform this check on the large child table is to perform an expensive, slow, Full Table Scan (FTS).

Let’s rollback and this time start with an insert into the child, ALBUMS table (but not yet commit): 

SQL> insert into albums values (1000001, 'HEATHEN', 1, 1, 1); 

1 row created.

In a second session, let’s now attempt to delete the a parent row from the ARTISTS table:

SQL> delete artists where id = 2;

 

We notice, this session now hangs while it waits for all current transactions on the ALBUMS table complete.

In a third session, we attempt to insert another row into the child, ALBUMS table:

SQL> insert into albums values (1000002, 'THE NEXT DAY', 1,2,3);

 

And we notice it hangs as well, due to the previous locks on the table. With lots of other transactions trying to make changes to the ALBUMS table getting locked as well, we effectively have locking hell …

Why ? Because Oracle needs some way to ensure while it runs the FTS looking for any FKs associated with the deleted parent row, no-one else comes in and inserts or updates a row with this FK value. And as Oracle is performing a slow FTS, and values could potentially be inserted or updated in an area of the table already checked, the only way to effectively achieve this is to exclusively lock the table. And exclusive table locks are not really that great from a concurrency point of view …

So, introducing the index on the FK column(s). By having an index in place, we can effectively address both of the above issues. When searching for a corresponding FK value, the index will very quickly direct us to the leaf block that will either:

  • find a value of the parent key being deleted (in which case the delete of the parent row will fail with an ORA-02292 that a child record has been found) or
  • not find the value being deleted, in which case the delete on the parent row can be successful

Additionally, as it’s a very fast index scan being performed, there is no need to exclusively lock the table. Oracle in fact effectively “locks” the location within the index where the index value would reside if it existed or were to be subsequently inserted. Only an attempt to insert/update a row into the child table with the specific deleted FK value would be locked until the point when the parent delete is either committed (in which case the child insert will fail with an ORA-02291 parent key not found) or rolled back (in which case the child insert will be successful). The FK index can effectively detect when such a child insert has taken place because unlike the table where a such a new row could potentially be anywhere within the table, such an insert can only occur in a specific location within the index.

So if you do potentially delete a parent record (or update the PK value, a rare thing to do which is logically equivalent to a delete/insert of the PK value), then it would be a good idea to create an appropriate index on the FK of the child tables.

So going back to our demo:

SQL> create index albums_fk_i on albums(artist_id, artist_code);

Index created.

If we now delete a parent row:

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

We notice the number of consistent gets has dropped dramatically from the previous 3358. So no expensive FTS of the child table and none of the locking issues previously experienced.

But what if the index had the columns in a different order to that specified in the constraints:

SQL> rollback;

Rollback complete.

SQL> drop index albums_fk_i;

Index dropped.

SQL> create index albums_fk_i on albums(artist_code, artist_id);

Index created.

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Not a problem. The index contains all the columns of interest and can still be effectively used to quickly check for the existence of the deleted parent value.

What if the index had additional columns defined ?

SQL> rollback;

Rollback complete.

SQL> drop index albums_fk_i;

Index dropped.

SQL> create index albums_fk_i on albums(artist_code, artist_id, album_name);

Index created.

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Again not a problem. As the leading columns contain the FK columns of interest, Oracle can still effectively find the location within the index where the deleted value would be found if it existed in the child table.

So any index in which all the FK columns match the leading columns of the index would suffice.

And it’s really quite easy to create a quick demo to test this all out :)

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers