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(at)yahoo(dot)com)
>
>
>
>
>
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
>

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