On Aug 21, 6:17 am, sakthi <[email protected]> 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 configuration
(shared pool, large pool, pga aggregate), network speed, server and
database activity, disk latency ... too many items to speculate upon
without solid information. These times, remember, are for deleting
the same number of records with and without an index; smaller volumes
of deleted data will take less time.
Until you supply much more information than you have already the best
we can do is speculate. Give us usuable data and you can possibly get
usable answers.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
