Simple Oracle Partition Table and partition Index

 

In Oracle, partitioned tables are stored in segments. Common tables are stored in one segment, while partitioned tables are divided into multiple segments, therefore, the process of searching for data is to first locate the partition range based on the query conditions, that is, the data is located in the partition or the interior, and then search for data within the partition, A partition generally ensures that more than 0.4 million pieces of data are normal, but the partition table is not created in disorder, and its maintainability is relatively more complicated, and the index creation is also a bit exquisite, please elaborate on these below.

1. type description:

The range partitioning method is also the most common partitioning method. It uses a combination of a field or several fields to partition from small to large according to the specified range description, we store the insert data in the specified partition.

List partitioning is generally based on a range with more list partitions. List partitioning is a way to list partitions. Generally, some regions, states, or encoding of specified rules are divided.

Hash partitioning method, which has no fixed rules. It is managed by Oracle and only needs to insert values. Oracle will automatically partition partitions based on a set of hash algorithms, you only need to tell Oracle that there are several partitions.

Partitions can be combined in pairs. Before the Oracle 11g, the combination must start with range as the primary partition. Oracle currently supports a maximum of two levels of partitions, but this level is enough for us to use.

Here, I only use the simplest partition method to create a partition to illustrate the problem. Let's take the range partition to illustrate the problem (the basic creation statement is as follows ):

Create Table table_partition (
Col1 number,
Col2 varchar2 (10)
)
Partition by range (col1 )(
Partition tab_partotion_01 values less than (450000 ),
Partition tab_partotion_02 valueless than (900000 ),
Partition tab_partotion_03 valueless than (1350000 ),
Partition tab_partotion_04 valueless than (1800000 ),
Partition tab_partotion_other values less than (maxvalue)
);

This partition table creates four fixed-length partitions. Ideally, it stores 450000 pieces of data, and the extended partition is a partition that exceeds this amount. When we find that the extended partition has data, you can perform the split operation on the extended partitions. Here we will introduce some common Partition Table query functions. We will insert some data first.

Insert into table_partition (col1, col2)
Values (1, 'Data test ');
Insert into table_partition (col1, col2)
Values (23, 'Data test ');
Insert into table_partition (col1, col2)
Values (449000, 'Data test ');

Insert into table_partition (col1, col2)
Values (450000, 'Data test ');

Insert into table_partition (col1, col2)
Values (1350000, 'Data test ');

Insert into table_partition (col1, col2)
Values (900000, 'Data test ');

Insert into table_partition (col1, col2)
Values (1800000-1, 'Data test ');

Commit;

To check which partitions have data queried by partition (this is not used in application development because partitions are not written to death)

SQL> select * From table_partition partition (tab_partotion_01 );

Col1 col2
-------------------------
1 Data Test
23 Data Testing
449000 Data Testing

Note that the first partition contains data such as 1, 23, and 44900, that is, Oracle finds the partition by itself during insertion, in fact, the sub-table management of partitions can also be completed by the program. Oracle provides you with a set of sub-tables, you can complete it by yourself. The rest of the data is queried by yourself.

2. partition applications:

Generally, a table is larger than 2 GB in size. We recommend that you use a partition table in Oracle. You need to create an index for all partitions. When it comes to partition indexes, it can be divided into global indexes and partition indexes, that is: global index and Local index. The former is the index method used to create an index in a partitioned table by default, and does not partition the index (the index is also a table structure, and partitions are also required when the index is large, the global index can be modified as a partition index, but it is different from the local index. The partition method of the former is completely created according to the custom method and has nothing to do with the table structure, therefore, the global indexes of Partitioned Tables are shown in the following two commonly used diagrams on the Internet:

2.1 Non-partitioned indexes for Partitioned Tables (this is a bit of a wrap, but it is a table partition, but its index is not partitioned ):

Create a syntax (directly create a syntax ):

Create index <index_name> On <partition_table_name> (<column_name> );

2.2. Partition indexes for partitioned tables:

Syntax:

Create index inx_tab_partition_col1 on table_partition (col1)
Global partition by range (col1 )(
Partition idx_p1 values less than (1000000 ),
Partition idx_p2 values less than (2000000 ),
Partition idx_p3 values less than (maxvalue)
);

2.3 Local index structure:

Syntax:

Create index inx_tab_partition_col1 on table_partition (col1) local;

You can also define the partition according to the partition structure of the partition table. The index partition will be renamed.

The bitmap index on a partition can only be a local index, not a global index.

2.4. Index comparison methods:

Generally, local indexes are easier to use and have a low maintenance cost. In addition, local indexes are created based on partitions, similar to creating indexes within a sub-table, in this way, the overhead is mainly divided into partitions, which are well managed and widely used in OLAP systems. The relative global index is a global index, you can adjust the category of a partition based on the actual situation, instead of defining the partition structure one by one. The maintenance cost is higher, and more is used in the OLTP environment, here the so-called OLTP and OLAP are also relative, not special projects, there is no absolute division concept, in the application process based on the actual situation, to improve the overall operational performance.

3. Common views:

1. query the partition tables of the current user:

Select * From user_part_tables;

2. query the partition indexes of the current user:

Select * From user_part_indexes;

3. query the partition information of the current user's partition index:

Select * From user_ind_partitions t
Where T. index_name =?

4. query the partition information of the current user's partition table:

Select * From user_tab_partitions t
Where T. table_name = ?;

5. query the data volume in a partition:

Select count (*) from table_partition partition (tab_partotion_01 );

6. query indexes and create partitions on the columns of tables:

Select * From user_part_key_columns;

7. query the information of a subpartition under a user (data is available only when a subpartition is created ):

Select * From user_tab_subpartitions;

4. Maintenance operations:

4.1 delete partitions

Alter table table_partition drop partition tab_partotion_03;

If it is a global index, because the partition structure of the global index and the table can be inconsistent, otherwise, the entire global index will fail. When deleting the partition, statement:

Alter table table_partition drop partition tab_partotion_03 update global indexes;

4.2 partition Merge (delete a partition from the middle, or merge the two partitions to reduce the number of partitions)

The range in the process of merging and deleting partitions is a bit like that, But merging partitions does not delete data. For list and hash partitions, they are also different from range partitions. The syntax is as follows:

Alter table table_partition merge partitions tab_partotion_01, tab_partotion_02 into partition merged_partition;

4.3. Partition separation (generally, partitions are separated from extended partitions)

Alter table table_partition split partition tab_partotion_othere at (2500000)
Into (partition tab_partotion_05, partition tab_partotion_othere );

4.4 create a new partition (if the partition data cannot provide a range, an error will be reported during insertion, and you need to add partitions to expand the range)

Generally, extended partitions are separated. If the tab_partotion_other partition is not created during table creation, when the data is large (according to the preceding rule, if the number exceeds 1800000, a new partition should be created for storage.) You can create a new partition, for example:

In order to test, we delete the extended partitions and then create new partitions (due to Oracle requirements, data in partitions cannot overlap, that is, data with the same partition field cannot be stored in different partitions at the same time ):

Alter table table_partition drop partition tab_partotion_other;

Alter table table_partition add partition tab_partotion_06 values less than (2500000 );

Create a new subpartition under the partition as follows (range partition, if it is a list or hash partition, you can change the creation method to the corresponding method ):

Alter table <table_name> modify partition <partition_name> Add subpartition <user_define_subpartition_name> values less (....);

4.5 modify partition name (modify relevant attribute information)

Alter table table_partition rename partition merged_partition to merged_partition02;

4.6. Swap partitions (fast data exchange is actually a switch segment name pointer)

First, create an exchange table with the same structure as the original table. If there is data, it must meet the conditions for the corresponding partition to be exchanged:

Create Table table_partition_2
As select * From table_partition where 1 = 2;

Then swap the data in the first partition:

Alter table table_partition exchange partition tab_partotion_01
With table table_partition_2 including indexes;

At this time, we will find that the data in the first partition is instantly exchanged with table_partition_2, which is faster than the truncate, because this process does not perform data transfer, but only modifies the segment name, it is irrelevant to the actual data volume.

If it is a subpartition, it can also be exchanged with external tables. You only need to change the keyword to subpartition.

4.7 clear partition data

Alter table <table_name> truncate partition <partition_name>;

Alter table <table_name> truncate subpartition <subpartition_name>;

9. Disk fragment Compression

Disk compression is performed on a partition in the Partition Table. After a large number of update and delete operations are performed on the internal data of the partition, disk compression is required for a certain period of time. Otherwise, during query, if you scan data through full scan, empty blocks will also be scanned. to compress the disk on the table, you need to perform row migration. Therefore, you must first perform the following operations:

Alter table <table_name> enable row movement;

The syntax for compressing a partition in a partitioned table is:

Alter table <table_name>

Modify partition <partition_name> shrink space;

Common table compression:

Alter table <table_name> shrink space;

The indexes also need to be compressed, and the indexes are also tables:

Alter index <index_name> shrink space;

10. re-analyze the partition table and re-analyze the index

After the table is compressed, you need to re-analyze the table and index to re-analyze the table. There are two methods:

Before Oracle 10 Gb, use:

Begin
Dbms_stats.gather_table_stats (user, upper ('<table_name> '));
End;

After 10 Gb of Oracle, you can use:

Analyze table <table_name> compute statistics;

Re-analyze the index and modify the two methods respectively. For example, you can use gather_index_stats In the first method, and change the second method to analyze index. However, recompile is usually used:

When partitioned tables are indexed and partitioned, you need to re-compile the indexes of each partition. Here, we use the local index as an example (the partition of each index is the same as that of the table partition, the default partition name is the same as the table partition name ):

Alter index <index_name> rebuild partition <partition_name>;

For a global index, you can modify it according to the partition name locked by the global index. If no partition exists, the recompilation method is the same as that for a common single-Table index:

Alter index <index_name> rebuild;

11. recompile the associated object

The above table and index are re-compiled, especially after the table is compressed, row migration will be generated. This process may cause some views and process objects to become invalid. At this time, re-compile it.

12. Expansion: if a new partition is created in the hash partition, you can re-hash it:

Alter table <table_name> coalesca Partition

5. Regression Summary: When to create a partition, partition category, index, and how to correspond to SQL

1. Creation Time

As described above, we recommend that you create partitions for tables larger than 2 GB.

The partitioning method depends on the actual situation to improve the overall performance.

Partition fields must be frequently used to extract data. Otherwise, multiple partitions will be traversed during the extraction process, which is slower than no partition.

Select an appropriate partition field. The data is evenly distributed to each partition. The partition range can be quickly located based on the partition field.

Generally, business operations should be completed within the same partition.

2. Partition category

Partitions mainly include range, list, and hash;

Range is partitioned by the range of values, which is also the most commonly used partition. Note that in a variable-length numeric string, many people will think of it as a number type and distinguish it by number, this will cause uneven distribution.

List is used to list partitions. It generally exists as a list partition (of course, it can also be used as a primary partition after 11 GB of Oracle). Based on the range, if the data needs to continue partitioning and the Data Volume Based on the range is relatively fixed, but it is large, further partitioning can be performed according to certain rules.

Hash only specifies the number of partitions. The partition details are completed by Oracle. You can re-distribute data by adding hash partitions.

Note: partition fields cannot be partitioned after Function Conversion. For example, to_numer (col_name) is used to partition a numeric string field.

3. index category

General score: global index and Local index, Qian he can be divided into: Global Non-partition index, and global partition index.

Global non-partition indexes are generally not recommended because a large index tree is used to map a table. This process is not faster than non-partition indexes.

Global partition index: If you want to use the index to search for data, first locate the internal partition of the index, then find the rowid in the index, and then return to the table to extract data.

The local index corresponds to the number of partitions one by one. It can be said that the partition in the Partition Table is located first, or the index partition is located first, because they correspond one to one. After finding the corresponding partition, A collection of index data within a partition.

4. Corresponding applications

When a partition table, index, or partition index needs to take advantage of its performance advantages, the most basic thing is to extract data, it is necessary to first narrow the data scope to a situation where it will not be too slow even if a full scan is performed.

Therefore, SQL must have a where condition for this field on the partition to quickly locate the data inside the partition and try to locate it in a partition (this is related to the partition creation rule ).

Creating a partition itself does not have the feed performance. Therefore, it is necessary to make good use of it to improve the performance. In the necessary RAC cluster, if multiple partitions are used to extract data, appropriate parallel extraction can improve the extraction speed.

For the index part, we only mention how to create a partition index and how to maintain a common index. After understanding the index principle, we will be more likely to recognize the techniques used to extract data.