How to add partitions to a table in oracle

In Oracle, you can use the ALTER statement with "ADD PARTITION" to add partitions to the table. The syntax is "ALTER TABLE table name ADD PARTITION partition VALUES".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to add a partition to the table in Oracle

1. Add a partition

The following command adds a P3 partition to the SALES table

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-09-01','YYYY-MM-DD'));

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

The following command 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 command deletes the P3 table partition :

ALTER TABLE SALES DROP PARTITION P3;

The P4SUB1 subpartition was deleted with the following command:

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

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

3. Truncate a partition

Truncating a partition means deleting the data in a partition. It will not delete the partition, nor will it delete the data in other partitions. data. When there is only one partition in the table, the partition can be truncated. Truncate a partition through the following command:

ALTER TABLE SALES TRUNCATE PARTITION P2;

Truncate a subpartition through the following command:

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4. Merge partitions

Merge partitions to merge adjacent partitions Partitions are merged into one partition, and the resulting partition will take the boundaries of the higher partition. It is worth noting that partitions cannot be merged into a partition with lower boundaries. The following command realizes the merger of P1 and P2 partitions:

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5. Split partition

Split partition Split one partition into two new partitions. After splitting The original partition no longer exists. Note that HASH type partitions cannot be split.

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6. Joining partitions (coalesca)

Combining partitions is to join the data in the hash partition to other partitions. When the data in the hash partition is compared When it is large, you can add hash partitions and then join them. It is worth noting that join partitions can only be used in hash partitions. Use the following command to join the partition:

ALTER TABLE SALES COALESCA PARTITION;

7. Rename the table partition

The following command will change P21 to P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

eight , Related queries

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 there 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

--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;

Recommended tutorial:《Oracle Video Tutorial》

The above is the detailed content of How to add partitions to a table in oracle. For more information, please follow other related articles on the PHP Chinese website!