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

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

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) 

 

Comments