Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand the Oracle partition table, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

One: what is Partition?

Partitioning is the physical decomposition of a table or index into smaller, more manageable parts.

Partitioning is transparent to the application, that is, for applications that access the database, logically there is only one table or index (equivalent to only one table or index that the application "sees"), but physically this table or index may consist of dozens of physical partitions.

Each partition is a separate object that can be handled alone or as part of a larger object.

-- Tips: sub-table and partition table--

A sub-table is to decompose a large table into multiple entity tables (child tables) with independent storage space according to certain rules.

For example, an order table ORDER, after adopting the year and month table, may generate many things such as ORDER_201601, ORDER_201602, ORDER_201603 in addition to ORDER itself. Wait for the child table.

A sub-table is logically multiple different tables, while a partitioned table is logically one table.

Two: when do you need zoning?

Two suggestions from the official website:

1. Tables greater than 2GB should always be considered for partitioning. (partitioning should be considered when the amount of data in the table is greater than 2GB)

2. 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. (new data is added to the table used to store historical data in the latest partition)

Three: the benefits of zoning

1. Improve data availability

A) thanks to the independence of each partition, the optimizer removes unused partitions when querying (this is also called eliminating partitions)

For example, if a query only uses data from one of the three partitions of a table, then Oracle will only scan the data of that partition when executing the query, not the data of the other two partitions.

This is useful in OLAP systems.

-extended Reading: OLTP and OLAP systems

OLTP (On-Line Transaction Processing):

The basic feature of the online transaction processing process, also known as the transaction-oriented processing process, is that the user data received by the foreground can be immediately transmitted to the computing center for processing, and the processing results can be given in a very short time to achieve a fast response to user operations.

The transactional requirements of such systems are very high, and they are generally highly available online systems, mainly small transactions and small queries. When evaluating its system, it is generally based on the number of Transaction and Execute SQL executed per second. A single database often processes more than hundreds or thousands of Transaction per second, and the execution of Select statements is thousands or even tens of thousands per second.

OLTP is the main application of traditional relational database. Typical OLTP systems include e-commerce system, bank system, securities system and so on.

OLAP (On-Line Analytical Processing):

Online analytical processing is the main application of data warehouse system. The so-called data warehouse is an analytical database for a large amount of data already formed by OLTP, which is used to deal with important decision information such as business intelligence, decision support and so on.

After the database is applied to a certain extent, the data warehouse processes and analyzes the historical data, reading more and updating less.

B) Partition can also improve availability by reducing downtime

For example, if the data in the middle of a 100GB table is corrupted, it will be maddening to recover.

If the 100GB table is divided into 50 2GB partitions, when one of the partition data is corrupted, only one 2GB partition data needs to be restored.

Downtime in the event of an error will be greatly reduced because of the significant reduction in the amount of work required for recovery.

two。 Easy to manage

Decomposing a large object into several small objects, it is obviously easier to manipulate these small objects than to directly manipulate the original large objects, and takes up less resources.

3. Improve statement performance (mostly for OLAP systems)

A) parallel DML (Parallel DML):

In previous versions of Oracle 9i, PDML (Parallel DML) required partitioning

This restriction has been relaxed in 9i and later versions, with two exceptions:

① wants to execute PDML on a table, and the table has a bitmap index on a LOB column. To perform operations in parallel, the table must be partitioned.

② takes the number of partitions that need to be accessed as the degree of parallelism for parallel access partition operations

-extended reading: PDML (Parallel DML)-

What is Parallel (parallel) technology?

For a large task, the general practice is to use a process to execute serially.

But if the system resources are sufficient, Parallel (parallel) technology can be used to divide a large task into several small tasks, enable N processes (or threads) at the same time, and deal with these small tasks in parallel. These concurrent processes are called parallel execution servers (parallel executeion server), and they are managed by a process called concurrent coordination process.

Note:

Parallel technology should be considered only if you need to handle a large task (such as a job that takes a few hours) and have sufficient system resources (including CPU, memory, iUnip O, and so on).

Otherwise, in a multi-concurrent user environment, the resource burden of the system itself is already very large. If Parallel is enabled, one session will try to occupy all resources and other sessions will have to wait, resulting in a decline in system performance.

In general, Parallel technology should not be used in OLTP systems, but can be considered in OLAP systems.

PDML classification:

Parallel Query (parallel query)

Parallel DML (parallel DML statement execution)

Parallel DDL (parallel DDL statement execution)

Parallel queries: parallel queries allow you to divide a select statement into smaller queries, where each part of the query runs concurrently, and then combines the results of each part to provide the final result. (mostly used for full table scan, index full scan, etc.)

Parallel DML:Parallel DML includes insert, update, delete and merge. During PDML, Oracle can use multiple parallel execution servers (that is, concurrent processes) to execute insert, update, delete and merge, and multiple sessions execute simultaneously. At the same time, each session (concurrent process) has its own undo segment, which is an independent transaction. These transactions are either committed by concurrent coordination processes or rollback.

B) query performance:

Partitions may have different effects on different systems

For OLTP systems, partitioning operations need to be used with caution, because in traditional OLTP systems, most queries are likely to return results immediately, and most of the data may be obtained through a small index interval scan. Therefore, the performance benefits of partitioning may not be shown at all in OLTP systems.

In an OLTP system, partitioning may even degrade performance if not applied properly (partitioning may improve the performance of some types of queries, but these queries are not usually used in OLTP systems)

So one thing you have to understand: partitions are not always associated with "performance improvement".

For OLAP systems, partition elimination and parallel queries may lead to a significant improvement in efficiency.

IV: table partitioning mechanism

Four types of table partitions:

Range Partition (Range)

Hash partition (Hash)

List partition (List)

Combined partitions (Range-Hash or Range-List)

1. Range Partition:

Range partitions map data to each partition based on the specified partition key.

This partitioning method is the most commonly used, and the date is often used as the partitioning key.

Note:

① each partition needs to have a VALUES LESS THEN clause that specifies the upper limit of the partition (that is, the maximum value of the partition key that the partition can accept). When the value of the partition key in the record is less than this upper limit, the record will be placed in the partition; and when the value of the partition key in the record is equal to or greater than this upper limit, the record will be placed in the next partition with a higher upper limit.

In all ② partitions, except for the first partition, all partitions actually have an implicit lower limit (that is, the minimum value of the partition key that the partition can accept), which is the upper limit of the previous partition.

③ in the last partition, the upper limit value can be defined as MAXVALUE (this value can be understood as a maximum upper limit value of all partitions, including null values). When the value of the record partition key is greater than the upper limit value of all previous partitions, the record will be placed in this last partition.

Example of a table-building statement:

/ * range partition example * * /-- create sample table create table range_example (id number (2), done_date date Data varchar2 (50)-- create a partition The partition key is the done_date field partition by range (done_date) (partition part_1 values less than (to_date ('20160901,' yyyymmdd')), partition part_2 values less than (to_date ('20161001,' yyyymmdd'), partition part_3 values less than (maxvalue)) in the sample table (range_example)-- View the partition information of the range_ example table select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE'

View table partition information

Insert data: you can see that the sample table range_example has been divided into three sections.

If the done_date of record 1 is 2016-8-11, which is less than the upper limit of partition part_1, record 1 will be put into the part_1 partition.

If the done_date of record 2 is 2016-9-8, which is greater than the upper limit of part_1 but less than the upper limit of part_2, record 2 will be put into the part_2 partition.

The done_date of record 3 is 2016-10-20, which is greater than the upper limit of the first two partitions, so it will be put into the partition of the last maxvalue (part_3).

The implicit lower limit of part_2 is actually the upper limit of the previous partition part_1.

two。 Hash partition:

When performing a hash partition on a table, Oracle applies a Hash function to the partition key to determine which of the N partitions the data should be placed in.

Oracle suggests that N is a power of 2 (for example, N = 2, 4, 8, 16, and so on), so that the table data has the best overall distribution.

It is recommended that you use hash partitioning when the value of a column does not have an appropriate range condition.

Note:

If you change the number of hash partitions (when adding or deleting a partition to a hash partition table), the data will be redistributed across all partitions, that is, all data will be rewritten, because each row may now belong to a different partition.

The hash key (partition key) selected for the table should be a unique column or set of columns (which should have multiple different values) so that rows can be evenly distributed across multiple partitions.

If you use a hash partition, you will have no control over which partition a row of data will eventually be placed in (controlled by the hash function).

Example of a table-building statement:

/ * Hash partition example * * /-- create sample table create table hash_example (id number (2), done_date date Data varchar2 (50)-- create a hash partition The partition key is the done_date field partition by hash (done_date) (partition part_1, partition part_2) select * from user_tab_partitions where table_name = 'HASH_EXAMPLE' in the sample table (hash_example).

3. List Partition:

List partitions can specify which values of data should be placed in which partition based on the value of the partition key.

Note:

If a default partition is specified in the list partition, records whose partition key value is not in any partition value list will be placed in the default partition

Once a default partition is created, no more partitions can be added to the table

If no default partition is specified, Oracle will report an error (ORA-14400: inserted partition key does not map to any partition) when inserting a record whose partition key value is not in any partition values list.

Example of a table-building statement:

/ * list partition example * * /-- create sample table create table list_example (id number (2), name varchar (30) Data varchar2 (50)-- create a list partition The partitioning keys are id fields partition by list (id) (partition part_1 values ('1, 3, 5, 7), partition part_2 values ('2, 4, 6, 8), partition part_default values (default)) select * from user_tab_partitions where table_name = 'LIST_EXAMPLE'' in the sample table (list_example).

Partition information:

As above, records with values of 1, 3, 5 and 7 in the partition key (that is, the id field in the list_ example table) will be put into the part_1 partition

Records with partition key values of 2, 4, 6 and 8 will be placed in the part_2 partition

Records with partition key values of other values are placed in the last part_default partition.

4. Combined Partition:

A combined partition is a combination of range partition and hash partition, or a combination of range partition and list partition.

In combined partitions, the top-level partition mechanism is always range partition, and the second-level partition mechanism may be hash partition or list partition.

The data is physically stored on a subpartition, and the partition (the top-level range partition) becomes a logical container, or a container that points to the actual subpartition.

Each top-level partition does not need to have the same number of subpartitions.

Range-example of a hash combination partition table statement:

/ * * range-Hash partition * * / create table range_hash_example (id number (2), done_date date Data varchar2 (50))-the partition key for the top-level range partition is the done_date field in the range_hash_example table -- the partition key of the second layer hash partition is the id field in the range_hash_example table Partition by range (done_date) subpartition by hash (id) (partition part_1 values less than (to_date ('20160901,' yyyymmdd')) (subpartition part_1_sub_1, subpartition part_1_sub_2), partition part_2 values less than (to_date ('20161001),' yyyymmdd') (subpartition part_2_sub_1, subpartition part_2_sub_2) Partition part_3 values less than (maxvalue) (subpartition part_3_sub_1, subpartition part_3_sub_2) select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE'

Partition information:

In the range-hash combination partition as above, Oracle will first apply the Range partition rule to find out which interval the data belongs to (that is, first determine whether the record belongs to part_1, part_2 or part_3 through the done_date field)

Then apply the Hash function to determine which subpartition (physical partition) the data is finally placed in (that is, through the id field to determine which subpartition the record belongs to under a partition)

Range-list combination partition creation table statement example:

/ * * range-list partition * * / create table range_list_example (id number (2), done_date date Data varchar2 (50))-the partition key for the top-level range partition is the done_date field in the range_list_example table The partition key of the second layer list partition is the id field in the range_list_example table. Partition by range (done_date) subpartition by list (id) (partition part_1 values less than (to_date ('20160901),' yyyymmdd')) (subpartition part_1_sub_1 values ('1,'3,'5'), subpartition part_1_sub_2 values ('2,'4,'6'), partition part_2 values less than ('20161001') ) (subpartition part_2_sub_1 values ('11,'13,'15,'17'), subpartition part_2_sub_2 values ('12,'14'), subpartition part_2_sub_3 values ('16,'18), partition part_3 values less than (maxvalue) (subpartition part_3_sub_1 values ('21,'23') '25'), subpartition part_3_sub_2 values (' 22','24','26')) select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE'

Partition information:

As shown in the figure, each top-level range partition can have a different number of subpartitions.

5. Summary

Generally speaking, range partitioning is often used if you need to logically aggregate data according to a certain value. For example, the partition by "year" and "month" based on time data is a typical example. In many cases, range partitions can take advantage of the partition elimination feature (=

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.