Oracle Partition Table

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.
Oracle provides the following table partitions:
I. Range partitioning: This type of partitioning uses a group of values of a column, which is usually used as the partition key.
Example 1: assume there is a CUSTOMER table with 200000 rows of data. We partition the table using mermer_id, and store 100000 rows for each partition, we save each partition to a separate tablespace so that data files can span multiple physical disks. The following code creates a table and a partition:

Program code

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
)

Note: When creating a table for partitioning, The tablespace must exist first. We recommend that you add different partitions to different tablespaces.
Example 2: if there is an ORDER_ACTIVITIES table that clears orders every six months, we can partition the table by month. The partition code is as follows:

Program code

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') TABLESPACE orD_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
)

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

Program code

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
)

3. Hash partitions: these partitions use the hash algorithm on column values to determine which partition the rows are placed in. When the column value does not have an appropriate condition, hash partitions are recommended. See the following example:
Example 1:

Program code

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
)

4. Composite range list partitions: these partitions are based on range partitions and list partitions. A table first partitions a range by a column, and then partitions a list by a column, A partition is called a subpartition.
Example 1:

Program code

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 P1_TS
(
SUBPARTITION P1SUB1 VALUES ('active') TABLESPACE SUBP1_TS1,
SUBPARTITION P1SUB2 VALUES ('inactive') TABLESPACE SUBP1_TS2
),
PARTITION P2 values less than (TO_DATE ('2017-03-01 ', 'yyyy-MM-DD') TABLESPACE P2_TS
(
SUBPARTITION P2SUB1 VALUES ('active') TABLESPACE SUBP2_TS1,
SUBPARTITION P2SUB2 VALUES ('inactive') TABLESPACE SUBP2_TS2
)
)

Example 2: Use a TEMPLATE

Program code

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)
SUBPARTITION TEMPLATE
(
SUBPARTITION SUB1 VALUES ('active') TABLESPACE SUBP1_TS1,
SUBPARTITION SUB2 VALUES ('inactive') TABLESPACE SUBP2_TS2
)
(
PARTITION P1 values less than (TO_DATE ('1970-01-01 ', 'yyyy-MM-DD') TABLESPACE P1_TS,
PARTITION P2 values less than (TO_DATE ('2017-03-01 ', 'yyyy-MM-DD') TABLESPACE P2_TS
)

5. Composite range hash partitions: these partitions are based on range partitions and hash partitions. the table first partitions the range by a column, and then hash partitions by a column. It is very similar to the preceding definition method, and is not separately used here.
Table partitions are transparent to users. when inserting data, Oracle will automatically judge the inserted data and put it into the corresponding table partitions. However, when you want to query data in a partition separately, you must manually specify the partition name.
Example 1: (this example is based on: 4. Example 1 of composite range list partition)
Insert a record to the SALES table without specifying the table partition.

Program code

Insert into sales values ('20170101', '01-February 02 ', 00001, 'active ')
/
Insert into sales values ('20170101', '01-July 01-01 ', 00002, 'active ')
/
Insert into sales values ('20170101', '01-February 03 ', 00003, 'inactive ')
/
Insert into sales values ('20170101', '04-July 03-03', 00004, 'inactive ')
/
Insert into sales values ('20170101', '04-February 02 ', 00005, 'inactive ')
/

Do not specify a table partition to view the SALES table information:
Select * from sales; the result is as follows:
Specify the partition of the P1 table to query the SALES table information:
Select * from sales partition (P1); the result is as follows:
Specify the P1SUB1 subpartition to query the SALES table information:
Select * from sales subpartition (P1SUB1); the result is as follows:
Example 2: (this example is based on: 4. Example 2 of composite range list partition)
Example 2 it is a little tricky to query table partitions Based on the TEMPLATE.
Specify the partition of the P1 table to query the SALES table information:
Select * from sales partition (P1); the result is as follows, consistent with the query.
Specify the SUB1 subpartition to query the SALES table information:
Select * from sales subpartition (SUB1); the following error message is displayed:
How can we solve the above problems? The following table lists the data dictionaries used to view partition information in sys mode:
We can see that the subpartition is not called SUB1, but P1_SUB1. query the information again, as shown in:
Some maintenance operations on table partitions:
I. Add partitions
The following code adds a P3 partition to the SALES table
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
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:
Alter table sales Drop PARTITION P3;
Run the following code to delete the P4SUB1 subpartition:
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:
Alter table sales truncate partition P2;
Run the following code to truncate a subpartition:
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:
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. coalesca joins data in hash partitions to other partitions. When the data in hash partitions is large, you can add hash partitions, then join. It is worth noting that the join partition can only be used in hash partitions. Run the following code to perform join partitioning:
Alter table sales coalesca partition;
7. rename table partitions
The following code changes p21.
Alter table sales rename partition p21-p2;
9. 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 ));
10. query the partitions in the table.
Select * FROM useR_TAB_PARTITIONS Where TABLE_NAME = 'tablename'
11. 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
-- 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;
Note: select a partition based on the actual situation.
Table partitions have the following advantages:
1. Data Query: data is stored on multiple files, which reduces the I/O load and improves the query speed.
2. Data pruning: It is ideal for storing historical data.
3. Backup: divides data in large tables into multiple files to facilitate backup and recovery.
4. Parallelism: DML operations can be performed on tables at the same time to improve the parallel performance.
========================================================== ==========
Index:
1. General indexes:
Create index index_name on table (col_name );
2. Oracle partition index details
Syntax: Table Index
Create [UNIQUE | BITMAP] INDEX [schema.] index_name
ON [schema.] table_name [tbl_alias]
(Col [ASC | DESC]) index_clause index_attribs
Index_clses:
There are two situations:
1. Local Index
The storage location of the index information depends on the Partition information of the parent table. In other words, to create such an index, the parent table must be Partition.
1.1 The index information is stored in the tablespace where the partition of the parent table is located. However, you can only create a HashTable or composite partition table in the parent table.
Local store in (tablespace)
1.2 you can only create a HashTable or composite partition table in the parent table. The specified number of partitions must be the same as the number of partitions in the parent table.
Local store in (tablespace) (PARTITION [partition [LOGGING | NOLOGGING] [TABLESPACE {tablespace | DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name | DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace])
1.3 The index information is stored in the tablespace where the partition of the parent table is located. This syntax is the simplest and is also the most commonly used partition index creation method.
Local
1.4. The number of specified partitions must be the same as that of the parent table.
LOCAL (PARTITION [partition
[LOGGING | NOLOGGING]
[TABLESPACE {tablespace | DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[Store in {tablespace_name | DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace])
Global Index
The storage location of the index information is completely irrelevant to the Partition information of the parent table. It doesn't even matter if the parent table is a partition table. Syntax:
Global partition by range (col_list)
(PARTITION partition values less than (value_list)
[LOGGING | NOLOGGING]
[TABLESPACE {tablespace | DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause])
However, in this case, if the parent table is a partition table, you must update Global Index to delete all partitions of the parent table. Otherwise, the Index information is incorrect.
Alter TABLE TableName Drop PARTITION PartitionName Update Global Indexes
-- Query Indexes
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