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, Pennsylvania 19026