Delete taking long time for no index on referential Integrity Constraint

If you are performing delete from parent table and if you don’t have index on child table foreign key column then Oracle database will perform full scan on child table for each row you delete from the parent table.

Example:

SQL> create table test1 (

x int primary key

);

Table created

SQL> create table test2 (

x int references test1 (x)

);

Table created.

SQL> insert into test1 values (1);

SQL>insert into test1 values (2);

SQL>insert into test1 values (3);

SQL>commit;

SQL> alter session set tracefile_identifier = deletetrace;

Session altered.

SQL> exec dbms_monitor.session_trace_enable();

PL/SQL procedure successfully completed.

SQL> delete test1;

3 rows deleted.

SQL> exec dbms_monitor.session_trace_disable();

PL/SQL procedure successfully completed.

SQL> exit

Now you can see the generated trace files in the trace file location

$ ls -lrt *DELETETRACE*

-rw-r----- 1 oracle dba 105 Nov 02 11:03 STORE_ora_8454590_DELETETRACE.trm

-rw-r----- 1 oracle dba 5506 Nov 02 11:03 STORE_ora_8454590_DELETETRACE.trc

Now let’s use TKPROF program to converts Oracle trace files into a more readable form.

$ tkprof STORE_ora_8454590_DELETETRACE.trc STORE_ora_8454590_DELETETRACE.out explain=scott sort=exeela

TKPROF: Release 12.1.0.2.0 - Development on Wed Nov 2 11:07:34 2016

password = *******

$ ls -lrt *DELETETRACE*

-rw-r----- 1 oracle dba 5506 Nov 07 22:15 STORE_ora_8454590_DELETETRACE.trc

-rw-r----- 1 oracle dba 9780 Nov 07 22:47 STORE_ora_8454590_DELETETRACE.trm

-rw-r----- 1 oracle dba 9765 Nov 07 23:07 STORE_ora_8454590_DELETETRACE.out

From the generated readable format file “STORE_ora_8454590_DELETETRACE.out” you will see it doing full table scan in child table:

SQL ID: 13vkd1jmp2yyp Plan Hash: 147238325

select /*+ all_rows */ count(1)

from

"SCOTT"."TEST2" where "X" = :1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 3 0.00 0.00 0 0 0 0

Fetch 3 0.00 0.00 1 9 0 3

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

total 7 0.00 0.00 1 9 0 3

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SCOTT (recursive depth: 1)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

1 1 1 SORT AGGREGATE (cr=3 pr=1 pw=0 time=1843 us)

0 0 0 TABLE ACCESS FULL TEST2 (cr=3 pr=1 pw=0 time=1826 us cost=2 size=13 card=1)