On Aug 22, 5:37 pm, Javier Montani <jmont...@gmail.com> wrote:
> Sorry Rob, I meant David.
>
Since you asked so nicely:
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:55.00 <--- Not much change here
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
----------------------------------------------------------
124 recursive calls
143530 db block gets
333482 consistent gets
164177 physical reads
97065952 redo size
674 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:02:06.19
SQL>
SQL> --
SQL> -- Index the governing column
SQL> --
SQL> create index delete_test_idx
2 on delete_test(recmode);
Index created.
Elapsed: 00:00:39.54
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:51.98
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:06.15 <-- Nor here
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
144994 db block gets
143 consistent gets
45423 physical reads
86035224 redo size
676 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> rollback;
Rollback complete.
Elapsed: 00:02:05.26
SQL>
SQL> --
SQL> -- Create a temporary table of the data to preserve
SQL> --
SQL>
SQL> create table delete_test_tmp
2 as select recnum, recid, recmode, recdata
3 from delete_test
4 where recmode <> 7;
Table created.
Elapsed: 00:01:38.32 <--- So, we start here
SQL>
SQL> --
SQL> -- Truncate the source table
SQL> --
SQL>
SQL> truncate table delete_test;
Table truncated.
Elapsed: 00:00:07.79 <--- Add this time
SQL>
SQL> --
SQL> -- Insert saved data
SQL> --
SQL>
SQL> insert into delete_test
2 select recnum, recid, recmode, recdata
3 from delete_test_tmp;
937500 rows created.
Elapsed: 00:04:22.57 <--- Then add this time and end up taking
00:06:08.68
Execution Plan
----------------------------------------------------------
Plan hash value: 2293286816
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 911K| 478M|
42552 (1)| 00:08:31 |
| 1 | LOAD TABLE CONVENTIONAL | DELETE_TEST | |
| | |
| 2 | TABLE ACCESS FULL | DELETE_TEST_TMP | 911K| 478M|
42552 (1)| 00:08:31 |
--------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7958 recursive calls
4034040 db block gets
449606 consistent gets
156418 physical reads
1342425892 redo size
678 bytes sent via SQL*Net to client
612 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
937500 rows processed
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>
It appears the delete, with an index, is the fastest way to process
this.
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 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
-~----------~----~----~----~------~----~------~--~---
