0

I have a txn table which is having 2 million rows every month i am loading data into that using stored procedure. I need to delete the transactions using the following if it failed during my process and i used to load again .

Table TXN structure

TXN_ID    NUMBER primary key 
TXN_AMT   number
YEAR    varchar2
MONTH   varchar2

Can i used bulk collect here, but in where clause i need

DELETE FROM TXN WHERE YEAR=cYear and Month=cMonth

please advice how i can make it fast

APC
  • 144,005
  • 19
  • 170
  • 281
YesYeeYen
  • 55
  • 2
  • 5
  • 2
    I'm not sure what you're asking. That delete statement should work fine, you shouldn't need a FORALL to do it. If it's running too slow, the best way to diagnose that is with the execution plan. – eaolson Oct 03 '12 at 01:49
  • As mentioned by eaolson, you need to check your query plan and analyze it in context of 1. How many rows you have in your table 2. How many rows are affected by your where clause in your delete statement 3. Is there any index on columns for year and month 4. Is the index being used and so on.. – Jagmag Oct 03 '12 at 02:06

2 Answers2

1

" I need to delete the transactions using the following if it failed during my process and i used to load again ."

It seems your actual problem is the load process. You should define the entire load as a one Unit Of Work, i.e. with a single commit at the end of the 2000000 rows. Then you don't have to delete anything in the event of failure.

At the moment you obviously have intermittent commits. There may be any one of several reasons why you think you need to do this, but they are all bogus. You simply need to get your DBA to size the UNDO tablespace so it is sized appropriately for your data volumes. Alternatively you need to re-write your load process so you can identify which rows have been loaded (i.e. committed); in the event of failure you can just load the remainder.

As far as speeding up the deletion process, you don't have many options. FORALL processing won't be faster: PL/SQL incurs overhead and so won't be cheaper than a straight DELETE. Unless you have a table with a decade's worth of data an index on (YEAR,MONTH) won't be any use.

If you have Enterprise Edition and lots of CPUs you can try use PARALLEL DML to execute the deletion in a shorter elapsed time.

But, as with any tuning problem, you need to understand your statement's current execution path and that means starting with an Explain Plan. They're not just for SELECTs, you know.

APC
  • 144,005
  • 19
  • 170
  • 281
0

As an alternative approach, since you are deleting the data basis Month and Year

  • You can create month wise partitions in the table and drop the partition of a particular month when not required. (ALTER TABLE TABLENAME DROP PARTITION PARTION_NAME)
  • Similarly if the load fails, just truncate that month partition ONLY and load again.
  • You can further write a PL/SQL job that can drop the partition basis a predefined condition, let's say DROP PARTION_DEC_2011 on 1st December 2012. And add a partition 2 months before you will use it to load new data.
Amit
  • 143
  • 1
  • 14
  • Partitioning is a chargeable extra to the Enterprise Edition license. It is not a cheap, let alone a free, programming option. – APC Oct 03 '12 at 07:37