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
-~----------~----~----~----~------~----~------~--~---
