Re: Some questions on truncating a partition in Oracle

From: Johnny Yeung <johnny.yeung_at_unforgettable.com>
Date: 21 Jul 2003 21:48:52 -0700
Message-ID: <55b03025.0307212048.378b8055@posting.google.com>

Hi Noons, Quarkman, Dave and other experts,

Thanks for the input from you all! :)

Right, I should have a more detailed description on the current DB environment.

The OS I use is Compaq Tru64 5.0A Unix
The Oracle version is 8.1.6.3.1.

In terms of the tablespace setup, I would say it's a hybrid of locally managed tablespaces (LMT) and dictionary managed tablespaces (DMT), as follows:

SYSTEM   - DMT
RBS      - DMT
TEMP     - LMT  (Actually, it's changed from DMT to LMT yesterday)
USER     - LMT
INDEX    - LMT

However, for my partitioned table, it should be inside the USER tablespace, which is locally managed.

And I did something on the TEMP tablespace yesterday. In the database, my DB's TEMP tablespace was dictionary managed originally. And when I saw it had a lot of rows (around 7000 rows) in the data dict DBA_FREE_SPACE, I moved the TEMP tbs to a locally managed one. And really, the truncate time drops from 10 seconds to 6 seconds. However, I don't understand the mechanics happening inside Oracle. How are they (the USER and TEMP tbs) related? What other system tablespaces are in play? That's why I ask the steps of truncate in my first posting.

In terms of the row chaining, i think it should not be the causing trouble here. Let me write a bit about my program.

For my table, I have 3 partitions. You can view it as 3 snapshots of the same data (at different time).

Everyday, I will do 3 round of data loading.

Round 1: truncate partition A, and reload data into partition A.
Round 2: truncate partition B, and reload data into partition B.
Round 3: truncate partition C, and reload data into partition C.

While I am reloading a partition, other programs may make queries on other partitions.

So, in terms of row chaining, as my operation is always truncate-then-insert, I think there should not be row chaining problem here. Am I correct? If not, how to see whether there are any row chainings?

A lengthy message, so, let me state again my objectives on my posting: 1. How to make a 'TRUNCATE PARTITION' faster, given my current environment?
2. During a table truncate, can other DML statements (here, SELECT) be issued at the same time? I can make sure that the select statement is not made on the partition I am truncating.

Thanks very much in advance! :)

Cheers,
Johnny. Received on Mon Jul 21 2003 - 23:48:52 CDT