4 Replies Latest reply: Jul 26, 2013 3:27 PM by Greg Spall RSS

    procedure to query and call another procedure to delete

    khallas301

      Sorry for long story since I wanted to give clear understandings.

       

      Friends yesterday I requested plsql to complete tricky task but it turns out I

      can't use simple delete statement to delete data due to size.

       

      Case Study:

      Application generate 1m audit logs everday and stores log into audit_logs1 &

      audit_logs2 tables.

       

      Requirements:

      Since app generates 1m rows everday business needs to use perl script to delete

      logs older than 2 months leaving Sunday & 1st of the month log but delete 1 day at

      a time in loop and not in single delete statement.

       

      Process flow:

      1. Procedure: call_delete()

          1.1 Check if it's First Saturday of the month if yes THEN

          1.2 query to get "created" date older than 2 months and date !='SUN' & !

      ='01'

          1.3 and for every date found in 1.2 delete 1 date at a time by calling

            procedure delete_logs()

       

      2. Procedure: delete_logs(v_created IN DATE)

              Procedure delete_logs(v_created IN DATE)

          IS

          BEGIN

          DELETE FROM AUDIT_LOGS1

          WHERE TRUNC(CREATED) = v_created;

       

          DELETE FROM AUDIT_LOGS2

          WHERE TRUNC(CREATED) = v_created;

       

          END delete_logs;

       

      -- Yesterday I endup creating below delete which I think I can still use for

      select query for 1.2 to get required dates to delete

      DELETE FROM AUDIT_LOGS

      WHERE TRUNC (CREATED) < ADD_MONTHS(TRUNC(SYSDATE), -2)

      AND TO_CHAR (CREATED, 'DY') != 'SUN'

      AND TO_CHAR (CREATED, 'DD') != '01'

       

      so basically idea is to use procedure call_delete() to find dates to delete and

      call delete_logs() for every date found.

      Both procedure are under same pkg and package has public constants as

      dateFormat   Constant   VARCHAR2(22) := 'mm/dd/yyyy hh24:mi:ss';

       

      can some guru help me out?

       

      Thanks,

        • 1. Re: procedure to query and call another procedure to delete
          34MCA2K2

          Sorry for pitching in, (More specifically as this call is for Gurus).. yeah this query will delete the records, however with this kind of table & data, why don't you consider partitioning and then partition purging, which can be much more efficient in every sense.

           

          Regards,

          • 2. Re: procedure to query and call another procedure to delete
            Frank Kulash

            Hi,


            You might want a procedure to delete a single day's data for some other reason, but it won't help this job.  Stick with the solution you got yesterday.

            If you need a statement that will only DELETE rows if it is run on the 1st Saturday of a month, then you can add conditions to your DELETE statement, like this:

             

            DELETE FROM  audit_logs1

            WHERE        created < ADD_MONTHS (SYSDATE, -2)

            AND          TO_CHAR (created, 'DY') != 'SUN'

            AND          TO_CHAR (created, 'DD') != '01';

            AND          TO_CHAR (SYSDATE, 'DY')  = 'SAT'    -- *** NEW  ***

            AND          TO_CHAR (SYSDATE, 'DD') <= '07'     -- *** NEW  ***

            ;

             

            No PL/SQL is required, but if you have some reason for using PL/SQL, then the DELETE statement above will work just as well in PL/SQL.

             

            As mentioned above, if you're using Oracle Enterprise Edition, you might consider partitioning the table by day, and dropping the unwanted partitions.

            .

             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), a couple of run dates, and  the results you want from that data (that is, what's left in the table) for each of the run dates.
            Point out where the statement above is getting the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.

            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

            • 3. Re: procedure to query and call another procedure to delete
              khallas301

              Oracle version is 11.2.0.3

              thanks frank... I will try to provide more details as mentioned in this and more comments.

               

              mentioned delete statement will works but since pkg is already using delete_logs() procedure my senior developer don't want to have another delete statement, so suggested to get dates to delete and feed into delete_logs() procedure.

               

              so I'm trying to use call_delete() procedure to query and loop everydate into delete_logs().

              • 4. Re: procedure to query and call another procedure to delete
                Greg Spall

                34MCA2K2 wrote:

                 

                Sorry for pitching in, (More specifically as this call is for Gurus).. yeah this query will delete the records, however with this kind of table & data, why don't you consider partitioning and then partition purging, which can be much more efficient in every sense.

                 

                Regards,

                Precisely what I was going to suggest.

                 

                create table audit_logs_partitioned
                     partition by range ( created )  interval ( numtoyminterval(1,'MONTH') )
                       (partition p0 values less than (to_date('20130701','yyyymmdd')))
                as select * from audit_logs;
                -- setup PKs, indexes, grants, etc.
                
                rename audit_logs to audit_logs old;
                rename audit_logs_partitioned to audit_logs;
                -- change synonyms make sure point to audit_logs
                
                
                

                 

                Problems solved.

                 

                Then all you need to do is cleanup old partitions when you want to purge old data:

                 

                alter table audit_logs drop partition '<partition name>';
                
                
                

                 

                That'll go very quickly.

                 

                [edit]

                oh, by day? yeah, same difference, just alter the interval to numtodsinterval(1,'day') if you get that many .. wow lot of audit lol

                My vote is definitely still partitions - saves alot of headache in this kind of scenario