Detailed description of oracle tablespace table partitions and how to query oracle table partitions

This article describes the concepts and operations of Partitioned Tables from the following aspects:
1. Concepts of tablespaces and partition tables
2. Specific functions of table partitions
3. Advantages and Disadvantages of table partitions
4. Table partition types and operation methods
5. maintenance operations on table partitions.
(1) concepts of tablespaces and partition tables
Tablespace:
Is a collection of one or more data files. All data objects are stored in the specified tablespace, but they are mainly stored in tables, so they are called tablespaces.

Partition Table:
When the amount of data in a table increases, the speed of data query slows down, and the performance of applications degrades. In this case, you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple tablespaces (physical files, it does not scan the entire table every time.

(2) specific functions of table partitions
The Table Partitioning function of Oracle brings great benefits to various applications by improving manageability, performance, and availability. In general, partitions can greatly improve the performance of some queries and maintenance operations. In addition, partitioning can greatly simplify common management tasks. Partitioning is a key tool for building a gigabit data system or a super high availability system.

The partition function further segments a table, index, or index organization table into segments. The segments of these database objects are called partitions. Each partition has its own name, and you can select its own storage features. From the perspective of the database administrator, the objects in a partition have multiple segments, which can be managed collectively or separately, this makes the database administrator quite flexible in managing the objects after the partition. However, from the perspective of the application, the partitioned table is exactly the same as the non-partitioned table. You do not need to modify it when using the SQL DML command to access the partitioned table.

When to use a partition table:
1. The table size exceeds 2 GB.
2. The table contains historical data. New data is added to new partitions.

(3) Advantages and Disadvantages of table partitions
Table partitions have the following advantages:
1. Improved query performance: You can query partition objects by searching only the partitions you are concerned about, improving the search speed.
2. Enhanced availability: If a 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 only need to fix the partition to fix the data;
4. Balanced I/O: You can map different partitions to disks to balance I/O and improve the overall system performance.

Disadvantages:
Partition Table: an existing table cannot be directly converted to a partition table. However, Oracle provides the online table redefinition function.

(4). Table partition types and operation methods

I. Range partitioning:

Range partitions map data to each partition based on the range. This range is determined by the partition key you specified when creating the partition. This partitioning method is the most commonly used, and the partition key usually uses the date. For example, you may partition the sales data by month.
When using range partitioning, consider the following rules:
1. Each partition must have a values less then clause, which specifies an upper limit not included in the partition. Any record with the partition key value equal to or greater than the upper limit will be added to the next higher partition.
2. All partitions except the first partition have an implicit lower limit. This value is the upper limit of the previous partition.
3. MAXVALUE is defined in the highest partition. MAXVALUE indicates an uncertain value. This VALUE is higher than the VALUE of any partition key in other partitions. It can also be understood as a VALUE higher than the value less then specified in any partition and a null VALUE.

Example 1:
Suppose there is a CUSTOMER table with 200000 rows of data. We partition this table using mermer_id. Each partition stores 100000 rows. We save each partition to a separate tablespace, in this way, data files can span across multiple physical disks. The following code creates a table and a partition:

Copy codeThe Code is as follows:
CREATE TABLE CUSTOMER
(
CUSTOMER_ID number not null primary key,
FIRST_NAME VARCHAR2 (30) not null,
LAST_NAME VARCHAR2 (30) not null,
PHONEVARCHAR2 (15) not null,
EMAILVARCHAR2 (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: divide by Time

Copy codeThe Code is as follows:
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 ', 'dd-MON-YYYY') TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 values less than (TO_DATE ('01-JUN-2003 ', 'dd-MON-YYYY') TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 values less than (TO_DATE ('01-JUL-2003 ', 'dd-MON-YYYY') TABLESPACE ORD_TS03
)

Example 3: MAXVALUE

Copy codeThe Code is as follows:
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
);
 

Ii. List partition:

This partition has only a few values in a column. Based on this feature, we can use list partitions.

Example 1

Copy codeThe Code is as follows:
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 2

Copy codeThe Code is as follows:
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
);
)


3. Hash partitions:

This type of partition uses the hash algorithm on the column value to determine which partition the row is placed in. When the column value does not have an appropriate condition, hash partitions are recommended.
Hash partitioning is a type of partitioning that distributes data evenly by specifying the Partition Number, because the size of these partitions is consistent by performing hash partitioning on the I/O device.

Example 1:

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
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 primary mechanism of hash partitions is to calculate the partition to which a specific record should be inserted based on the hash algorithm. The most important part of the hash algorithm is the hash function. If you want to use hash partitions in Oracle, you only need to specify the number of partitions. We recommend that the number of partitions use the N power of 2 to make the data distribution in each shard more even.

4. Combine range hash partitions

This type of partition is based on the range partition and list partition. the table first partitions the range by a column, and then partitions the list by a column. The partition in the partition is called a subpartition.

Copy codeThe Code is as follows:
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 ('1970-01-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('active') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('inactive') TABLESPACE rptfact2009
),
PARTITION P2 values less than (TO_DATE ('2017-03-01 ', 'yyyy-MM-DD') TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('active') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('inactive') TABLESPACE rptfact2009
)
)


5. Composite range hash partitions:

This type of partition is based on the range partition and hash partition. the table first partitions the range by a column, and then hash partitions by a column.

Copy codeThe Code is as follows:
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 ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition part_03 values less than (maxvalue)
);
 

(5) maintenance operations on table partitions:

I. Add partitions

The following code adds a P3 partition to the SALES table

Copy codeThe Code is as follows:
Alter table sales add partition P3 values less than (TO_DATE ('2017-06-01 ', 'yyyy-MM-DD '));

Note: The partition limit added above should be higher than the last partition limit.
The following code adds an P3SUB1 subpartition to the P3 partition of the SALES table
Copy codeThe Code is as follows:
Alter table sales modify partition P3 add subpartition P3SUB1 VALUES ('complete ');

Ii. delete partitions
Run the following code to delete a partition in Table P3:
Copy codeThe Code is as follows:
Alter table sales drop partition P3;
 

Run the following code to delete the P4SUB1 subpartition:
Copy codeThe Code is as follows:
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 the partition, you must delete the table.
Iii. Partition Truncation

A partition is used to delete data from a partition. Data in other partitions is not deleted. If a table has only one partition, it can be truncated. Run the following code to truncate a partition:
Copy codeThe Code is as follows:
Alter table sales truncate partition P2;
 

Run the following code to truncate a subpartition:
Copy codeThe Code is as follows:
Alter table sales truncate subpartition P2SUB2;
 

4. merge partitions

Merging partitions combines adjacent partitions into one partition. The result partitions use the boundary of High-score partitions. It is worth noting that partitions cannot be merged into partitions with low boundaries. Run the following code to merge the P1 P2 partitions:
Copy codeThe Code is as follows:
Alter table sales merge partitions P1, P2 into partition P2;
 

5. Shard

A shard Splits a shard into two new shards. After the Shard is split, the original shard no longer exists. Note that HASH partitions cannot be split.

Alter table sales sb1_partition P2 AT (TO_DATE ('1970-02-01 ', 'yyyy-MM-DD') INTO (PARTITION p21. PARTITION P22 );
 

6. Join partition (coalesca)
A combined partition joins data in a hash partition to another partition. When the data in the hash partition is large, you can add a hash partition and then join the partition. It is worth noting that, join partitions can only be used in hash partitions. Run the following code to perform join partitioning:

Copy codeThe Code is as follows:
Alter table sales coalesca partition;

7. rename table partitions

The following code changes p21.
Copy codeThe Code is as follows:
Alter table sales rename partition p21-p2;
 

VIII. related queries

Cross-partition Query
Copy codeThe Code is as follows:
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 the partitions in a table.
Copy codeThe Code is as follows:
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME = 'tablename'
 

Query index information

Copy codeThe Code is as follows:
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

-- Display information of all database partition tables:
Select * from DBA_PART_TABLES

-- Display information about all partition tables accessible to the current user:
Select * from ALL_PART_TABLES

-- Display information about all the partition tables of the current user:
Select * from USER_PART_TABLES

-- Display the table partition information to display the detailed partition information of all database partition tables:
Select * from DBA_TAB_PARTITIONS

-- Displays the detailed partition information of all partition tables accessible to the current user:
Select * from ALL_TAB_PARTITIONS

-- Display the detailed partition information of all the partition tables of the current user:
Select * from USER_TAB_PARTITIONS

-- Display the subpartition information to display the subpartition information of all the combined partition tables of the database:
Select * from DBA_TAB_SUBPARTITIONS

-- Display the subpartitions of all the combined partition tables accessible to the current user:
Select * from ALL_TAB_SUBPARTITIONS

-- Display the subpartitions of all the combined partition tables of the current user:
Select * from USER_TAB_SUBPARTITIONS

-- Display partition column information of all database partition tables:
Select * from DBA_PART_KEY_COLUMNS

-- Display the partition column information of all partition tables accessible to the current user:
Select * from ALL_PART_KEY_COLUMNS

-- Display the partition column information of all the partition tables of the current user:
Select * from USER_PART_KEY_COLUMNS

-- Display the subpartition column information of all database partition tables:
Select * from DBA_SUBPART_KEY_COLUMNS

-- Displays information about the subpartition columns of all partition tables accessible to the current user:
Select * from ALL_SUBPART_KEY_COLUMNS

-- Displays information about the subpartition columns of all partition tables of the current user:
Select * from USER_SUBPART_KEY_COLUMNS

-- How to query all partition tables in the oracle database
Select * from user_tables a where a. partitioned = 'yes'

-- To delete the data of a table is
Truncate table table_name;

-- Delete the data in a partition table
Alter table table_name truncate partition p5;