SQL & PL/SQL

Hi Guys!

I've a table whose data needs to be deleted periodically.

Previously i use simple DML statement. But, the problem is for every deletion this process deletes roughly 2850000 rows.

And, it is taking almost 30 mins to complete.

sess1>ed
Wrote file afiedt.buf

  1  explain plan for
  2  DELETE from perftxn_admin.perf_txn
  3* WHERE evnt_dt = '&&in_del_txn_dt'
sess1>/
Enter value for in_del_txn_dt: 2008-07-20
old   3: WHERE evnt_dt = '&&in_del_txn_dt'
new   3: WHERE evnt_dt = '2008-07-20'

Explained.

Elapsed: 00:00:00.03
sess1>
sess1>
sess1>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |  2331K|   117M| 13508 |
|   1 |  DELETE              | PERF_TXN    |       |       |       |
|*  2 |   TABLE ACCESS FULL  | PERF_TXN    |  2331K|   117M| 13508 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - filter("PERF_TXN"."EVNT_DT"='2008-07-20')

Note: cpu costing is off

15 rows selected.

Elapsed: 00:00:00.09
sess1>

Can anyone explain why this is happening?

I don't have any privs to see the trace file.

Regards