Introduction to partition truncate operations and analysis of the impact on global indexes and space release

Environment: [SQL] [oracle @ localhost ~] $ Uname-r2.6.18-308. el5xen [oracle @ localhost ~] $ Sqlplus-v SQL * Plus: Release 10.2.0.1.0-Production
(I) syntax www.2cto.com example:
① Reclaim space immediately:
Alter table table_name truncate partition partition_name drop storage;
② Maintain global indexes at the same time:
Alter table table_name drop partition partition_name update global indexes; (2) effect on global indexes
After the partition Table truncate partition, you need to maintain the global index. Otherwise, the global index will become unusable.
Problem description:
① When dropping partition, the update indexes or update global indexes condition must be added to maintain the global index.
Have you studied the differences between the two conditions?
A: update global indexes only maintains global indexes.
Update indexes maintains both global and local INDEXES.
There is no big difference between DROP/TRUNCATE PARTITION
For MERGE and split partition, you can see the difference between the two.
Although the index becomes valid, the space of the index is not released.
Because this operation is not equal to REBUILD, it is only necessary to synchronize and maintain index information during DDL operations.
Www.2cto.com
Introduction to industrial environments:
② Today, I have TRUNCATE a partition in the partition table. This partition has a common unique index and a local index,
When TRUNCATRE is run, the update global indexes command is not used, and the result is:
ORA-01502: index 'bill. IDX_CHARGE_D_591_0712_SID 'or partition of such index is in unusable state
This is because the truncate partition does not contain update global indexes, which causes the global index to fail (unusable ).
Then I had:
Alter index bill. idx_charge_d_591090712_sid parallel 10 nologging rebuild to rebuild the whole, 1.3 billion large table records
Later, when someone continued to insert this table at night, he told me that it was too slow. at least 4 million records could have been run in an hour. Now it's only 3 hours before it's 1.3 million.
I immediately thought about whether it would be a local index problem, because I heard that although partition exchange or TRUNCATE has no effect on local indexes,
But there is actually a problem, but it is better to reconstruct it:
Alter index bill. unq_rrating_charge_d_591090712 rebuild partition PART_20
Re-create the local index involved in the partition I just TRUNCATE
The results immediately showed the effect. 2 million records were run in 10 minutes, and 6 million records ran well in 20 minutes! Faster than running in the same period

Called to work in the middle of the night
Strange: Why is it hard to execute the following statements for half a day?
Alter table bill. recur_rating_charge_d_5920.0712 truncate partition PART_21 update global indexes;

Select count (*) from bill. recur_rating_charge_d_591_0712 partition (PART_21)
Data remains unchanged
But I think v $ session_longops will soon be able to see this SID,
In my opinion, the table partition record is always in
I was dizzy, so I had to use the old method. After killing the session,
Alter table bill. recur_rating_charge_d_5920.0712 truncate partition PART_20 without update global indexes
Then the common index and Local index are maintained separately. This addition of NOLOGGING and PARALLEL 8 is also very fast. It took only 0.3 billion seconds to maintain the common index for 200 of large tables.
Alter index bill. idx_charge_d_5920.0712_sid rebuild parallel 8 nologging;
Alter index bill. unq_rrating_charge_d_591090712 rebuild partition PART_21 parallel 8 nologging;
Cause of speculation:
After truncate partition PART_20, the statistics of the partition and the local index on the partition will not be updated or lost.
When I insert data into this partition, the execution plan is generated based on the wrong statistics, so it will be slow.
When I rebuild index partition PART_20, the statistics of this index will be lost,
And my execution plan may change, so my insertion is faster.

Conclusion: www.2cto.com
After truncate, you should analyze the partition immediately. cascade => true (add statistics on the index ),
At the same time, rebuild the global index and analyze the global index.

(Iii) Space release Problems
In fact, the space and so on have been released, but the data dictionary has not been updated,
For example, you can check the dba_segments view and find that the bytes of this partition is actually the original size.
We can execute alter table *** allocate extent to update the data dictionary to a normal state.
For example, perform the following operations on the range partition:
Alter table *** modify partition PART _ *** allocate extent;