SQL question

[Dave Hull]

On Sat, 9 Aug 2003, Jim Herrmann wrote:

> Dave Hull wrote:
> 
> >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.

Sounds right to me. The only time I've ever used autocommit was when I was 
attempting to delete all rows from every table in the db, but didn't have 
enough disk space for the rollback segment to extend itself as needed. By 
setting autocommit to on, the rollback segment was flushed after each 'delete 
from tablename' completed and as a result, the rollback segment never became 
filled up. It's not appropriate in Brian's situation.

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

Ci.

-- 
Dave Hull
http://insipid.com

One way to stop a runaway horse is to bet on him.