Unused indexes

[tamilselvan]

That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.

But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.

Modic, I disagree with your points.
I do not know how you tested.
Here is my test.

The following demo was tested in Oracle 9.2.0.5

The easiest way to check FK Indexes are needed is disable
table lock on the child table.

SQL> get cr_p
1 CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(10) not null,
4 contact_name varchar2(10),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6 );
7 CREATE TABLE products
8 ( product_id numeric(10) not null,
9 supplier_id numeric(10) not null,
10 CONSTRAINT fk_supplier
11 FOREIGN KEY (supplier_id)
12 REFERENCES supplier(supplier_id)
13 ON DELETE CASCADE
14* );
15
SQL> @cr_p

Table created.


Table created.

SQL> desc supplier
Name Null? Type
-------------------------------- -------- ---------------------------
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(10)
CONTACT_NAME VARCHAR2(10)

SQL> insert into supplier values (1, 'Name-1','Scott');

1 row created.

SQL> insert into supplier values (2 , 'Name-2','Tom');

1 row created.

SQL> commit;

Commit complete.

SQL> desc products
Name Null? Type
--------------------------------------- -------- ---------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)

SQL> insert into products values (1,1);

1 row created.

SQL> insert into products values (2,2) ;

1 row created.

SQL> commit;

Commit complete.

REM The next command is very important to understand how Oracle works

SQL> alter table products disable table lock;

Table altered.

SQL> delete supplier where supplier_id = 1 ;
delete supplier where supplier_id = 1
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for PRODUCTS

Why should I get an error here?
Why should oracle try to acquire "Table Lock" on products?
I am just deleting one row from the parent table and one row from the child by
way cascade delete.

SQL> rollback;

Rollback complete.

SQL> alter table products enable table lock;

Table altered.

SQL> delete supplier where supplier_id = 1 ;

1 row deleted.

SQL> commit;

Commit complete.

Once Oracle table lock was enabled, Oracle was able to delete rows on both table.

Let us see how an Index on FK plays an important role.

SQL> select * from supplier ;

SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom

SQL> select * from products ;

PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2

I inserted deleted rows again.

SQL> insert into supplier values (1,'Name-1','Scott');

1 row created.

SQL> insert into products values (1,1) ;

1 row created.

SQL> commit;

Commit complete.

SQL> alter table products enable table lock;

Table altered.

SQL> create index products_fk_idx on products(supplier_id);

Index created.

SQL> alter table products disable table lock;

Table altered.

SQL> select * from supplier;

SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom
1 Name-1 Scott

SQL> select * from products ;

PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2
1 1

SQL> delete supplier where supplier_id = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from supplier;

SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom

SQL> select * from products ;

PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2

Even when the "table lock" is disabled but an index on FK is present,
Oracle is able to delete rows in both parent and child table.

Tamil