Re: [HACKERS] Oracle TRUNCATE statement

What did we decide on this?

> Hello,
> 
> For What Its Worth:
> 
> I am just "Joe User" so please forgive my ignorance.
> I have a patch for 6.5 which implements the Oracle
> TRUNCATE statement.
> 
> >From the Oracle Server 7 manual...
> 
> You can use the TRUNCATE command to quickly remove
> all rows from a table.  Removing rows with the 
> TRUNCATE command is faster than removing rows with 
> the DELETE command for these reasons:
> 
> 1] The TRUNCATE command is a Data Definition Language 
> command and generates no rollback information.
> 
> 2] Truncating a table does not fire the table's
> DELETE triggers.
> 
> Deleting rows with the TRUNCATE command is also more 
> convienient for these reasons:
> 
> 1] Dropping and recreating invalidates the table's
> dependent objects, while truncating does not.
> 
> 2] Dropping and recreating requires you to regrant
> object privileges while truncating does not.
> 
> 3] Dropping and recreating requires you to recreate
> the table's indexes and integrity constraints 
> while truncating does not.
> 
> You cannot rollback a TRUNCATE statement.
> 
> ....
> 
> In addition, using the TRUNCATE statement on large
> tables before a vacuum dramatically reduces 
> vacuuming times, since vacuum no longer needs to 
> perform large index deletes (row by row) for a newly
> emptied table.
> 
> For example, on my Linux RedHat 90Mhz Pentium, 48M
> RAM, a DELETE on a 30K row table tabkes approx.
> 5 seconds.  Vacuuming the table takes minutes and
> consumes all RAM on the machine.  The TRUNCATE
> command, however, is instantaneous.
> 
> Anyways, what should I do with this patch? Is this 
> something people would want?  We do large imports of
> mainframe datasets into tables on a nightly basis.
> We intend to grant select privileges on these tables 
> to a large base of users (a network of hospitals),
> which will be using the system 24 hours a day, 
> 7 days a week.  The TRUNCATE command is used to make
> administration of privileges more sane, allow for
> referential integrity triggers (check_primary_key)
> to be used on a table which needs to be "refreshed"
> on a nightly basis, and allows for faster processing.
> 
> It patches cleanly against 6.5beta, and I have a 
> patch for 6.4 as well...
> 
> What should I do?
> 
> Marcus Mascari (mascarim@yahoo.com)
> 
> 
> 
> 
> 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026