Explain in detail the concepts and advantages of Oracle Table Partitioning

Welcome to the Oracle community forum and interact with 2 million technical staff. After Entering Oracle8i, partition options will be available. Partitions separate tables on different tablespaces and use the divide-and-conquer method to support large tables with limited meta expansion. Large tables are physically manageable. Splitting a large table into smaller partitions can improve table maintenance, backup, recovery, and operations.

Welcome to the Oracle community forum and interact with 2 million technical staff> partition options are available after Oracle 8i. Partitions separate tables on different tablespaces and use the divide-and-conquer method to support large tables with limited meta expansion. Large tables are physically manageable. Splitting a large table into smaller partitions can improve table maintenance, backup, recovery, and operations.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Partition options are available after Oracle 8i. Partitions separate tables on different tablespaces and use the divide-and-conquer method to support large tables with limited meta expansion. Large tables are physically manageable. Splitting a large table into smaller partitions can improve table maintenance, backup, recovery, transaction and query performance.

Advantages of partitioning:

1. Enhanced availability: If a partition in the table cannot be used due to system failure, the remaining good partitions in the table can still be used;

2. Reduce the shutdown time: if the system failure only affects one partition of the table, only this partition needs to be repaired, which may take less time to repair than the whole large table;

3. Easy Maintenance: If you need to create a table, it is much easier to manage each public area in a single production environment than to manage a single large table;

4. Balanced I/O: You can allocate different partitions of a table to different disks to balance I/O and improve performance;

5. Improved performance: the query, add, modify, and other operations on large tables can be divided into different partitions of the table for parallel execution, which can make the operation faster, it is particularly useful for TP queries in data warehouses.

6. partitions are transparent to users, and the end user cannot feel the existence of partitions.

Create table BS_CDR_WLAN_LOC_01

(

DAY_NUMBER NUMBER (2) not null,

......

TPREMARK VARCHAR2 (200)

)

Partition by list (DAY_NUMBER)

(

Partition P_BS_CDR_OTH_LOC_01 values (1 ),

Partition P_BS_CDR_OTH_LOC_02 values (2 ),

......

Partition P_BS_CDR_OTH_LOC_31 values (31)

);

Partition by range

Note: partitions are based on a certain range.

SQL> create table niegc_part

2 (

3 part_id integer primary key,

4 part_date date,

5 part_dec varchar2 (100)

6)

7 partition by range (part_date)

8 (

9 partition part_01 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),

10 partition part_02 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),

11 partition part_03 values less than (maxvalue)

12 );

[1] [2]