Re: Best way to delete million records

From: <paa.listas_at_gmail.com>
Date: Sat, 16 Aug 2008 21:17:37 -0700 (PDT)
Message-ID: <cf74d9a0-64b6-4d92-a415-d6823fcb527b@l42g2000hsc.googlegroups.com>

On 30 jun, 05:00, fefe78 <pe..._at_puppurnazzi.com> wrote:
> Hi all,
>
> I have to delete about 43 millions rows from a table, but the following
> conditions exist:
>
> - The table is not partitioned and unfortunately I can't modify the
> structure
> - The delete procedure must be done without stopping service, so I can't
> use CTAS, moving records and renaming table.
> - I can't use truncate because the records affected are selected in a
> range of dates
>
> Waiting your suggestions and probable problems for this procedure.
>
> Thanks for help.
>
> Bye

Hello,

 i have a similar problem and use someting like this:

---

DECLARE

• 1 month ITERACIONES CONSTANT NUMBER := 30;
• 2 years. your oldest record timestamp OLDEST_DATE_ CONSTANT NUMBER := 730; oldest_date DATE;

KEEP_DATE CONSTANT NUMBER := 60; STEP_DATE CONSTANT NUMBER := 1;
loop_date DATE;

BEGIN
    dbms_output.put_line('.- Iniciando a las: ' || SYSDATE );

• Inicializamos
• set server outout on format wrapped; SELECT SYSDATE - OLDEST_DATE_ INTO oldest_date FROM DUAL; SELECT oldest_date INTO loop_date FROM DUAL;

    FOR x IN 1..ITERACIONES LOOP

        dbms_output.put_line('Iniciando procedimiento nro: ' || x);
        dbms_output.put_line('borrando registros entre Fechas ' ||

oldest_date || ' y ' || loop_date );

• DELETE...; DELETE FROM iwaylogs.cm_leases WHERE iwaylogs.cm_leases.fecha BETWEEN oldest_date AND loop_date;
• COMMIT; COMMIT;
• adelanto la fecha oldest_date := loop_date;
• adelanto el loop loop_date := loop_date + STEP_DATE;
• Si ya llegue a borrar hasta donde quiero salgo IF ( loop_date >= (SYSDATE - KEEP_DATE) ) THEN dbms_output.put_line('Llegamos a los 60 dias, saliendo'); EXIT; END IF; dbms_output.put_line('Procedimiento listo.');
  • Esperamos XX segundos para 'descansar' la DB :) dbms_lock.sleep(90); END LOOP;

    dbms_output.put_line('Finalizado' );

END;

---

) Received on Sat Aug 16 2008 - 23:17:37 CDT