Oracle Partition Table (partition)

 

When looking at the example library, we found that some table DDL not only has the column part, but also has a lot of partition parts. I don't understand what it means. So I searched and found that it was a feature of oracle. The following are references: (you can combine some table learning in the sh in the example Library)

Oracle provides Partitioning technology to support vldb (very large database ). The partition table judges the partition columns to store different records in different partitions. Partitions are completely transparent to applications.

Oracle partition tables can contain multiple partitions. Each partition is an independent segment and can be stored in different tablespaces. You can use a query table to access data in each partition, or you can specify a partition for query.

Partitions provide the following advantages:

Data is distributed to various partitions, which reduces the possibility of data corruption;

You can back up and restore individual partitions;

Partitions can be mapped to different physical disks to distribute Io;

Improves manageability, availability, and performance.

Oracle provides the following Partition types:

Range partition (range );

Hash partition (hash );

List partition (list );

Range-Hash composite partition (range-hash );

Range-list composite partition (range-list ).

Indexes can also be partitioned. There are two types of partition indexes: global and local. For a local index, each table partition corresponds to an index partition. When the table partition changes, the index maintenance is automatically performed by Oracle. For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the global index is usually invalded and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to re-create a global index while maintaining partitions.

Global indexes can contain values of multiple partitions. Local indexes are easier to manage than global indexes, while global indexes are faster.
Note: you cannot create global indexes for hash partitions or subpartitions.

If the expected data volume in the table is large, you usually need to consider using a partition table. After you determine the Partition Table type, you need to determine the partition type (range partition, hash partition, list partition, and so on) partition interval size. It is best to create a partitionProgramSome kind of tacit understanding is that I used to create a partition table and define partitions according to the natural month, but the default start time and end time of the program during query are: the current date-30 to the current date, for example, if the date is 9.18, the query condition is 8.18-9.18. After the result is partitioned, the performance is not significantly improved. Later, the query date of the program is adjusted to query by calendar month, the system load is much smaller.

Table partitions are supported from oracle8.0 (Table partitions are supported from mssql2005 ).

The Oracle9i partition improves the manageability, performance, and availability of many applications. Partitions further divide tables, indexes, and index orchestration tables, allowing you to manage and access these database objects more precisely. Oracle provides a wide range of partitioning solutions to meet all business needs. In addition, because SQL statements are completely transparent, partitions can be used in almost all applications.

A partition table allows you to divide data into smaller, better-managed blocks called partitions or even subpartitions. Indexes can also be partitioned in this way. Each partition can be managed independently and can be used independently without relying on other partitions. Therefore, a structure that is more conducive to availability and performance is provided.

Partitioning improves manageability, performance, and availability, and brings great benefits to a variety of applications. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitions can simplify daily management tasks. Partitioning also enables database designers and administrators to solve the most difficult problems caused by cutting-edge applications. Partitioning is a key tool for establishing a data system with hundreds of millions of bytes or requiring extremely high availability.

In a multi-CPU configuration environment, if you plan to use parallel execution, partitions provide another parallel method. By allocating different parallel execution servers to different partitions of a table or index, you can perform operations on partition tables and partition indexes in parallel.

Table or index partitions and subpartitions share the same logical attributes. For example, all partitions or subpartitions in a table share the same column and constraint definitions, and one index partition or subpartition shares the same index option. However, they can have different physical attributes, such as tablespace.

Although you do not need to place each partition or sub-partition of a table or index in a different tablespace, it is better to do so. You can store partitions in different tablespaces.

L reduce the possibility of data conflicts in multiple partitions

L independent backup and recovery of each partition

L controlling the ing between partitions and disk drives is important for balancing I/O Load

L improves manageability availability and performance

Partition operations are transparent to existing applications and standard DML statements running on partitioned tables. However, you can program applications by using the name of the partition extension table or index in DML to make use of the advantages of partitions.

You can use SQL * loader, import, and export tools to load or unload data in a partition table. These tools support both partitions and subpartitions.

Partition Method Oracle9i provides the following five partition methods:

L range partition range

L hash of hash partitions

L list partition list

L combination range-hash-range-Hash

L combination range-list partition range-list

You can partition indexes and tables. Global indexes can only be partitioned by range, but can be defined on any type of partition or non-partition table. Generally, global indexes require more maintenance than local indexes.

Generally, a local index is created to reflect the structure of the basic table. It is equivalent to the basic table, that is, it is equivalent to the basic table.

Create the same number of partitions or subpartitions for the table in the same column and set the same partition boundary as the basic table. For local indexes, index partitions are automatically maintained when maintenance activities affect partitions. This ensures the equivalent partition between the index and the basic table.

Range of range partitions:

To map rows to a partition based on the column value range, use the range partition method. This type of partition is useful when data can be divided into logical ranges, such as the month in the year. The best performance is when data can be evenly divided across a range. If the partition by range is significantly different in size due to uneven division, you need to consider other partitioning methods.

Hash for hash partitions:

If the data is not so easy to partition by range, but you want to partition for performance and management reasons, use the hash partitioning method. Hash partitions provide a way to partition data in all specified partitions. The hash value based on the partition Key Maps rows to the partition. Creating and using hash partitions provides you with a flexible way to place data, because you can broadcast (remove) between I/O drives) these equally quantitative partitions affect availability and performance.

List partition list:

When you need to explicitly control how to map rows to partitions, use the list partition method. You can specify a discrete value for the partition column in the description of each partition. This is different from the range partition, where a range is related to a partition, which is different from the hash partition, where the user cannot control how to map rows to partitions. The list partitioning method is specially designed to partition discrete values based on modular data. Range partitions or hash partitions are not easy to achieve. Further, list partitions can naturally group and organize unordered and irrelevant datasets.

Unlike range partitions and hash partitions, list partitions do not support multiple column partitions. If you want to partition a table by column, the partition key can only be composed of a single column in the table. However, you can use the range partition or hash partition method to partition all columns, you can use the list partition method to partition.

About the combination range-Hash partition:

The combination of range and Hash technology first partitions the table range, and then uses the Hash technology to partition each range partition again. All subpartitions of a given range partition are combined to represent the logical subsets of data.

About the combination range-list partition:

The combination of range and list technology first partitions the table range, and then uses list technology to partition each range partition again. Unlike the combined range-Hash partition, all content of each subpartition represents the logical subset of data, which is described by the appropriate range and list partition settings.

When creating or changing a partition table, you can specify a row moving clause, that is, enable row movement or disable row movement. When its key is changed, this clause enables or disables migration of rows to a new partition. The default value is disable row movement. This product (Project) uses the Enable row movement clause.

Partitioning technology can improve the manageability of databases:

Using partition technology, maintenance operations can be concentrated on specific parts of the table. For example, a database administrator can back up only a part of a table without backing up the entire table. Maintenance operations on the entire database object can be performed on the basis of each partition, so as to break down the maintenance work into smaller pieces that are easier to manage.

A typical use of partition technology to improve manageability is to support 'rolling Windows' loading processes in data warehouses. Assume that the database administrator loads new data into the table every week. This table can be a range partition, so that each partition contains data for one week. Loading a process simply adds a new partition. Adding a new partition is much more efficient than modifying the entire table, because the Database Administrator does not need to modify any other partitions. The same is true for removing data from a partitioned table. You only need to delete a partition with a simple and quick data dictionary operation, instead of issuing the 'delete' command to use a large amount of resources and mobilize all the data to be deleted.

Partitioning technology can improve database performance:

The partition function of Oracle9i provides performance advantages because it reduces the number of data checked or operated and allows parallel execution. These performances include:

L partition trimming: partition trimming is the simplest and most valuable way to improve performance with Partitioning technology. Partition trimming often improves query performance by several orders of magnitude. For example, assume that the application contains an order table with order history, and the table is partitioned by week. To query orders for one week, you only need to access one partition of the Order table. If the order table contains two years of history, this query only needs to access one instead of one hundred and four partitions. The query speed may be one hundred times faster due to partition trimming. Partition trimming can work with all other Oracle performance features. Oracle will combine partition trimming technology with indexing technology, linking technology, and parallel access methods.

L partition smart join: The partition function can improve the performance of Multi-table join by using the technology called partition smart join. When two tables need to be joined together and each table uses the join keyword for partitioning, you can use smart partitioning. Partition smart connections break down large connections into smaller connections in different intervals to complete all connections in less time. This significantly improves the performance of both serial and parallel execution.

L parallel execution of update and deletion: The partition function can execute the update, delete, and merge statements infinitely in parallel. When accessing partitioned or unpartitioned database objects, Oracle processes select and insert statements in parallel. When Bitmap indexes are not used, you can also process update, delete, and merge statements in parallel for database objects that are partitioned or not partitioned. To process objects with bitmap indexes in parallel, the target table must be partitioned first. The parallel execution of these SQL statements can greatly improve the performance, especially when the update, delete, or merge operations involve a large amount of data.

Partition technology improves availability:

Partitioned database objects are partition independent. The independence of the partition may be an important part of the high availability strategy. For example, if the partition of the partition table is unavailable, all other partitions of the table remain online and available. The application can continue to perform queries and transaction processing on the partition table. As long as the partition is not accessed, the database operation can still run successfully. The database administrator can specify that each partition is stored in a different tablespace, so that the administrator can back up and restore each partition independently of other table partitions. In addition, the partition function can reduce the scheduled downtime. As the partition function is improved, the database administrator can maintain large database objects in a relatively small batch processing window.

How many partitions Does Oracle Support?

We canConcepts ManualFor oracle9ir2:

Tables can be partitioned into up64,000Separate partitions.

Oracle enhances the partition feature for oracle10gr2:

Tables can be partitioned into up1024k-1Separate partitions.

Oracle provides the following suggestions on when to partition:

■ tables greater than 2 GB shoshould always be considered for partitioning.
■ tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.