Re: Some questions on truncating a partition in Oracle

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

> The Oracle version is 8.1.6.3.1.

That's gettin a bit long in the tooth... Time to look at upgrading to at least 8.1.7? Latest patch level for that OS, of course.

> 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
I really don't like this "mix". It looks like you've progressively moved things into LMT as you felt more comfy with the concept? Byte the proverbial and go LMT all the way. Hang on, wait until you're at 8.1.7, patched up.

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

That's good.

> 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.

I don't know for sure, but I'd guess you also have indexes? When you drop the partition, the indexes may be the cause for the temp tablespace activity. Depending on where they are global or local.

I'd say also this: if you have progressively moved to LMT, it is quite possible that your SYSTEM tablespace may be quite disorganized as a result of prior heavy use of DMT. Time for a full export/clear/import? As in full reorg? Hopefully the last time you need one!

>
> 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?

Actually, row chaining can happen without updates: all you need is a few of the rows to be larger than the block size. But agreed: that won't be your problem here. Most likely.

>
> 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?

You're just about there. LMT all the way through. Make sure your indexes are local. Another thing you may try: make each partition into a separate tablespace. Minimizes overhead into other allocation tables from the partition being TRUNCATEd. And vicky-the-versa. Make the LMT "chunk" size something reasonable with respect to the size of table you dealing with. If it is large, 64K won't cut it for speed of TRUNCATE.

> 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.

I don't think that is a problem. Maybe in 8.0 but not in 8i onwards. SELECT certainly would be allowed since 8.0. I know because we had this a few years ago at a VLDB in 8.0 and it wasn't a problem. We could happily truncate/reload without stopping other sessions doing SELECT.
Dunno about other DML on 8.1.6, depends on how the partitioning is setup as well. Experiment? Maybe if Jonathan or one of the others is online they can step-in with more ideas.

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