Oracle Query Optimizing Complex Delete Query

Question

We had a delete request built for a table (already 2 billion rows) in our Oracle database. This query was executed as part of PL / SQL Proc. Below is our current request, which is still in testing.

DELETE from TABLE1 
    where ROWID IN (SELECT rid from (SELECT ROWID rid, ROW_NUMBER() over (PARTITION BY C1_Varchar2,C2_Varchar2 ORDER BY C3_Date desc) as Rank 
                                from TABLE1 where C3_Date < ADD_MONTHS(SYSDATE, -20))
                where Rank <> 1);

This query should delete all records (from table 1) that are older than 20 months from the current month, with the exception of the last record formed by a unique combination of columns C1 and C2. Approximately 12% of records will be deleted using this query.

When we run the query, we get the following error.

ORA-00604: error at the recursive level of SQL 2 ORA-04031: cannot allocate 32 bytes of shared memory ("shared pool", "select i.obj #, i.ts #, i.file #, ...", "SQLA "," tmp ")

Note that the table is split into a C3_Date column. But with the above logic, we will still save several entries in the partitions and, therefore, cannot go with the ability to delete the entire partition.

Can anyone suggest how to approach this removal so that it is more efficient and stable?

Plan as shown below:

Plan hash value: 2112788339

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                    |     1 |    59 |       |  9080K  (2)| 30:16:07 |       |       |
|   1 |  DELETE                      | TABLE1             |       |       |       |            |          |       |       |
|   2 |   NESTED LOOPS               |                    |     1 |    59 |       |  9080K  (2)| 30:16:07 |       |       |
|   3 |    VIEW                      | VW_NSO_1           |   496M|  5684M|       |  6785K  (1)| 22:37:12 |       |       |
|   4 |     SORT UNIQUE              |                    |     1 |    11G|       |            |          |       |       |
|*  5 |      VIEW                    |                    |   496M|    11G|       |  6785K  (1)| 22:37:12 |       |       |
|   6 |       WINDOW SORT            |                    |   496M|    20G|    26G|  6785K  (1)| 22:37:12 |       |       |
|*  7 |        INDEX SKIP SCAN       | XPKTABLE1          |   496M|    20G|       |  1206K  (1)| 04:01:18 |       |       |
|   8 |    TABLE ACCESS BY USER ROWID| TABLE1             |     1 |    47 |       |     1   (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("RANK"<>1)
   7 - access("C3_Date"<ADD_MONTHS(SYSDATE@!,-15))
       filter("C3_Date"<ADD_MONTHS(SYSDATE@!,-15)) 

Answer 1

This query generates a large number of time segments (26G), as shown in the SQL plan, and the query is designed to run for more than 30 hours. Therefore, you get ORA-04031 at runtime.

These are my good recommendations.

, , , .

- , literal bind. PL/SQL . SYSDATE . ( ).

, Oracle rowid , rowid (s). - ( autotrace ).

, , - ( , ):

delete from TABLE1 t1_1
 where C3_Date < :upper_date_bound
   and C3_Date >= :lower_date_threshold
   and (C1_Varchar2, C2_Varchar2, C3_Date) not in 
       (select C1_Varchar2, C2_Varchar2, max(C3_Date)
          from table1 t1_2
         where C3_Date < :upper_date_bound
           and C3_Date >= :lower_date_bound
      group by C1_Varchar2, C2_Varchar2)

- , , "IN" "EXISTS" "NOT IN". , C3_Date,

...
exists (select null from table1 t1_2
         where t1_2.C1_Varchar2 = t1_1.C1_Varchar2
           and t1_2.C2_Varchar2 = t1_1.C2_Varchar2
           and t1_2.C3_Date = t1_1.C3_Date
           /* don't forget about partition selectivity hint */
           and t1_2.C3_Date < :upper_date_bound
           and t1_2.C3_Date >= :lower_date_bound         
         group by t1_2.C1_Varchar2, t1_2.C2_Varchar2
        having t1_1.C3_Date < max(t1_2.C3_Date))  

-

Answer 2

( 1 ) .

12% 1 ( 200 )!

ORA-01555 ( ). UNDO.

, , (1 ).

, max min C3_Date 20 . .

'C3_Date' , "INDEX SKIP SCAN". . !

var i_days number ;                             
SELECT (max(C3_Date) - min(C3_Date)) into :i_days  from TABLE1 where C3_Date < ADD_MONTHS(SYSDATE, -20);

-- CHANGE i_mig_start_date IN PROD, if required
var i_mig_start_date varchar2(30); 
exec :i_mig_start_date := ADD_MONTHS(SYSDATE, -20)); 
--exec :i_mig_start_date := '03-OCT-2016 16:00:00';
declare 
    i number;
    v_sql VARCHAR2(4000);
    l_cmd_str VARCHAR2(4000);
    l_from_datetime date;
    l_to_datetime date;
begin
    -- Process chunk  (1 day)
    FOR i IN 1..:i_days LOOP
         l_from_datetime := to_date(:i_mig_start_date,'DD-MON-YYYY HH24:MI:SS')-(i);
         l_to_datetime   := to_date(:i_mig_start_date,'DD-MON-YYYY HH24:MI:SS')-(i-1);

        l_cmd_str := 'DELETE from /*+ PARALLEL(TABLE1  4)*/ TABLE1 
                    where ROWID IN (SELECT rid from (SELECT ROWID rid, ROW_NUMBER() over (PARTITION BY C1_Varchar2,C2_Varchar2 ORDER BY C3_Date desc) as Rank 
                                                from TABLE1 where C3_Date > to_date(:l_from_datetime) and C3_Date <= to_date(:l_to_datetime))
                                    where Rank <> 1
                                    )';
        DBMS_OUTPUT.PUT_LINE('Processing cycle i #'              || i || ' From: ' || l_from_datetime || ' To: ' || l_to_datetime ) ;
        DBMS_OUTPUT.PUT_LINE(l_cmd_str) ;
        execute immediate l_cmd_str using l_from_datetime, l_to_datetime;
        commit;
    END LOOP;    
end;
/