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

What are the advantages and disadvantages of Oracle table partitioning? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

(1.) The concept of table space and partition table

Tablespace:

Is a collection of one or more data files, all data objects are stored in the specified table space, but mainly store tables, so it is called table space.

Partition table:

As the amount of data in the table increases, the speed of querying the data slows down and the performance of the application degrades, so you should consider partitioning the table. After the table is partitioned, the table is still logically a complete table, but the data in the table is physically stored in multiple tablespaces (physical files), so that when querying data, the entire table is not scanned every time.

(2). The specific role of table partitions

Oracle's table partitioning features bring great benefits to a wide range of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of some queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabyte data systems or ultra-high availability systems.

The partitioning function can further subdivide tables, indexes, or index organization tables into segments, and the segments of these database objects are called partitions. Each partition has its own name, and you can choose its own storage characteristics. From the point of view of the database administrator, the object after a partition has multiple segments, which can be managed either collectively or individually, which gives the database administrator considerable flexibility in managing the partitioned objects. However, from an application perspective, the partitioned table is exactly the same as the non-partitioned table, and no modification is required when using the SQL DML command to access the partitioned table.

When to use partition tables:

1. The size of the table exceeds that of 2GB.

2. The table contains historical data, and new data is added to the new partition.

(3)。 Advantages and disadvantages of table partitioning

Table partitioning has the following advantages:

1. Improve the query performance: the query for partition objects can only search the partitions you care about, so as to improve the retrieval speed.

2. Enhanced availability: if one partition of the table fails, the data of the table in other partitions is still available

3. Easy maintenance: if a partition of the table fails, you need to repair the data. Just repair the partition.

4. Balanced Imax O: different partitions can be mapped to disk to balance Imax O and improve the performance of the whole system.

Disadvantages:

Partitioned table correlation: there is no way for existing tables to be converted directly to partitioned tables. However, Oracle provides the ability to redefine tables online.

(4)。 Several types and Operation methods of Table Partition

one。 Range Partition:

Range partitions map data to each partition based on scope, which is determined by the partition key you specified when you created the partition. This partitioning method is the most commonly used, and the partitioning key often uses a date. For example: you might break down sales data by month.

When using range partitioning, consider the following rules:

1. Each partition must have a VALUES LESS THEN clause that specifies an upper limit that is not included in the partition. Any record whose value of the partition key is equal to or greater than this upper limit is added to the next higher partition.

2. All partitions, except the first, have an implicit lower limit, which is the upper limit of the previous partition of this partition.

3. In the highest partition, MAXVALUE is defined. MAXVALUE represents an uncertain value. This value is higher than the value of any partition key in other partitions, and can also be understood as higher than the value of VALUE LESS THEN specified in any partition, including null values.

Example 1:

Suppose you have a CUSTOMER table with 200000 rows of data. We partition the table through CUSTOMER_ID, each partition stores 100000 rows, and we save each partition to a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY

FIRST_NAME VARCHAR2 (30) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

PHONE VARCHAR2 (15) NOT NULL

EMAIL VARCHAR2 (80)

STATUS CHAR (1)

)

PARTITION BY RANGE (CUSTOMER_ID)

(

PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01

PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02

)

Example 2: divided by time

CREATE TABLE ORDER_ACTIVITIES

(

ORDER_ID NUMBER (7) NOT NULL

ORDER_DATE DATE

TOTAL_AMOUNT NUMBER

CUSTOTMER_ID NUMBER (7)

PAID CHAR (1)

)

PARTITION BY RANGE (ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE ('01-MAY-2003)) TABLESPACEORD_TS01

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE ('01murJUNMYY')) TABLESPACE ORD_TS02

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE ('01murJULMULMY') TABLESPACE ORD_TS03

)

Example 3: MAXVALUE

CREATE TABLE RangeTable

(

Idd INT PRIMARY KEY

INAME VARCHAR (10)

Grade INT

)

PARTITION BY RANGE (grade)

(

PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb

PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb

);

two。 List Partition:

The characteristic of this partition is that there are only a few values for a column, based on which we can use list partitioning.

Example one

CREATE TABLE PROBLEM_TICKETS

(

PROBLEM_ID NUMBER (7) NOT NULL PRIMARY KEY

DESCRIPTION VARCHAR2 (2000)

CUSTOMER_ID NUMBER (7) NOT NULL

DATE_ENTERED DATE NOT NULL

STATUS VARCHAR2 (20)

)

PARTITION BY LIST (STATUS)

(

PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01

PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02

Example two

CREATE TABLE ListTable

(

Id INT PRIMARY KEY

Name VARCHAR (20)

Area VARCHAR (10)

)

PARTITION BY LIST (area)

(

PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb

PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb

);

)

three。 Hash partition:

This type of partition uses a hashing algorithm on column values to determine which partition to put the row into. It is recommended to use hash partitioning when there are no appropriate conditions for the value of the column.

A hash partition is a type of partition that distributes data evenly by specifying a partition number, because these partitions are of the same size by hashing the partitions on the Imap O device.

Example 1:

CREATE TABLE HASH_TABLE

(

COL NUMBER (8)

INF VARCHAR2 (100)

)

PARTITION BY HASH (COL)

(

PARTITION PART01 TABLESPACE HASH_TS01

PARTITION PART02 TABLESPACE HASH_TS02

PARTITION PART03 TABLESPACE HASH_TS03

)

Abbreviation:

CREATE TABLE emp

(

Empno NUMBER (4)

Ename VARCHAR2 (30)

Sal NUMBER

)

PARTITION BY HASH (empno) PARTITIONS 8

STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8)

The main mechanism of hash partition is to calculate which partition a record should be inserted into according to hash algorithm. The most important thing in hash algorithm is the hash function. If you want to use hash partition in Oracle, you only need to specify the number of partitions. It is suggested that the number of partitions should be 2 to the n-th power, which can make the data distribution of each interval more uniform.

four。 Combined range hash partition

This kind of partition is based on range partition and list partition. The table is partitioned first by a column and then by a column. The partitions in the partition are called sub-partitions.

CREATE TABLE SALES

(

PRODUCT_ID VARCHAR2 (5)

SALES_DATE DATE

SALES_COST NUMBER (10)

STATUS VARCHAR2 (20)

)

PARTITION BY RANGE (SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

PARTITION P1 VALUES LESS THAN (TO_DATE ('2003-01-01-01-01)) TABLESPACE rptfact2009

(

SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009

SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009

),

PARTITION P2 VALUES LESS THAN (TO_DATE ('2003-03-01)) TABLESPACE rptfact2009

(

SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009

SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009

)

)

five。 Compound range hash partition:

This kind of partitioning is based on range partitioning and hash partitioning. The table is partitioned first by a column and then by a column.

Create table dinya_test

(

Transaction_id number primary key

Item_id number (8) not null

Item_description varchar2 (300)

Transaction_date date

)

Partition by range (transaction_date) subpartition by hash (transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)

(

Partition part_01 values less than (to_date ('2006-01-01-01-01))

Partition part_02 values less than (to_date ('2010-01-01-01-01))

Partition part_03 values less than (maxvalue)

);

(5)。 Some maintainable operations about table partitioning:

First, add partition

The following code adds a P3 partition to the SALES table

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN (TO_DATE ('2003-06-01)

Note: the partition boundary added above should be higher than the last partition boundary.

The following code adds a P3SUB1 subpartition to the P3 partition of the SALES table

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES ('COMPLETE')

2. Delete the partition

The following code removes the P3 table partition:

ALTER TABLE SALES DROP PARTITION P3

The P4SUB1 subpartition is deleted in the following code:

ALTER TABLE SALES DROP SUBPARTITION P4SUB1

Note: if the deleted partition is the only partition in the table, the partition cannot be deleted. To delete this partition, you must delete the table.

3. Truncation of zoning

Truncating a partition means that the data in one partition is deleted, and the partition is not deleted, nor the data in other partitions. You can truncate a partition even if there is only one partition in the table. Truncate the partition with the following code:

ALTER TABLE SALES TRUNCATE PARTITION P2

Truncate the subpartition with the following code:

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2

IV. Merging partitions

Merging partitions merges adjacent partitions into a single partition, resulting in higher partition boundaries, and it is worth noting that partitions cannot be merged into lower-bounded partitions. The following code implements the merging of P1 and P2 partitions:

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2

5. Split the partition

Split partition splits a partition into two new partitions. after the split, the original partition no longer exists. Note that partitions of type HASH cannot be split.

ALTER TABLE SALES SBLIT PARTITION P2 AT (TO_DATE ('2003-02-01)) INTO (PARTITION P21 partner P22)

VI. Joint zone (coalesca)

The combined partition is to join the data in the hash partition to other partitions. when the data in the hash partition is relatively large, you can add the hash partition, and then join it. It is worth noting that the joint partition can only be used in the hash partition. Join the partition with the following code:

ALTER TABLE SALES COALESCA PARTITION

7. Rename table partition

The following code changes P21 to P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2

8. Related inquiries

Cross-partition query

Select sum (*) from

(select count (*) cn from t_table_SS PARTITION (P200709 / 1)

Union all

Select count (*) cn from t_table_SS PARTITION (P200709 / 2)

);

Query how many partitions are on the table

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

Query index information

Select object_name,object_type,tablespace_name,sum (value)

From v$segment_statistics

Where statistic_name IN ('physical reads','physical write','logical reads') and object_type='INDEX'

Group by object_name,object_type,tablespace_name

Order by 4 desc

-- displays information about all partitioned tables in the database:

Select * from DBA_PART_TABLES

-- displays all partition table information that the current user can access:

Select * from ALL_PART_TABLES

-- displays information about all partition tables of the current user:

Select * from USER_PART_TABLES

-- display table partition information display detailed partition information of all partition tables in the database:

Select * from DBA_TAB_PARTITIONS

-- displays the detailed partition information of all partition tables that the current user can access:

Select * from ALL_TAB_PARTITIONS

-- displays the detailed partition information of all partition tables of the current user:

Select * from USER_TAB_PARTITIONS

-- display subpartition information displays the subpartition information of all combined partition tables in the database:

Select * from DBA_TAB_SUBPARTITIONS

-- displays the sub-partition information of all combined partition tables that the current user can access:

Select * from ALL_TAB_SUBPARTITIONS

-- displays the sub-partition information of all combined partition tables of the current user:

Select * from USER_TAB_SUBPARTITIONS

-- display partition column displays partition column information for all partition tables in the database:

Select * from DBA_PART_KEY_COLUMNS

-- displays the partition column information of all partition tables that the current user can access:

Select * from ALL_PART_KEY_COLUMNS

-- displays the partition column information of all partition tables of the current user:

Select * from USER_PART_KEY_COLUMNS

-- display the sub-partition column displays the sub-partition column information of all partition tables in the database:

Select * from DBA_SUBPART_KEY_COLUMNS

-- displays the sub-partition column information of all partition tables that the current user can access:

Select * from ALL_SUBPART_KEY_COLUMNS

-- displays the sub-partition column information of all partition tables of the current user:

Select * from USER_SUBPART_KEY_COLUMNS

How to query all the partitioned tables in the oracle database

Select * from user_tables a where a.partitionedorganizations

-- deleting the data of a table is

Truncate table table_name

Delete a partition table the data of a partition is

Alter table table_name truncate partition p5

Why use table partitioning: in actual development, there is often a large amount of data in a table. The order of magnitude of millions. If you put this data in a physical file (that is, the physical storage file of the table), it is too large. We can use table partitioning. For example, I put the data in the table with "name, ID number" in one physical file, and the rest in another physical file. In this way, although this is still data in a table, it is physically separate. There are two ways of partitioning, one is partitioning by row. For example, the first ten thousand rows are divided into one district, and the last ten thousand rows are divided into one district. The second is to partition by field. This is also easy to understand. The structure of Oracle is: data block-> area-> segment-> table space. Take this as an inclusion relationship. A table in the tablespace (no partition) uses a segment to store data (in fact, there are a total of 4 segments to store the table, one of which is the data segment, which you asked to specifically store table data, and the other three segments have other uses. You can check it out for yourself). On the other hand, it is easy to understand that partitioned tables have a separate segment in each partition. Each partition has table data, and it is certain that each partition must have a data segment to store data. There are mainly the following ways to partition: range + hash partition mode using EXP/IMP mode online redefinition, etc. But it is best to partition according to the business difficult to maintain the above content, do you know what are the advantages and disadvantages of Oracle table partition? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.