Forum Stats

  • 3,734,273 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

deletion of records older then 90 days

chandra_1986
chandra_1986 Member Posts: 262 Blue Ribbon

HI experts,


we have table where a4297735 records from below condition on table J_DOC, we need to delete the records older then 90 days for those status='Pent' .

First we need to store this records into temp table, while creating temp table on below condition database is getting stopped, kindly suggest how can we store into temp table. to delete records older then 90 days and status='Pent' from table J_DOC.'


 SELECT count(*)

 FROM J_DOC

 WHERE LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status='Pent';

 

 4297735

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    Why do you need to store them in a "temp table"? If you're doing that then you're retaining the records anyway, so why not just have a column on the existing table to mark the record as "deleted" if it's only going to be virtually deleted.

    If you really want to delete it, then just delete it.

    And it's not clear what you mean by "database is getting stopped". We don't see your code for how you are creating your temp table, nor what issue is happening when you do that. Copying records to another table would just be a simple INSERT ... SELECT statement (or CREATE TABLE ... AS SELECT ... statement if appropriate)

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    HI Experts,


    We are creating temp table as:


    create temp_j_doc as

     SELECT *

     FROM J_DOC

     WHERE LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status='Pent';

     

    as records are :  4297735 so database is getting stopped.


    We need to make procedure where we have to delete first from SIGNATURE_DETAILS table where JOB_ID columns are same as present in J_DOC table.

    We have to delete data older that 90 days with STATUS is SENT. We have status column only on JOB_DOCUMENT table. 

    So we need to create a temp table to perform this deletes from J_DOC table.


    Kindly suggest.

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,280 Red Diamond

    The question about why a temp table is used, remains unanswered.

    What happens to the temp table post the delete process?

    If the database is "stopped" during the DML CTAS (Create Table As Select), the process can be rolled back. So why is the database being stopped an issue?

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Thanks experts, as there are 4297735  number of records so database is getting stopped.

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,280 Red Diamond

    Database does not stop just because a table with 4297735 rows are created.

    What happens to the temp table post the delete process? In other words, what do you do with the temp table after using it to delete rows in the SIGNATURE_DETAILS and J_DOC tables? It is dropped?

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Yes experts, will drop that table.

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,280 Red Diamond
    edited Nov 6, 2020 5:52AM

    I/O is the slowest and most expensive database operation.

    You do I/O to read the rows to delete, and then I/O to make copies of the rows to delete. Then I/O to read the copied rows, and more I/O to find and delete the matching rows in two tables (so more I/O x 2).

    You need to reconsider this approach of using loads of I/O overheads by first making temporary copies of the rows to delete.

    If there are no alternatives, the only way to make the process faster is to do as much of the I/O as possible in parallel.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Hi experts,


    am making temp table from below condition to delete older then 90 days

    records as we have to delete first from table SIGNATURE_DETAILS where JOB_ID is same as present in Below table J_DOC , so will get all the job_id which are similar from both table only diffference is tht We have to Delete records older then 90 days with status=‘Pent’ and status column is present in J_DOC table only.


    so our Approach is to first create temp table from below condition and then match this JOB_ID with table SIGNATURE_DETAILS and delete first from SIGNATURE_DETAILS

    and then from J_DOC table.


    SELECT *

     FROM J_DOC

     WHERE LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status='Pent';

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    Kindly suggest how can we make procedure for this thanks experts.

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy

    Again and again and again

    WHY are you creating a Temp Table?

    WHY not just delete from SIGNATURE_DETAILS where JOB_ID in (your J_DOC Query)

    and afterwards

    Delete from J_DOC.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    I tried with

    first select before delete from signature_details table

    select * from Signature_details where job_id IN( select * from j_doc where last_update_time_utc<=Trunc(sysdate) -90 and status=‘Pent’);

    getting Ora error :

    too many values ORA-00913

    kindly suggest

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,280 Red Diamond

    select * from Signature_details where job_id IN( select JOB_ID from j_doc where last_update_time_utc<=Trunc(sysdate) -90 and status=‘Pent’);

    The SQL projection and columns in the predicate need to match - the same record structure is needed for comparison.

  • chandra_1986
    chandra_1986 Member Posts: 262 Blue Ribbon

    thanks experts,


    what kind of procedure we can write to complete this cleanup task on both the dbs according to above requirement condition:

    select * from Signature_details where job_id IN( select * from j_doc where last_update_time_utc<=Trunc(sysdate) -90 and status=‘Pent’

Sign In or Register to comment.