Re: Some questions on truncating a partition in Oracle

"Johnny Yeung" <johnny.yeung_at_unforgettable.com> wrote in message news:55b03025.0307210446.762b9fbc_at_posting.google.com...

> I have some questions on Oracle partition and truncate. I would be
> grateful of any of your experience here.

In situations like these, it is very important that you tell everyone what your version and platform are. Certain features of Oracle may vary widely between versions, hence the need for more info.

> partition has a total of 120,000 records. When I truncate the empty
> partition, the statement returns immediately. On the other hand, when
> I truncate the other partition, it takes around 10 seconds.

If you are using LMT, then 10 seconds is probably too much. If not, then it's probably OK. Depending on how large those rows are and any potential row chaining that may have happened.

> enough figure or not. For Oracle, what are the steps that will be
> performed in a "TRUNCATE" statement? How to measure the performance
> of each sub-steps? Or, if it is really too slow, any way I can
> improve the situation?

Again, very hard to say without a specific version info and more information about your tablespaces. For example, are they locally managed (LMT) or dictionary managed (DMT)? As for the sub-steps: I wouldn't bother with measuring anything in them. There is not much you can do to split the individual steps of a TRUNCATE so don't worry. Concentrate on getting it overall faster. The way to do that is to use LMT and make sure you don't get row chaining.

> While the statement "ALTER TABLE TEST_PART TRUNCATE PARTITION
> part_a" is being issued, I issue a "select" statement concurrently in
> another session. In this case, will the select be blocked until the
> truncate operation end?

AFAIK, the truncate of a partition doesn't lock access to the table from everyone else. Once again, there might be differences depending on your version. And your indexing, etc etc. 8.0 used to be very limited in this. Got a lot better on 8i and is probably as good as it gets in 9ir2.

--
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam