Sorry Rob, I meant David. 2009/8/22 Javier Montani <[email protected]>
> 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 <[email protected]> > > >> >> >> On Aug 21, 12:33 pm, ddf <[email protected]> wrote: >> > 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... >> > >> > 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 [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 -~----------~----~----~----~------~----~------~--~---
