Skip to Main Content
  • Questions
  • Best practice to delete rows with a CLOB column

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Gary.

Asked: August 05, 2020 - 2:34 am UTC

Answered by: Connor McDonald - Last updated: September 09, 2020 - 2:47 am UTC

Category: PL/SQL - Version: 12.1.0.2

Viewed 1000+ times

You Asked

Environment: Oracle 12.1.0.2 on Exadata



I have a table with 30 columns, one of which is a CLOB, that contains about 26 million rows.



I have a purge job (PL/SQL packaged procedure) that DELETEs rows from nine (9) other tables based on a list of IDs from the driver table that contains the CLOB.



I save the list of IDs in a global temporary table and use that to delete the associated rows from the other tables that are done in the correct order to maintain all the FK relationships.



I am running a test today that has identified about 98,000 IDs to be purged but I have about 5 million that need to be purged to 'catch up' and going forward I'll have about 10,000 per day to purge.



The DELETE on the other 9 tables runs very quickly.



The table with the CLOB column is taking many times longer than the others, like several hours as opposed to several minutes.



I'm guessing there is a better and more efficient way to remove the rows from the table containing the CLOB and to regain the storage used by the deleted CLOB.



I'm currently issuing just a 'DELETE FROM <table> WHERE ID IN (SELECT ID FROM <gtt>', where ID is the PK.



I would appreciate any suggestions and order of operational steps to accomplish this purge, both for the 'catch up' and the daily run of 10,000.



Thanks in advance for all the help!! Much appreciated!



-gary


and we said...

For the initial purge, to remove 5m out of 26m, you are probably better off with a create-table-as-select, where you query just the 21m rows you want to keep.

For the daily run (or any smaller scale), we really need to where the time is being lost. So you can do this:

- exec dbms_monitor.session_trace_enable(waits=>true)
- [run your delete]
- exec dbms_monitor.session_trace_disable(waits=>true)
- query V$DIAG_INFO to get the name of your trace file

and then run tkprof on it to see where the time is lost.

Feel free to post that back here as a review and we'll check it out

and you rated our response

  (9 ratings)

Reviews

No access to DBMS_MONITOR, OEM yes

August 05, 2020 - 1:53 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Hi Connor
Thanks for the quick response. Unfortunately I don't have access to DBMS_MONITOR nor could I get to the trace file without engaging another group with the authority that I do not have.

However, I do have OEM and the monitoring there shows huge amounts of CPU usage and very little I/O.

I am using the 'parallel (8)' hint.

I manually deleted four (4) rows and it took about 20 seconds. I'm now running a delete that affects about 8,000 rows and it's been running for 2 hours so far.

Question: why is it taking so long to delete rows with a CLOB column and why is the CPU usage so high?

Is it just dealing with the CLOB and the associated index?

Thanks very much for your help!

-gary
Connor McDonald

Followup  

August 06, 2020 - 3:55 am UTC

Hmmm...no trace. That make things harder.

Maybe try this

Start a fresh session
Get the SID (ie, sys_context('userenv','sid')
Start your delete in serial mode.

After a while, from another session, query v$session_event and v$sesstat for the running SID and see what comes out of that


Data back from V$SESSTAT and V$SESSION_EVENT

August 06, 2020 - 4:51 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Results seem to show a lot of I/O. From V$SESSION_EVENT:

       SID EVENT                               TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS          CON_ID
---------- ----------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- --------------- ----------
       678 Disk file operations I/O                     74              0           0            0          0              1493  166678035    1740759767           8 User I/O                33
       678 Disk file Mirror Read                         2              0           0          .03          0               567   13102552    1740759767           8 User I/O                33
       678 control file sequential read                  7              0           0          .02          0              1668 3213517201    4108307767           9 System I/O              33
       678 latch: cache buffers chains                  17              0           0            0          0               265 2779959231    3875070507           4 Concurrency             33
       678 log file sync                                 5              0           1           .1          0              5177 1328744198    3386400367           5 Commit                  33
       678 latch: shared pool                           17              0           0          .01          0              2310 2696347763    3875070507           4 Concurrency             33
       678 resmgr:cpu quantum                           64              0          88         1.37         29            879799 1452455426    2396326234          10 Scheduler               33
       678 PX Deq: Join ACK                            396              0           2          .01          0             20849 4205438796    2723168908           6 Idle                    33
       678 PX Deq: Parse Reply                         198              0           1          .01          0             10475 4255662421    2723168908           6 Idle                    33
       678 PX Deq: Execute Reply                       708              0           9          .01          3             85317 2599037852    2723168908           6 Idle                    33
       678 PX Deq: Table Q Normal                       94              0           0            0          0               204  799271425    2723168908           6 Idle                    33
       678 SQL*Net message to client                   124              0           0            0          0               336 2067390145    2000153315           7 Network                 33
       678 SQL*Net more data to client                  10              0          15         1.53         15            152684  554161347    2000153315           7 Network                 33
       678 SQL*Net message from client                 123              0      687196      5586.96     224298        6871964897 1421975091    2723168908           6 Idle                    33
       678 SQL*Net break/reset to client                 8              0          21         2.66          6            213085 1963888671    4217450380           1 Application             33
       678 cell single block physical read          105284              0        3042          .03          2          30419630 2614864156    1740759767           8 User I/O                33
       678 cell multiblock physical read             80296              0        4435          .06          2          44345672  443865681    1740759767           8 User I/O                33
       678 cell list of blocks physical read         13025              0        2602           .2          1          26017880 3975960017    1740759767           8 User I/O                33
       678 events in waitclass Other                   427              3           1            0          0              5090 1736664284    1893977003           0 Other                   33


OK, not sure why that didn't format properly.

Question: Why does this table with the CLOB column take so long to delete?

All FKs are indexed. All tables are analyzed.

From V$SESSTAT:

131,490,867 no work - consistent read gets  609446
131,777,876 consistent gets pin (fastpath)  610157
131,975,535 consistent gets pin  610157
132,105,362 consistent gets  610157
132,105,362 consistent gets from cache  610157
132,508,198 session logical reads  611913
1,596,722,183 session connect time  0
1,596,722,183 process last non-idle time  0
24,729,370,624 physical read bytes  16384
24,729,485,312 cell physical IO interconnect bytes  16384
24,729,485,312 physical read total bytes optimized  16384
24,729,485,312 physical read total bytes  16384
1,085,507,158,016 logical read bytes from cache  5012750336


What am I doing wrong with the code formatting? Doh!

Other tables with many more rows but no CLOB column perform in seconds.

Thanks very much for the help!!

-gary
Connor McDonald

Followup  

August 10, 2020 - 2:22 am UTC

Can we get the entire DDL for the table please.

We need to see the parameters used etc to define the LOB

Why does a DELETE with a CLOB column take so long?

August 07, 2020 - 5:50 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Connor

I guess my biggest question is why does the DELETE from a table with a CLOB column take many times longer than a table without a CLOB but deletes many more rows?

What is it about the CLOB column that is causing the long delete and what can I do about it?

Thanks for all your help!!

-gary

Full table CREATE

August 10, 2020 - 1:09 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Connor

Here is the entire CREATE TABLE script.

The largest CLOB is 100245 bytes.

CREATE TABLE CLAIMS
(
  ID                         NUMBER,
  LINECOUNT                  NUMBER             NOT NULL,
  IMPORTDATE                 DATE               NOT NULL,
  RAWDATA                    CLOB,
  HASATTACHMENTS             NUMBER             NOT NULL,
  DATEOFSERVICEBEGIN         DATE               NOT NULL,
  DATEOFSERVICEEND           DATE               NOT NULL,
  DCN                        VARCHAR2(50 BYTE)  NOT NULL,
  DIAGNOSISCODE              VARCHAR2(255 BYTE),
  DIAGNOSISCODEADMITTING     VARCHAR2(255 BYTE),
  DRG                        VARCHAR2(255 BYTE),
  IDCPROCEDURECODEPRINCIPAL  VARCHAR2(255 BYTE),
  MEMBERSUBMITTEDPAPERCLAIM  NUMBER             NOT NULL,
  PATIENTAGE                 NUMBER             NOT NULL,
  PLACEOFSERVICE             VARCHAR2(255 BYTE),
  PROVIDERID                 VARCHAR2(255 BYTE),
  PROVIDERSPECIALTY          VARCHAR2(255 BYTE),
  PROVIDERTAXID              VARCHAR2(255 BYTE),
  TYPEOFBILL                 VARCHAR2(255 BYTE),
  STATEID                    NUMBER,
  CLAIMTYPEID                NUMBER,
  SOURCETYPEID               NUMBER,
  MEDICAID                   NUMBER             NOT NULL,
  NOVOLOGIX                  NUMBER             NOT NULL,
  HASMULTNM1                 NUMBER             NOT NULL,
  HASMULTSVD                 NUMBER             NOT NULL,
  RCRD_CREATR_NM             VARCHAR2(35 BYTE),
  RCRD_CREATD_DT             DATE,
  RCRD_MDFR_NM               VARCHAR2(35 BYTE),
  RCRD_MDFD_DT               DATE
)
LOB (RAWDATA) STORE AS SECUREFILE (
  TABLESPACE  EDIE_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE EDIE_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
ROW STORE COMPRESS ADVANCED
NOCACHE
MONITORING
ENABLE ROW MOVEMENT;


Thanks for all your help!!

-gary

More data from recent clean test ...

August 10, 2020 - 4:13 pm UTC

Reviewer: Gary from Indianapolis, IN USA

I just ran a new clean test and captured the run stats.



I deleted 1170 rows that took 22 minutes.



I am the only one in the database.



The delete was only from one(1) table that has the one (1) CLOB column where the maximum length of the CLOB column is 3073 bytes in the set of rows being deleted.



Data from V$SESSION_EVENT and V$SESSTAT were gathered after the DELETE operation had completed.



NAME                                 TYPE        VALUE  
------------------------------------ ----------- ------------------------------  
db_file_multiblock_read_count        integer     128  
 

 

 

SQL> select * from v$session_event where sid = 2689;  
  
  
       SID EVENT                                    TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT TIME_WAITED_MICRO   EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                    CON_ID  
---------- ---------------------------------------- ----------- -------------- ----------- ------------ ---------- ----------------- ---------- ------------- ----------- ------------------------- ----------  
      2689 Disk file operations I/O                          20              0           0            0          0               324  166678035    1740759767           8 User I/O                          33  
      2689 log file sync                                      1              0           0          .01          0                98 1328744198    3386400367           5 Commit                            33  
      2689 resmgr:cpu quantum                                18              0          18            1          3            179843 1452455426    2396326234          10 Scheduler                         33  
      2689 SQL*Net message to client                         19              0           0            0          0                55 2067390145    2000153315           7 Network                           33  
      2689 SQL*Net message from client                       18              0       18596      1033.13      18191         185962916 1421975091    2723168908           6 Idle                              33  
      2689 cell single block physical read                42590              0        1276          .03          3          12764409 2614864156    1740759767           8 User I/O                          33  
      2689 cell multiblock physical read                    201              0          27          .14          1            273031  443865681    1740759767           8 User I/O                          33  
  
  
7 rows selected.  
  
  
SQL> select ss.value, sn.name from v$sesstat ss, v$statname sn where ss.statistic# = sn.statistic# and sid = 2689 and value != 0;  
  
  
     VALUE NAME  
---------- ----------------------------------------------------------------  
        18 Requests to/from client  
         1 logons cumulative  
         1 logons current  
     10894 opened cursors cumulative  
         4 opened cursors current  
        26 user calls  
     11399 recursive calls  
    129286 recursive cpu usage  
         1 pinned cursors current  
         1 user logons cumulative  
  56247801 session logical reads  
    129393 CPU used when call started  
    129393 CPU used by this session  
    130640 DB time  
      1303 user I/O wait time  
        17 scheduler wait time  
      1321 non-idle wait time  
     85659 non-idle wait count  
1597071931 session connect time  
1597071931 process last non-idle time  
    715440 session uga memory  
   1464920 session uga memory max  
        11 messages sent  
   2707080 session pga memory  
   3362440 session pga memory max  
      3570 enqueue requests  
     22239 enqueue conversions  
      3552 enqueue releases  
     42791 physical read total IO requests  
       152 physical read total multi block requests  
     42791 physical read requests optimized  
 509517824 physical read total bytes optimized  
 509517824 physical read total bytes  
 509517824 cell physical IO interconnect bytes  
     46721 db block gets  
     46721 db block gets from cache  
     27530 db block gets from cache (fastpath)  
  56201080 consistent gets  
  56201080 consistent gets from cache  
  56199734 consistent gets pin  
  56157457 consistent gets pin (fastpath)  
      1346 consistent gets examination  
      1346 consistent gets examination (fastpath)  
4.6078E+11 logical read bytes from cache  
     62197 physical reads  
     62197 physical reads cache  
     42791 physical read IO requests  
 509517824 physical read bytes  
     15141 db block changes  
         1 consistent changes  
        10 change write time  
     62789 free buffer requested  
        11 dirty buffers inspected  
         6 pinned buffers inspected  
     53498 hot buffers moved to head of LRU  
     54347 free buffer inspected  
         1 commit cleanouts  
         1 commit cleanouts successfully completed  
         1 CR blocks created  
       591 switch current to new buffer  
     19406 physical reads cache prefetch  
     10579 shared hash latch upgrades - no wait  
       378 physical reads for flashback new  
     58564 calls to kcmgcs  
         2 calls to kcmgas  
     14465 calls to get snapshot scn: kcmgss  
      8679 redo entries  
   5255696 redo size  
      1690 redo subscn max counts  
       105 redo synch time (usec)  
        23 redo synch time overhead (usec)  
         1 redo synch time overhead count (  2ms)  
         1 redo synch writes  
         1 redo write info find  
  13037440 file io wait time  
   4088952 undo change vector size  
         1 data blocks consistent reads - undo records applied  
  56139565 no work - consistent read gets  
      1635 cleanouts only - consistent read gets  
         1 cleanouts and rollbacks - consistent read gets  
        35 immediate (CURRENT) block cleanout applications  
      1636 immediate (CR) block cleanout applications  
         1 deferred (CURRENT) block cleanout applications  
      1635 commit txn count during cleanout  
         1 active txn count during cleanout  
      1636 cleanout - number of ktugct calls  
        35 Block Cleanout Optim referenced  
        13 table scans (short tables)  
        31 table scan rows gotten  
        31 table scan disk non-IMC rows gotten  
        13 table scan blocks gotten  
      1412 table fetch by rowid  
         1 table fetch continued row  
       180 cluster key scans  
       307 cluster key scan block gets  
         7 rows fetched via callback  
      3510 index fast full scans (full)  
       210 index fetch by key  
       215 index scans kdiixs1  
      1171 HSC Compressed Segment Block Changes  
      1172 HSC Heap Segment Block Changes  
         1 Heap Segment Array Updates  
         8 sql area evicted  
     10847 session cursor cache hits  
        42 session cursor cache count  
        10 cursor authentications  
      1850 buffer is pinned count  
      1777 buffer is not pinned count  
        77 workarea executions - optimal  
         4 parse time cpu  
         5 parse time elapsed  
        74 parse count (total)  
        28 parse count (hard)  
     10892 execute count  
      6077 bytes sent via SQL*Net to client  
      5901 bytes received via SQL*Net from client  
        19 SQL*Net roundtrips to/from client  
       149 sorts (memory)  
      1499 sorts (rows)  
     42791 cell flash cache read hits  


Thanks very much for your help!!!



-gary
Connor McDonald

Followup  

August 11, 2020 - 9:24 am UTC

I'm not convinced its the clob (without the trace data its all a bit of guess work).

I did this sample on my exadata instance (its 19 not 12). I tried both easily compressible data and non-compressible. Both results in zippy deletion.


SQL>
SQL> CREATE TABLE tmp_claims
  2  (
  3    ID                         NUMBER,
  4    LINECOUNT                  NUMBER             NOT NULL,
  5    RAWDATA                    CLOB,
  6    DATEOFSERVICEBEGIN         DATE               NOT NULL,
  7    DATEOFSERVICEEND           DATE               NOT NULL
  8  )
  9  LOB (RAWDATA) STORE AS SECUREFILE (
 10    ENABLE      STORAGE IN ROW
 11    CHUNK       8192
 12    NOCACHE
 13    LOGGING
 14  )
 15  LOGGING
 16  ROW STORE COMPRESS ADVANCED
 17  NOCACHE
 18  MONITORING
 19  ENABLE ROW MOVEMENT;

Table created.

SQL>
SQL> truncate table tmp_claims;

Table truncated.

SQL> declare
  2    pk int := 0;
  3  begin
  4    for i in 1 .. 1000
  5    loop
  6      insert into tmp_claims
  7      values ( pk, 0, dbms_random.string('A',6000), sysdate, sysdate );
  8      pk := pk + 1;
  9    end loop;
 10    commit;
 11    for i in 1 .. 1000
 12    loop
 13      insert into tmp_claims
 14      values ( pk, 0, dbms_random.string('A',32000) || dbms_random.string('A',32000) , sysdate, sysdate );
 15      pk := pk + 1;
 16    end loop;
 17    commit;
 18
 19    for i in 1 .. 1000
 20    loop
 21      insert into tmp_claims
 22      values ( pk, 0,
 23                   dbms_random.string('A',32000) ||
 24                   dbms_random.string('A',32000) ||
 25                   dbms_random.string('A',32000) ,
 26                   sysdate, sysdate );
 27      pk := pk + 1;
 28    end loop;
 29    commit;
 30
 31  end;
 32  /

PL/SQL procedure successfully completed.

SQL>
SQL> set timing on
SQL> delete tmp_claims;

3000 rows deleted.

Elapsed: 00:00:00.48
SQL>
SQL> truncate table tmp_claims;

Table truncated.

Elapsed: 00:00:00.69
SQL> declare
  2    c1 clob;
  3    pk int := 0;
  4  begin
  5    for i in 1 .. 1000
  6    loop
  7      insert into tmp_claims
  8      values ( pk, 0, rpad('A',6000,'A'), sysdate, sysdate );
  9      pk := pk + 1;
 10    end loop;
 11    commit;
 12    c1 := rpad('A',32000,'A');
 13    c1 := c1 || c1;
 14    for i in 1 .. 1000
 15    loop
 16      insert into tmp_claims
 17      values ( pk, 0,c1 , sysdate, sysdate );
 18      pk := pk + 1;
 19    end loop;
 20    commit;
 21
 22    c1 := rpad('A',32000,'A');
 23    c1 := c1 || c1 || c1 || c1;
 24    for i in 1 .. 1000
 25    loop
 26
 27      insert into tmp_claims
 28      values ( pk, 0,c1 , sysdate, sysdate );
 29      pk := pk + 1;
 30    end loop;
 31    commit;
 32
 33  end;
 34  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.61
SQL>
SQL> set timing on
SQL> delete tmp_claims;

3000 rows deleted.

Elapsed: 00:00:00.37
SQL>
SQL> truncate table tmp_claims;

Table truncated.

Elapsed: 00:00:00.33
SQL> declare
  2    c1 clob;
  3    pk int := 0;
  4  begin
  5    for i in 1 .. 1000
  6    loop
  7      insert into tmp_claims
  8      values ( pk, 0, rpad('A',6000,'A'), sysdate, sysdate );
  9      pk := pk + 1;
 10    end loop;
 11    commit;
 12    c1 := rpad('A',32000,'A');
 13    c1 := c1 || c1;
 14    for i in 1 .. 1000
 15    loop
 16      insert into tmp_claims
 17      values ( pk, 0,c1 , sysdate, sysdate );
 18      pk := pk + 1;
 19    end loop;
 20    commit;
 21
 22    c1 := rpad('A',32000,'A');
 23    c1 := c1 || c1 || c1 || c1;
 24    for i in 1 .. 1000
 25    loop
 26
 27      insert into tmp_claims
 28      values ( pk, 0,c1 , sysdate, sysdate );
 29      pk := pk + 1;
 30    end loop;
 31    commit;
 32
 33  end;
 34  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.33
SQL>
SQL> alter table tmp_claims move;

Table altered.

Elapsed: 00:00:00.32
SQL> alter table tmp_claims move lob (rawdata) STORE AS SECUREFILE;

Table altered.

Elapsed: 00:00:10.88
SQL>
SQL> set timing on
SQL> delete tmp_claims;

3000 rows deleted.

Elapsed: 00:00:00.45
SQL>


This leads me to think - is this table a parent of lots of child tables? Looking at the stats/events you can see you only lost 12.76 seconds in I/O but also:

56247801 session logical reads
129393 CPU used by this session

1293.93 is almost all of your 20 mins in CPU time, and logical reads are the chief burner of CPU.

Was this a fresh session? The 'execute count' looks like a red flag to me.

You nailed it Connor!

August 11, 2020 - 1:09 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Hi Connor

You are absolutely correct. I was looking at the problem from the wrong perspective! I was so focused on the LOB column that I totally ignored the ten (10) FKs that reference the PK on my LOB table.

I did a test where I disabled the FKs, ran the delete and re-enabled the FKs and the operation took just minutes for my test of 1170 rows,

This purge is run at night when there are no users in the application,

Question: what are the potential dangers of doing the disable/enable process that I tested?

I have already explicitly deleted the related child rows before deleting the rows from the parent so I'm confident I'm not creating a situation that would result in an orphan child row.

Thoughts??

Thanks for all your help and getting me to see past my 1-track mindset with the LOB.

-gary
Connor McDonald

Followup  

August 13, 2020 - 3:04 am UTC

"what are the potential dangers of doing the disable/enable process"

No dangers, but obviously the tables can't be used by others during that time

Also, check your indexing because that still seems a very slow process going on there.

FK follow up ... preventing child table searches

August 11, 2020 - 6:52 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Connor

Is there a way to prevent the DELETE operation from searching the child tables for potential rows that match the reference column from the parent table?

I tried re-creating the FK without the ON DELETE clause and it showed the DELETE_RULE to be 'NO ACTION' but it still seems to want to search the child tables.

I saw something about an 'ON DELETE RESTRICT' but it doesn't seem to be valid in my 12.1.0.2 environment and I couldn't find any documentation on it anyway.

Thanks very much for your help!!

-gary
Chris Saxon

Followup  

August 12, 2020 - 8:57 am UTC

Think about it - an FK stops you having orphaned rows in the child.

So when deleting from the parent, the database has to check for the existence of child rows. Without this check, you can end up with orphans

Solution: disable/enable FKs

August 14, 2020 - 2:17 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Hi Guys

My solution was to disable the FKs on the parent table before deleting the qualified rows and re-enabling them afterwards.

Since I had already explicitly deleted the child rows as an earlier step I felt comfortable that I was not creating any orphan child rows.

I will schedule the purge job at a time of low activity and no data loading. On a 'normal' purge run the job should only take a few minutes.

Thanks for all your help and suggestions!!

-gary
Connor McDonald

Followup  

August 15, 2020 - 6:44 am UTC

Glad this worked out.

To be safe, issue a "lock table" command on the parent and child tables before doing that final delete to make sure no-one slips a orphan child row in there whilst the constraint are disabled.

My incorrect indexing was the culprit :-(

September 08, 2020 - 6:36 pm UTC

Reviewer: Gary from Indianapolis, IN USA

Guys

As it turns out the indexes I was using to satisfy the FK validation did not have the referenced column as the leading edge even though it was part of the index (usually the 2nd element).

I modified the indexing to retain the original indexes and created new indexes with the referenced column as the leading edge and the purge process now runs well within acceptable limits.

I did remove the disable/enable code after realizing my indexing error.

Thanks for all your help and suggestions!!!

-gary
Connor McDonald

Followup  

September 09, 2020 - 2:47 am UTC

Glad you got to the bottom of it, and thanks for updating the post

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database