Shulou(Shulou.com)06/01 Report--

The regular operation of oracle partitioned tables leads to the impact on the index

Oracle partition table has been widely used in our production system, but in the daily need to maintain the partition table, some of the basic operations of the partition table, we will inevitably worry about whether the index on the partition table is invalid, so today I will take you to see which operations will lead to the index failure on the partition table.

In order to control the length, this experiment is only aimed at the RANGE partition. Please test the other two zones by yourself.

1. Classification of partitioned table indexes

Anyone familiar with partitioned tables should know that there are two types of indexes for oracle partitioned tables, one is Local index, which is also called local index, and the other is Global index, which is also called global index. This experiment does not specifically introduce the differences between the two indexes in terms of performance problems caused by partitioned table access, but only introduces some effects of some common DDL operations on indexes on partitioned tables.

If you want to know the performance difference between the two indexes, please look forward to sharing in the next article.

2. Perform various DDL operations on partitioned tables to view the impact on indexes 2.1 create partitioned tables, and create global indexes and partitioned indexes

Createtable tmp_test_range

(

Idnumber

Id_local number

Namevarchar2 (30)

Int_date date

Bz varchar2 (20)

)

PARTITIONBYRANGE (int_date)

-- interval (numtodsinterval (1)

(

Partition P201511 valueslessthan (to_date ('201512018)

Partition P20151201 valueslessthan (to_date ('20151202)

Partition P20151203 valueslessthan (to_date ('20151203 writing journal yyyymmdd`))

Partition P20151204 valueslessthan (to_date ('20151204 recording journal yyyymmdd`))

Partition P20151205 valueslessthan (to_date ('20151205 writing journal yyyymmdd`)

Partition P20151206 valueslessthan (to_date ('20151206 writing journal yyyymmdd`))

Partition P20151207 valueslessthan (to_date ('20151207 recording journal yyyymmdd`)

);

Insert data into the partition table:

Declare

V_date date:= to_date ('20151127mm Murdd`)

Begin

For c in1.. 9loop

For d in1.. 100loop

Insertinto tmp_test_range values (c | | dMagnec | | dMagnec 'test data', vSecretdaterecoveryBZ')

Endloop

V_date: = v_date + 1

Endloop

Commit

End

Create an index:

Create a global index and a partitioned index in ID,ID_LOCAL and

Createindex I_TMP_TEST_RANGE_G on tmp_test_range (id) nologging

Createindex I_TMP_TEST_RANGE_L on tmp_test_range (id_local) nologgingLOCAL

View the status of the index:

Local Index:

Global Index:

2.2 impact of DDL operations on global indexes:

Well, now that the previous basic work is ready, let's start to do some DDL operations to see the impact on the global index.

Add partitions and rename one of them:

ALTERTABLE tmp_test_range ADDPARTITION P20151208 valueslessthan (to_date ('20151208 writing journal yyyymmdd`))

ALTERTABLE tmp_test_range RENAMEPARTITION P20151207 TO P20151207_2

Check the index:

Global

Local Index:

Summary: adding and renaming partitions does not invalidate Global and local indexes.

Delete the partition in the table:

ALTERTABLE tmp_test_range DROPPARTITION P20151208

Clear the data in one of the partitions:

ALTERTABLE tmp_test_range TRUNCATEPARTITION P201511

Check the index:

The local index deletes the local index on the deleted partition and does not affect the indexes of other partitions.

Global Index:

OK, after the above experiment, you may easily come to the conclusion that deleting a partition will not invalidate the Global index, but it is not, let's look at another situation:

Look at the data of a partition (P201511) and see that the following figure shows that there is data:

Now delete the partition:

Altertable tmp_test_range droppartition P201511

Or:

ALTERTABLE tmp_test_range TRUNCATEPARTITION P201518

View the Global index:

Alas, it still fails, so when deleting a partition in the partition table, be sure to make sure that any data exists.

Summary:

When deleting the partition in the partition table or the data in the truncate partition, be sure to make sure that there is data in the partition. If there is no data, it will not lead to Global invalidation, otherwise it will cause the Global index to fail. There is no impact on local indexes on other partitions.

Merge partitions:

There are two ways to merge partitions, one is to maintain the index, and the other is not to maintain the index. Let's first look at whether the index is maintained or not.

ALTERTABLE tmp_test_range MERGEPARTITIONS P201511,P20151201 INTOPARTITION P20151208

View the index:

Summary: Global index, do merge partition operation will lead to Global index failure, so you must be careful when operating oh!

The local index does not maintain the merged partition, but it does not affect other partitions, and the merged partition operation deletes the partition that was originally merged.

Of course, oracle also provides the operation of maintaining the index when merging partitions. When you operate online, please use the following statement to merge partitions.

ALTERTABLE tmp_test_range MERGEPARTITIONS P201511,P20151201 INTOPARTITION P20151208 update indexes

In this way, the index will not be invalidated when the merge partition operation is done.

Split the partition:

There are also two ways to split partitions, one is to split directly without maintaining the index, and the other is to split the index with maintenance. For specific operations, see the following experiment:

Let's first look at the way to maintain the index split:

ALTERTABLE tmp_test_range SPLITPARTITION P201511 at (date'2015-11-28') INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE) updateindexes

View the index:

Sure enough, it is normal to split the Global index and the local index using update indexes. So let's take a look at the ways in which indexes are not maintained:

ALTERTABLE tmp_test_range SPLITPARTITION P201511 at (date'2015-11-28')

INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE)

It still doesn't disappoint us. Using this direct split, both indexes fail.

The index of the local index failed on the two new partitions, and the Global index failed as expected.

Summary:

If you do not add the parameter update indexes to the partition table, the local index on the new partition will be invalidated and the Global index will fail. Using update indexes, neither of these indexes will be invalidated.

Swap Partition:

There are also two ways to swap partitions, one is to maintain the index automatically, and the other is to swap the partition directly without maintaining the index. obviously, the second way will lead to index failure. For details, see the following experiment (do not operate the following two at the same time):

ALTERTABLE tmp_test_range EXCHANGEPARTITION P201511 WITHTABLE tmp_test_range_2

ALTERTABLE tmp_test_range EXCHANGEPARTITION P201511 WITHTABLE tmp_test_range_2 updateINDEXES

View the two indexes:

Sure enough, swapping partitions can still cause Global indexes to fail, and local indexes to invalidate indexes on swapped partitions. So when doing swap partitions, we can still use update indexes to maintain the index Global index, but for the local index, even using update indexes will cause the local index to fail, so we still have to re-maintain it.

3. Summary:

Well, after all we've said above, let's summarize which actions will have an impact on the indexes on the partitioned table.

Operations that have no impact on either index:

1. Add a partition

two。 Delete partition (no data in partition)

3. Rename a partition

It will have an impact:

1. Merge Partition

The local index on the new partition is invalid

Global index invalidation

PS: using update indexes can avoid index failures (including local and Global indexes).

2. Split the partition

The local index on the split partition is invalid

Global index invalidation

PS: using update indexes can avoid index failures (including local and Global indexes).

3. Partition switching

The local index on the swapped partition is invalid

Global index invalidation

PS: the use of update indexes can avoid the occurrence of Global index failure, can not avoid local index failure.

4. When deleting data in a partition or truncate partition, if there is data on the deleted partition, it will cause the Global index to become invalid.

Through the above experiments, we can see that for partitioned tables, regardless of performance, it is more convenient to manage the data in partitioned tables only for the convenience of data management, or to create local indexes.

Author: Feng Donghua

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.