09.Partitioning of Oracle tables

oracle basic system learning directory

01.CentOS7 silently install oracle11g
02.Oracle startup process
03. Start with simple sql
04.Oracle’s architecture
05.Oracle database objects
06.Oracle data backup and recovery
07.User and permission management
08.Oracle tables
09.Partitioning of Oracle tables
10.Oracle synonyms and sequences
11.Oracle’s view
12.Oracle index
13.Oracle connects to Java through JDBC
14.Transactions in Oracle
15.Oracle11g archiving method and log file related operations
16.Oracle’s data dictionary and dynamic performance view
17. PL/SQL basics of Oracle11g
18.Oracle procedures and functions
19. Cursors in Oracle11g
20. Triggers in Oracle11g
21.Oracle package (Package)
22.Temporary tablespace in Oracle
23. Oracle11g UNDO table space
24.Logical backup and recovery of Oracle11g
25. Oracle’s Recycle Bin
26.Oracle11g data loading
27.Oracle11g Flashback
28.Oracle11g materialized view

Oracle table partitions

oracle basic system learning directory
1. What is table partitioning?
- 1. Concept
- 2. Applicable scenarios:
2. Classification of table partitions
- 1. Classification of traditional table partitions:
- 2. New features of Oracle 11g:
3. Manage and maintain partitions
Click here to jump to the next section: 10. Synonyms and sequences of Oracle

1. What is table partitioning

1. Concept

In Oracle database, table partitioning is a technology that divides the data in the table into multiple parts for storage according to certain rules. Partitioned tables disperse and store data in tables into different partitions, which can improve query performance, data management, and maintenance operations. The following is an overview of partitioning of Oracle tables:

2. Applicable scenarios:

Large tables: When the table contains a large amount of data, using partitions can improve query performance because you can query only the data in a specific partition without scanning the entire table.
Data management: For applications that need to regularly delete or archive old data, time ranges or other rules can be used to partition the data to facilitate management and maintenance.
Parallel operations: Partitioned tables make it easier to perform parallel operations, such as parallel loading, parallel queries, etc.

2. Classification of table partitions

The following is a classification description and code examples of traditional table partitioning and new features of Oracle 11g:

1. Classification of traditional table partitions:

Range Partitioning

Range partitioning is to partition according to the range of a specified column, such as date range, number range, etc.

The following is a simple example code that demonstrates how to create a table using range partitioning in Oracle:
```
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2022', 'DD-MON-YYYY')),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2022', 'DD-MON-YYYY')),
  PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2022', 'DD-MON-YYYY')),
  PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);
```
In the above example, we created a table named sales and used the PARTITION BY RANGE statement to define a range based on the sale_date field Partition. Then, for each partition, we define the partition range using the VALUES LESS THAN statement.

By using range partitioning, we can partition based on the range values of the field, such as date range, numeric range, etc. This can better manage the data in the table, improve query performance, and enable flexible partition design based on business needs.
List Partitioning

List partitioning is to partition according to the value list of a specified column, for example, partitioning according to specific values such as region, department, etc.
```
CREATE TABLE employees (
  employee_id NUMBER,
  employee_name VARCHAR2(100),
  department VARCHAR2(100)
)
PARTITION BY LIST (department)
(
  PARTITION employees_dept1 VALUES ('Sales', 'Marketing'),
  PARTITION employees_dept2 VALUES ('HR', 'Finance'),
  PARTITION employees_dept3 VALUES ('IT')
);
```
In the above code, we create a table named employees, including employee_id, employee_name and department columns. Then, we use the PARTITION BY LIST statement to define list partitioning according to the department field, and define three partitions based on different department values.

List partitioning is a method of partitioning based on column values, which can allocate data to different partitions based on the specific content of column values. This partitioning method is suitable for situations where partitioning needs to be based on the value of a specific column.
Hash Partitioning

Hash partitioning is to perform hash partitioning based on specified columns and store data in different partitions.
```
CREATE TABLE orders (
  order_id NUMBER,
  customer_id NUMBER,
  order_date DATE,
  order_amount NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;
```
In the above code, we create a table named orders, including order_id, customer_id, order_date and order_amount columns. Then, we use the PARTITION BY HASH statement to define hash partitioning according to the customer_id field and specify 4 partitions.

Hash partitioning is a method of partitioning data based on its hash value, which can evenly distribute data into different partitions. This partitioning method is suitable for situations where there is no specific order requirement for data access and the data needs to be evenly distributed in different partitions.

Range-Range Partitioning

Range-range partitioning first range partitions by one column, and then range partitions by another column within each range partition.

CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY RANGE (sale_amount)
(
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2022', 'DD-MON-YYYY'))
  (
    SUBPARTITION sales_q1_amount1 VALUES LESS THAN (1000),
    SUBPARTITION sales_q1_amount2 VALUES LESS THAN (5000),
    SUBPARTITION sales_q1_amount3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2022', 'DD-MON-YYYY'))
  (
    SUBPARTITION sales_q2_amount1 VALUES LESS THAN (1000),
    SUBPARTITION sales_q2_amount2 VALUES LESS THAN (5000),
    SUBPARTITION sales_q2_amount3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2022', 'DD-MON-YYYY'))
  (
    SUBPARTITION sales_q3_amount1 VALUES LESS THAN (1000),
    SUBPARTITION sales_q3_amount2 VALUES LESS THAN (5000),
    SUBPARTITION sales_q3_amount3 VALUES LESS THAN (MAXVALUE)
  ),
  PARTITION sales_q4 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY'))
  (
    SUBPARTITION sales_q4_amount1 VALUES LESS THAN (1000),
    SUBPARTITION sales_q4_amount2 VALUES LESS THAN (5000),
    SUBPARTITION sales_q4_amount3 VALUES LESS THAN (MAXVALUE)
  )
);

In the above code, we create a table named sales, including sale_id, sale_date and sale_amount columns. Then, we use the PARTITION BY RANGE statement to define range partitioning based on the sale_date field, and use the SUBPARTITION BY RANGE statement to define range subpartitioning based on the sale_amount field. Next, we defined four primary partitions and three subpartitions for each primary partition.

2. New features of Oracle 11g:

Reference Partitioning

Reference Partitioning, which allows you to create partitions based on foreign key relationships. This can establish reference relationships between partitioned tables, thereby improving query performance and data maintenance efficiency.

The following is a simple example code that demonstrates how to create a reference partition in Oracle 11g:
```
--Create a main table
CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  order_date DATE,
  customer_id NUMBER
)
PARTITION BY RANGE (order_date)
(
  PARTITION orders_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
  PARTITION orders_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
  PARTITION orders_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
  PARTITION orders_future VALUES LESS THAN (MAXVALUE)
);

--Create a slave table that references the partition
CREATE TABLE order_items (
  order_item_id NUMBER,
  order_id NUMBER,
  product_id NUMBER,
  quantity NUMBER,
  CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE (fk_order_id);
```
In the above example, we first created a main table orders and partitioned it. Then, we created a slave table order_items and used the PARTITION BY REFERENCE statement to specify that the table is a reference partition and its partition key is the foreign key in the primary table order_id.

In this way, when data is inserted into the order_items table, the data will be automatically distributed to the associated orders table based on the value of order_id in the partition, thus realizing the function of referencing the partition.
Virtual Column-Based Partitioning

In Oracle 11g, the new feature of virtual column partitioning (Virtual Column Partitioning) was introduced, which can help users better manage data in partition tables. Virtual column partitioning allows users to define a virtual column in the partition table and use this column for partitioning, thereby achieving more flexible partition management. The following is a simple example code that demonstrates how to create a table partitioned using virtual columns in Oracle 11g:
```
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER,
  sale_month VARCHAR2(10) GENERATED ALWAYS AS (TO_CHAR(sale_date, 'YYYY-MM')) VIRTUAL
)
PARTITION BY RANGE (sale_month)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) -- defines the interval as every month
(
  PARTITION sales_q1 VALUES LESS THAN ('2022-04'),
  PARTITION sales_q2 VALUES LESS THAN ('2022-07'),
  PARTITION sales_q3 VALUES LESS THAN ('2022-10'),
  PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);
```
In the above example, we created a table named sales and defined a virtual column sale_month using the GENERATED ALWAYS AS statement, This column automatically generates data in year and month format based on the sale_date field. Then, we use the PARTITION BY RANGE statement to define range partitioning based on the sale_month field. Finally, we define the partition range for each partition using the VALUES LESS THAN statement.

By using virtual column partitioning, we can partition based on the value of a virtual column without actually storing the data for that column in the table. This simplifies table design and management while also improving query performance on partitioned tables.
Interval Partitioning

Interval Partitioning, which makes the management of partition tables more flexible and convenient. Use interval partitioning to automatically create new partitions for new data without having to manually define each partition. The following is a simple example code that demonstrates how to create a table using interval partitioning in Oracle 11g:
```
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTODSINTERVAL(1, 'MONTH')) -- defines the interval as every month
(
  PARTITION sales_q1 VALUES LESS THAN (TO_DATE('01-APR-2022', 'DD-MON-YYYY')),
  PARTITION sales_q2 VALUES LESS THAN (TO_DATE('01-JUL-2022', 'DD-MON-YYYY')),
  PARTITION sales_q3 VALUES LESS THAN (TO_DATE('01-OCT-2022', 'DD-MON-YYYY')),
  PARTITION sales_q4 VALUES LESS THAN (MAXVALUE)
);
```
In the above example, we created a table named sales and used the PARTITION BY RANGE statement to define a range based on the sale_date field Partition. Then, we use the INTERVAL statement to specify the interval as every month, which means that when new data is inserted, the system will automatically create new partitions for the new month.

By using interval partitioning, we do not need to manually define new partitions for each new time period. The system will automatically manage the creation and maintenance of partitions for us, which greatly simplifies the management of partition tables.
System Partitioning

System Partitioning does not specify a partition column, and ORACLE completes the control and management of the partition. It has no boundaries between range partitions or list partitions. The following is a simple sample code that demonstrates how to create a table using system partitioning in Oracle 11g:
```
CREATE TABLE student (
  ID NUMBER,
  name varchar2(20),
  address varchar2(20)
)
PARTITION BY SYSTEM
(
   partition p1,
   partition p2,
   partition p3
);
```
By using system partitions, we can store different partition data in different table spaces to better control and manage data storage. This is especially important for large partitioned tables, as different partition data can be stored on different physical storage devices, improving performance and management flexibility.

Please note that the code in the above example is limited to Oracle 11g releases. If you need to know more about system partitions, it is recommended that you consult the official documentation of Oracle 11g or refer to relevant tutorials and guides.

3. Manage and maintain partitions

In the Oracle database, managing table partitions can include operations such as creating, modifying, and deleting partitions, as well as performing data maintenance and queries on partitions. The following are some common operations for managing table partitions, along with corresponding sample code:

Add partition:
Use the ALTER TABLE statement to add partitions to a partitioned table.

Sample code:

ALTER TABLE sales
ADD PARTITION sales_q5 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY'));

Delete partition:
Use the ALTER TABLE statement to delete a specified partition in a partitioned table.

Sample code:
```
ALTER TABLE sales
DROP PARTITION sales_q5;
```
Truncate partition
Use the ALTER TABLE statement to delete all records in the specified partition

Sample code:
```
ALTER TABLE SALES
TRUNCATE PARTITION P3;
```
Merge partitions
Use the ALTER TABLE statement to join two adjacent partitions of a range partition or a composite partition.

Sample code:
```
ALTER TABLE SALES
MERGE PARTITIONS S1, S2 INTO PARTITION S2;
```
split partition
Use the ALTER TABLE statement to split records in a large partition into two partitions

Sample code:
```
ALTER TABLE SALES
SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);
```
Insert data:
You can use statements such as INSERT, UPDATE, and DELETE to maintain data in partitioned tables.

Sample code:
```
INSERT INTO sales (sale_id, sale_date, sale_amount) VALUES (1, TO_DATE('15-MAR-2022', 'DD-MON-YYYY'), 1000);
```
Query the partition table:
You can use the SELECT statement to query the partitioned table. You can query based on the partition key or query the data of a specific partition.

Sample code:
```
SELECT * FROM sales PARTITION (sales_q1);
```

The above are some common examples of operations for managing table partitions. In actual use, more complex partition management operations can be performed according to specific needs. If you need more detailed information, it is recommended to consult Oracle’s official documentation or refer to relevant tutorials and guides.