Bitmap Indexes and Foreign key

1.     Introduction

Several months ago I have published a sql script that checks the existence or the absence of an index covering the deadlock threat of unindexed foreign key constraint when DML are issued against the parent table. That script doesn’t bother about the index type. If an index of a bitmap type exists it will tell you that it is not necessary to create an extra index for your FK constraint. Let’s see that in action

2.     Bitmap Index and FK constraint

I will start by creating a classical parent/child table relation ship

houri> create table t_parent as select * from all_users;
Table created.
mhouri> alter table t_parent add constraint t_p_pk primary key (user_id);
Table altered.
mhouri> create table t_child (user_id references t_parent, data varchar2(10));
Table created.                 

Then I will create a bitmap index to cover my Foreign Key:

mhouri > create bitmap index t_bitmap_fk_i on t_child(user_id);
Index created.

Using my traditional script to check if I need to create an index to cover my FK or not, gives me the following situation

mhouri> start index_fk.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS
------------------------- ------------------------------ ---------- ----------
T_CHILD                   T_BITMAP_FK_I                  USER_ID             1

Oh! Yes; it is telling me that I don’t need to create an extra index because there is already one index starting with the foreign key column. If I execute Tom Kyte script,  it is also not pointing out the need to index my user_id FK column

mhouri> start tkyte.sql
TABLE_NAME                     CONSTRAINT_NAME                COLUMNS
------------------------------ ------------------------------ -------------------
MY_XXX_TAB                   C_FK                           ID1,ID2

My T_CHILD table is not returned by Tom Kyte script as a table having an unindexed foreign key. So, according to both scripts it is safe to do DML operations on T_PARENT table. Let’s then submit our parent table to a classical OLTP operations

mhouri> select min(user_id), max(user_id) from t_parent;
MIN(USER_ID) MAX(USER_ID)
------------ ------------
0           73
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
2   pragma autonomous_transaction;
3   begin
4   delete from t_parent where user_id = 73;
5   commit;
6   end;
7  /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

mhouri> rollback;
Rollback complete.

Ooops!!!  The existing bitmap index does not cover the deadlock threat induced by a DML operation on a parent table having a child table with an unindexed foreign key.  The bitmap index type has to be excluded from my script. The new script index_fk when executed against the t-child table gives this:

mhouri> start index_fk_2.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

And now, it’s OK. The script is telling me that I need to index my user_id FK columns because the existing bitmap index will not cover the deadlock threat.

          2.1 Remark

It would have been very bizarre that a bitmap index covers the deadlock threat induced by an unindexed foreign key because the bitmap index itself is responsible of several deadlocks situations when it is used in an OLTP application and this is why Tom Kyte script and my initial script has not foresee to exclude this type of indexes from the verification script

3.     Function Base Index and FK constraint

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child relationship it needs:

1. to start with the FK column(or columns for a composite key)
2. to not be of a bitmap type

 Looking to the above two conditions, there is nothing that impeaches a function based index to cover the FK threat. Let’s work with few examples:

mhouri> create index t_fbi_fk_i on t_child(user_id desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

My Function based index has only one column in it and it is a hidden column (SYS_NC00003$ for example). This is why it didn’t succeed to cover the above deadlock situation. Let’s check this with my new script:

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

 My new updated script is working well in this case as far as it is telling me that I need to create an extra index.  Let’s now drop and change a little bit this function based index

mhouri> drop index t_fbi_fk_i;
Index dropped.
mhouri> create index t_fbi_fk_i on t_child(user_id, data desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'testfbi');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

PL/SQL procedure successfully completed.

The newly created function based index is covering very well the FK on the child table. My new script is also working well in this case as it is telling me that I don’t need to create an extra index

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
<pre>TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS

------------------------- ------------------------------ ---------- ----------

T_CHILD                   T_FBI_FK_I                     USER_ID             1

4.     Conclusion

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child table relationship it needs:

1. to start with the FK column(or columns for a composite key in any order)
2. to not be of a bitmap type

Even a function based index when it starts with the FK columns will play the same role as a b-tree index will do when it will be asked to cover the deadlock threats induced by a DML operation on a parent/child relationship