If you have to delete most of the records insert those you need in a temp
table, truncate the other one and insert them back.
Hey Rob, I see you like spending some time doing testings. Try this one and
let me know.
2009/8/22 Rob Wolfe <wolfe....@gmail.com>
>
>
>
> On Aug 21, 12:33 pm, ddf <orat...@msn.com> wrote:
> > On Aug 21, 6:17 am, sakthi <ihimi...@gmail.com> wrote:
> >
> >
> >
> >
> >
> > > Hi Experts,
> >
> > > I m have problem with sql delete statement.
> >
> > > When the no. of records is less in my table , its getting deleted
> > > successfully with in a minute.
> >
> > > But if my table have 10 lac records , and i m tried to delete some of
> > > 60000 records based on query condition,it is taking more then 3 hours
> > > to perform this operation.
> > > I m using oracle 10.2 g verion
> >
> > > Please guide me to do the correct thing....
> >
> > > Thanks in advance,
> > > Sakthi
> >
> > Then supply the query plan for this delete as I suspect it's
> > performing a full table scan. No one will know for certain until we
> > see how the optimizer has decided to execute that statement. Let's
> > look at a rather extreme example:
> >
> > SQL> --
> > SQL> -- Create test table
> > SQL> --
> > SQL> create table delete_test(
> > 2 recnum number not null,
> > 3 recid varchar2(40),
> > 4 recmode number not null,
> > 5 recdata varchar2(4000),
> > 6 addrecdata varchar2(4000)
> > 7 );
> >
> > Table created.
> >
> > SQL>
> > SQL> --
> > SQL> -- Populate test table
> > SQL> --
> > SQL> -- Rows will be longer than a single block
> > SQL> --
> > SQL> begin
> > 2 for i in 1..1000000 loop
> > 3 insert into delete_test
> > 4 values(i, 'ABXD'||i||'RFG'||i||'XZQY'||i||i,
> mod(i,16),
> > rpad('X', 4000, 'X'), rpad('Y', 4000, 'Y'));
> > 5 end loop;
> > 6
> > 7 commit;
> > 8
> > 9 end;
> > 10 /
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL>
> > SQL> --
> > SQL> -- Gather statistics
> > SQL> --
> > SQL>
> > SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> > tabname=>'DELETE_TEST', cascade=>true, estimate_percent=>null);
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL>
> > SQL> set autotrace on timing on
> > SQL>
> > SQL> --
> > SQL> -- Delete ~60000 rows
> > SQL> --
> > SQL> -- Scan entire table for matching rows
> > SQL> --
> > SQL> delete from delete_test
> > 2 where recmode = 7;
> >
> > 62500 rows deleted.
> >
> > Elapsed: 00:23:00.42 <--- note the time without an index
> >
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 2974503846
> >
> >
> ---------------------------------------------------------------------------
> -------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> >
> ---------------------------------------------------------------------------
> -------
> > | 0 | DELETE STATEMENT | | 62500 | 183K| 542K
> > (1)| 01:48:29 |
> > | 1 | DELETE | DELETE_TEST | | |
> > | |
> > |* 2 | TABLE ACCESS FULL| DELETE_TEST | 62500 | 183K| 542K
> > (1)| 01:48:29 |
> >
> ---------------------------------------------------------------------------
> -------
> >
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 2 - filter("RECMODE"=7)
> >
> > Statistics
> > ----------------------------------------------------------
> > 853 recursive calls
> > 503350 db block gets
> > 4007359 consistent gets
> > 1997740 physical reads
> > 692471732 redo size
> > 680 bytes sent via SQL*Net to client
> > 570 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 4 sorts (memory)
> > 0 sorts (disk)
> > 62500 rows processed
> >
> > SQL>
> > SQL> rollback;
> >
> > Rollback complete.
> >
> > Elapsed: 00:11:19.98
> > SQL>
> > SQL> --
> > SQL> -- Index the governing column
> > SQL> --
> > SQL> create index delete_test_idx
> > 2 on delete_test(recmode);
> >
> > Index created.
> >
> > Elapsed: 00:07:04.95
> > SQL>
> > SQL> --
> > SQL> -- Gather statistics
> > SQL> --
> > SQL>
> > SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> > tabname=>'DELETE_TEST', cascade=>true, estimate_percent=>null);
> >
> > PL/SQL procedure successfully completed.
> >
> > Elapsed: 00:15:17.92
> > SQL>
> > SQL> --
> > SQL> -- Execute delete again
> > SQL> --
> > SQL> -- Use index
> > SQL> --
> > SQL> delete from delete_test
> > 2 where recmode = 7;
> >
> > 62500 rows deleted.
> >
> > Elapsed: 00:09:18.54 <--- note the time with an index
> >
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 719378220
> >
> >
> ---------------------------------------------------------------------------
> ----------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> >
> ---------------------------------------------------------------------------
> ----------
> > | 0 | DELETE STATEMENT | | 62500 | 183K| 125
> > (1)| 00:00:02 |
> > | 1 | DELETE | DELETE_TEST | |
> > | | |
> > |* 2 | INDEX RANGE SCAN| DELETE_TEST_IDX | 62500 | 183K| 125
> > (1)| 00:00:02 |
> >
> ---------------------------------------------------------------------------
> ----------
> >
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 2 - access("RECMODE"=7)
> >
> > Statistics
> > ----------------------------------------------------------
> > 429 recursive calls
> > 501814 db block gets
> > 299 consistent gets
> > 117175 physical reads
> > 540836956 redo size
> > 680 bytes sent via SQL*Net to client
> > 570 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 3 sorts (memory)
> > 0 sorts (disk)
> > 62500 rows processed
> >
> > SQL>
> > SQL> commit;
> >
> > Commit complete.
> >
> > Elapsed: 00:00:00.00
> > SQL>
> >
> > Remember these rows were chained, which dramatically increased the
> > processing time. If we choose to not chain rows:
> >
> > SQL> --
> > SQL> -- Create test table
> > SQL> --
> > SQL> create table delete_test(
> > 2 recnum number not null,
> > 3 recid varchar2(40),
> > 4 recmode number not null,
> > 5 recdata varchar2(1000)
> > 6 );
> >
> > Table created.
> >
> > SQL>
> > SQL> --
> > SQL> -- Populate test table
> > SQL> --
> > SQL> begin
> > 2 for i in 1..1000000 loop
> > 3 insert into delete_test
> > 4 values(i, 'ABXD'||i||'RFG'||i||'XZQY'||i||i,
> mod(i,16),
> > rpad('X', 999, 'X'));
> > 5 end loop;
> > 6
> > 7 commit;
> > 8
> > 9 end;
> > 10 /
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL>
> > SQL> --
> > SQL> -- Gather statistics
> > SQL> --
> > SQL>
> > SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> > tabname=>'DELETE_TEST', cascade=>true, estimate_percent=>null);
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL>
> > SQL> set autotrace on timing on
> > SQL>
> > SQL> --
> > SQL> -- Delete ~60000 rows
> > SQL> --
> > SQL> -- Scan entire table for matching rows
> > SQL> --
> > SQL> delete from delete_test
> > 2 where recmode = 7;
> >
> > 62500 rows deleted.
> >
> > Elapsed: 00:01:53.22 <--- without index
> >
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 2974503846
> >
> >
> ---------------------------------------------------------------------------
> -------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> >
> ---------------------------------------------------------------------------
> -------
> > | 0 | DELETE STATEMENT | | 62500 | 183K| 46483
> > (1)| 00:09:18 |
> > | 1 | DELETE | DELETE_TEST | | |
> > | |
> > |* 2 | TABLE ACCESS FULL| DELETE_TEST | 62500 | 183K| 46483
> > (1)| 00:09:18 |
> >
> ---------------------------------------------------------------------------
> -------
> >
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 2 - filter("RECMODE"=7)
> >
> > Statistics
> > ----------------------------------------------------------
> > 116 recursive calls
> > 143528 db block gets
> > 333480 consistent gets
> > 164413 physical reads
> > 97068784 redo size
> > 681 bytes sent via SQL*Net to client
> > 570 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 1 sorts (memory)
> > 0 sorts (disk)
> > 62500 rows processed
> >
> > SQL>
> > SQL> rollback;
> >
> > Rollback complete.
> >
> > Elapsed: 00:01:51.40
> > SQL>
> > SQL> --
> > SQL> -- Index the governing column
> > SQL> --
> > SQL> create index delete_test_idx
> > 2 on delete_test(recmode);
> >
> > Index created.
> >
> > Elapsed: 00:00:37.65
> > SQL>
> > SQL> --
> > SQL> -- Gather statistics
> > SQL> --
> > SQL>
> > SQL> exec dbms_stats.gather_table_stats(ownname=>null,
> > tabname=>'DELETE_TEST', cascade=>true, estimate_percent=>null);
> >
> > PL/SQL procedure successfully completed.
> >
> > Elapsed: 00:00:52.01
> > SQL>
> > SQL> --
> > SQL> -- Execute delete again
> > SQL> --
> > SQL> -- Use index
> > SQL> --
> > SQL> delete from delete_test
> > 2 where recmode = 7;
> >
> > 62500 rows deleted.
> >
> > Elapsed: 00:01:08.34 <--- with index
> >
> > Execution Plan
> > ----------------------------------------------------------
> > Plan hash value: 719378220
> >
> >
> ---------------------------------------------------------------------------
> ----------
> > | Id | Operation | Name | Rows | Bytes | Cost
> > (%CPU)| Time |
> >
> ---------------------------------------------------------------------------
> ----------
> > | 0 | DELETE STATEMENT | | 62500 | 183K| 125
> > (1)| 00:00:02 |
> > | 1 | DELETE | DELETE_TEST | |
> > | | |
> > |* 2 | INDEX RANGE SCAN| DELETE_TEST_IDX | 62500 | 183K| 125
> > (1)| 00:00:02 |
> >
> ---------------------------------------------------------------------------
> ----------
> >
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> >
> > 2 - access("RECMODE"=7)
> >
> > Statistics
> > ----------------------------------------------------------
> > 116 recursive calls
> > 144995 db block gets
> > 144 consistent gets
> > 47069 physical reads
> > 86034848 redo size
> > 681 bytes sent via SQL*Net to client
> > 570 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 2 sorts (memory)
> > 0 sorts (disk)
> > 62500 rows processed
> >
> > SQL>
> > SQL> commit;
> >
> > Commit complete.
> >
> > Elapsed: 00:00:00.04
> > SQL>
> >
> > Of course all of this is affected by the operating system, server
> > configuration (disks, available memory), instance...
> >
> > read more ยป
>
> David, That was a thing of beauty.
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
