2

Friends,

I have order tables which have minimum 100 Million records in each table. We have a job running which invokes a stored procedure which deletes atleast 50K (MIN) and 200K (MAX) records per day.

I'm currently using SQL BULK COLLECT to delete records from the table. Currently it is taking more than 4 hours for deleting 50K which is dead slow.

After searching in google, Figured out CTAS method ,i,e Creating table and keeping the records which we want and drop the existing one and rename the temp table. I CANNOT DO THIS AS THIS OPTION WAS NOT ACCEPTED AS THE TABLES ARE MORE CRITICAL.

Could you please suggest some solution to improve the performance of the same?

Thanks in advance!!

Prabhu
  • 115
  • 9
  • What are the 50K records that are deleted ? On what basis is a record selected to be deleted? – A Nice Guy May 15 '14 at 06:41
  • 2
    What's wrong with "delete from where "? What is the bulk collect used for?
     – Rene May 15 '14 at 06:42
  • I'm using FORALL to delete. Records are being deleted based on Time Stamp. – Prabhu May 15 '14 at 07:31
  • Rene- Deleting records one by one will take more time especially table contains 180 million. Also it generates more redo and undo logs.Correct me if I'm wrong? – Prabhu May 15 '14 at 07:36
  • What is your criterion for deleting records? Do you delete the oldest ones? Have you considered partitioning? – Frank Schmitt May 15 '14 at 08:33
  • Yes I'm considering Partition,Tom is suggesting 3 options CASE#1: create table new_table unrecoverable as select * from old_table where ....; drop table old_table; rename new_table to old_table; create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5; ..... NO log on that, just move the data to a new table, drop/rename old/new, create indexes as fast as possible without log. CASE#2: partition the data, do a parallel delete. Each partition will use its own rollback segment, each will run in parallel. THE OPTION LEFT TO ME IS HAVING A PARTION – Prabhu May 15 '14 at 08:53
  • 2
    4 hours to delete 50K rows seems much too long, regardless of how the rows are deleted. You should profile and monitor your code to see exactly where the time is spent. Is there a bad join causing the row selection to take forever? Does the table have dozens of indexes making the deletes very slow? – Jon Heller May 15 '14 at 09:15
  • You do seem to be looking for a solution without having established what the problem really is. Unless you know why it's slow you won't be able to have any confidence that your changes will actually help, and you could end up spending a lot of time and effort for no benefit (or even make things worse). – Alex Poole May 15 '14 at 09:44
  • Jonearles - 4 hours for deleting records in 50 tables. – Prabhu May 15 '14 at 10:40

2 Answers2

1

Assuming you have enough physical disk space to duplicate your data, you could use the following approach (I assume your table is called ORDERS):

  • create a copy ORDER_B of your order table with CREATE TABLE orders_b AS SELECT * FROM orders
  • rename your original table to ORDERS_A: ALTER TABLE orders RENAME TO orders_a
  • create a synonym called ORDERS pointing to ORDERS_A: CREATE SYNONYM orders FOR order_a

So far, so good. Your client code now uses the synonym ORDERS instead of the physical table. Now comes the fun part (the daily refresh routine):

  • truncate ORDERS_B
  • fill ORDERS_B with INSERT /*+APPEND+*/ (you might also want to try the PARALLEL hint)
  • switch the synonym ORDERS to point to ORDERS_B
  • next day: repeat with ORDERS_A instead of ORDERS_B
  • repeat ad infinitum

Alternatively, instead of using TRUNCATE/INSERT, you could DROP the table and re-CREATE it. This requires more work, since you'll also have to recreate indices and grants.

This technique is called synonym switching - you might want to read Tyler Muth's article about synonym switching to get a more complete explanation of it.

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
0

What is the condition for deleting the records?

Maybe you can create a function based index on this table. Then you may delete the records with a single and simple delete statement. Or think about table partitioning. If your partition key is smart and covers the delete condition you can drop or purge the old partition within a few seconds.

Btw, with package DBMS_REDEFINITION you can change a "normal" table to a partitized table even without interruption of service.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110