Partition Table and partition index 2 (further discussion)

Partition Application

Generally, a table is larger than 2 GB in size. We recommend that you use partitioned tables in Oracle. Generally, you need to create an index for a partition. When it comes to a partition index, it can be divided into global index and partition index, that is, global index and Local index. The former 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 (I think it is the partition index of the partition table)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 index of the partition table has the following two commonly used diagrams on the Internet:

1.1 non-partitioned indexes for Partitioned Tables (this is a bit round, but it is a table partition, but its index is not partitioned ):

Create syntax (directly create): Create index <index_name> On <partition_table_name> (<column_name> );

1.2 partition indexes for partitioned tables:

Syntax:

 1   Create    Index Inx_tab_partition_col1 On  Table_partition (col1)  2 Global Partition By  Range (col1)  3 Partition idx_p1 Values Less ( 1000000  ),  4 Partition idx_p2 Values Less ( 2000000  ),  5 Partition idx_p3 Values  Less than (maxvalue) 6 )

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

Index Comparison Method

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.

When to create a partition, partition category, Index:

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.

In general, do your best to make business operations 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. The former 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. To search for data, you must first locate the internal partition of the index, 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.

Common View
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;

Maintenance Operations (For details, refer to the Oracle manual):
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;

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

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

4. Create a New partition (if the partition data cannot provide a range, an error will be reported during insertion. 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, the data is inserted when it is large (according to the preceding rule, more than 1800000
You can create a new partition.

5. Modify the partition name (modify relevant attribute information ):

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

7. Clear partition data

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

9. 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 the first method: gather_index_stats, and change the second method to analyze index.
Is to recompile. 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>;

You can modify the global index according to the partition name locked by the global index. If no partition exists, the recompilation method is the same as that of the normal single-Table index: Alter index <index_name> rebuild;

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

11. Extension: if a new partition is created in the hash partition, you can re-hash it: alter table <table_name> coalesca partition %