SQL question

[Jim Herrmann]

Dave Hull wrote:

>On Fri, 8 Aug 2003, Brian Densmore wrote:
>
>  
>
>>2) is there anyway to have a single delete query statement
>>do periodic commits (as really dangerous as it is)?
>>    
>>
>
>Depends on the DBMS, I suppose. I know in Oracle one can "set autocommit on" 
>in certain client software and each delete statement will be committed as soon 
>as it is completed. I have resorted to this when deleting all rows from all 
>tables in Oracle.
>  
>

Commiting after every one of the 6 million rows seems like it would be 
excruciating as every one of those commits has to flush the changed 
buffer pages to disk.  That means that if you are deleting 100 rows that 
are on a single data page, you would do 100 writes, rather than one 
write.  That being said, I don't think what you describe would commit 
after every row, but rather every *statement* which could be deleting 6 
million rows at a whack.  No Bueno!

If you are deleting all the rows, I would recommend a load replace using 
a null file as input.  You have to be careful with this and know your 
physical architecture.  We usually put one table per tablespace, thus 
allowing a load replace of the tablespace, with out affecting other 
tables.  DB2 has a feature, that it's had for years, if your table is in 
a segmented tablespace, a "delete from tablename" with no where clause 
wipes out the whole table, regardless of size, in under a second.  
That's because it simply initializes the space management pages.  This 
is called a mass delete, but it's definately not what Brian is looking for.

HTH,
Jim