Truncating an Oracle Partition within a Partitioned Table?

iBinc
iBinc used Ask the Experts™
on
Can someone give me the syntax for truncating a partition within an oracle partitioned table (10g). Also updating the indexes?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
alter table t_part truncate partition q12009;


>>Also updating the indexes?

What do you mean? Do you have a global index on the table or locally partitioned indexes? This is important to know. A truncate / direct path load on a partition will put a global index into unusable state, requiring rebuild.

Author

Commented:
Shru...not trying to truncate a table...just a partition.

mrj, how do I find out what kind of index is on the table? Can you explain the differences? I'm new...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
1) You would have created the table using the "local" keyword in an index or primary key clause.

2) Use this query to list the indexes for the table T_PART

select index_name, index_type, partitioned, status from user_indexes where table_na
me = 'T_PART';


If PARTITIONED column is YES, then it is a locally partitioned index.

The difference between a global index and a locally partitioned index is the same as the difference between a regular table and a partitioned table. The index itself is partitioned corresponding to each table partition. So you can manage each partition as a separate table with its own indexes.

The main thing is just to check the status of the index after the truncate / load, and run an index rebuild if it is in UNUSABLE status.

Author

Commented:
can you append "update indexes" to the end of the truncate to rebuild them?

alter table t_part truncate partition q12009 update indexes;

Also, sorry to not know but..is "q12009" the value within the partitioned key?
Top Expert 2009
Commented:
q12009 is an example. It is the name of the partition that you want to truncate. Don't  you know your partition names?

You can add "UPDATE GLOBAL INDEXES" to the end of the ALTER TABLE


alter table t_part truncate partition q12009 update global indexes;


That will rebuild, yes. But if you are truncating multiple partitions, I would not add the syntax or at least add it on the last truncate command.

Top Expert 2009

Commented:
You can find the partitions for a table by querying user_tab_partitions

Author

Commented:
thanks...like I said I'm new and NO I don't know the names yet, since I have not created a partitioned table yet.
I'm first doing research. Sounds like what you are saying is each value within the partitioned column becomes the partition name.

Author

Commented:
ok, got it...thanks for the info mrjoltcola!

Top Expert 2009

Commented:
Welcome!
Top Expert 2009

Commented:
>>Sounds like what you are saying is each value within the partitioned column becomes the partition name.

No, each range of values goes according to your initial parition rules in the create table statement, or based on adding partitions. Why not open a question about partitioning tables and I'll work with you on it. I can round up some samples for you.
Top Expert 2009

Commented:
Remember, too, partitioning is an Enterprise only feature, and is separately licensed even for Enterprise Edition.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial