7 Replies Latest reply: Feb 15, 2013 8:46 AM by rahulras RSS

    Deleting Data from 85 tables

    991200
      Hi All,

      There is a 13 years old data in DB, which holds data from 2001. The task assigned to me is to delete the old data from Main table and its associated tables.
      87 tables are identified and data needs to be removed.
      1) We don't have an option to bring down the DB, the DB should be online.
      2) We can't disable triggers and constratins.
      3) We can't disable LOGGING
      4) The total Size of the tables that we are going to do the deleting is around 290 GB.
      5) DB is Oracle 11g.

      What I did is,
      I wrote a Stored Procedure, that get's MONTH and YEAR as input parameter. This procedure is called 12 times to delete one year.
      I have collected all the ID’s and stored in a separate table. Based on the id, I am deleting the data from each table and committing. (I was doing a bulk commit after deleting some group of tables, but those commits took lot of time.).
      Now total time to delete 150 Million Rows from the entire table is 4 days to delete 7 years of data.
      Is it any way I can make the process faster other than adding INDEX to avoid full table scans.

      is there is a better approach to handle this?.

      Advance Thanks for your response.

      -Jac
        • 1. Re: Deleting Data from 85 tables
          sb92075
          988197 wrote:
          Hi All,

          There is a 13 years old data in DB, which holds data from 2001. The task assigned to me is to delete the old data from Main table and its associated tables.
          87 tables are identified and data needs to be removed.
          1) We don't have an option to bring down the DB, the DB should be online.
          2) We can't disable triggers and constratins.
          3) We can't disable LOGGING
          4) The total Size of the tables that we are going to do the deleting is around 290 GB.
          5) DB is Oracle 11g.

          What I did is,
          I wrote a Stored Procedure, that get's MONTH and YEAR as input parameter. This procedure is called 12 times to delete one year.
          I have collected all the ID’s and stored in a separate table. Based on the id, I am deleting the data from each table and committing. (I was doing a bulk commit after deleting some group of tables, but those commits took lot of time.).
          Please quantify "lot of time".
          EXACTLY how was the time to complete COMMIT actually measured?

          PL/SQL will NEVER be faster than plain SQL
          • 2. Re: Deleting Data from 85 tables
            SomeoneElse
            is there is a better approach to handle this?.
            Partitioning.
            • 3. Re: Deleting Data from 85 tables
              rp0428
              Welcome to the forum!

              Whenever you post provide your 4 digit Oracle version.
              >
              Now total time to delete 150 Million Rows from the entire table is 4 days to delete 7 years of data.
              Is it any way I can make the process faster other than adding INDEX to avoid full table scans.

              is there is a better approach to handle this?.
              >
              Sometimes the best answer is: don't do anything except what you are doing now.

              Since this is a one-time operation and you are over half way done what would be the point of trying another approach?

              Assuming that you want to keep at least 1 year of data you only have 3 more years to go. That is only two days. By the end of the weekend you will be done and can forget about it.

              In my opinion this is one of those times where you should just finish what you started. The time to ask about a 'better approach' would be in the planning stage before you actually start doing it.

              Hopefully you are in archive log mode and are taking regular backups?
              • 4. Re: Deleting Data from 85 tables
                EdStevens
                988197 wrote:
                Hi All,

                There is a 13 years old data in DB, which holds data from 2001. The task assigned to me is to delete the old data from Main table and its associated tables.
                87 tables are identified and data needs to be removed.
                1) We don't have an option to bring down the DB, the DB should be online.
                2) We can't disable triggers and constratins.
                3) We can't disable LOGGING
                4) The total Size of the tables that we are going to do the deleting is around 290 GB.
                5) DB is Oracle 11g.

                What I did is,
                I wrote a Stored Procedure, that get's MONTH and YEAR as input parameter. This procedure is called 12 times to delete one year.
                I have collected all the ID’s and stored in a separate table. Based on the id, I am deleting the data from each table and committing. (I was doing a bulk commit after deleting some group of tables, but those commits took lot of time.).
                Now total time to delete 150 Million Rows from the entire table is 4 days to delete 7 years of data.
                Is it any way I can make the process faster other than adding INDEX to avoid full table scans.
                If you added another index, that would likely make the operation SLOWER because every time a row is deleted, the index would also have to be changed to reflect that fact. Indexes introduce more work to be done on DML operations.

                RP0428 has it right. This is a one-time operation and you are already well down the road. Just keep going.

                is there is a better approach to handle this?.

                Advance Thanks for your response.

                -Jac
                • 5. Re: Deleting Data from 85 tables
                  Fran
                  did you think about:
                  create a new table as select * your table (with rows that you want preserve)>> truncate old table >> insert new table into old table.

                  HTH
                  • 6. Re: Deleting Data from 85 tables
                    riedelme
                    988197 wrote:
                    Hi All,

                    There is a 13 years old data in DB, which holds data from 2001. The task assigned to me is to delete the old data from Main table and its associated tables.
                    87 tables are identified and data needs to be removed.
                    1) We don't have an option to bring down the DB, the DB should be online.
                    2) We can't disable triggers and constratins.
                    3) We can't disable LOGGING
                    4) The total Size of the tables that we are going to do the deleting is around 290 GB.
                    5) DB is Oracle 11g.
                    Do you have the licence for the parallel query option? if so - expecially if you are doing full table scans - consider running the deletes in parallel. You may have to enable parallel dml; check the docs to see how and check it with an execution plan (an easy command that I can't remember at this second. It is early in the morning here)

                    That aside rpo428's advice is really good. You are halfway there on a one-shot process so just finishing might be the best option
                    • 7. Re: Deleting Data from 85 tables
                      rahulras
                      Looks like DELETE is the only option you have got. Partitioning an existing non-partitioned table is a huge task in itself. And no need to worry about logging.
                      Also, I am assuming, while your are deleting data, normal system operation is in progress (i.e. insert/update on the same tables is also going on).

                      When you are running your deletion process, what are the main wait events? Study them carefully.
                      How big are the redo logs? can you make them bigger?
                      How big is your SGA? Buffer cache?

                      I would suggest
                      1) Increase your SGA to maximum you can have, which will reduce I/O, which can speed up the whole thing a lot. This will need a discussion with DBAs and few minutes downtime to the database
                      2) Keep your commit frequency to a "reasonable" size. Don't commit after every delete (of related data from all tables) and also don't wait for deleting tens of thousands of records before committing
                      3) Check the wait events and make sure you have big enough redo logs

                      Best of luck.