Oracle 11g notes--partition table

First, the partition table
Partitioning technology, Oracle allows you to divide a large table into parts, each part called a partition, and then put each part on a different physical disk to improve the performance of the entire database.
Each partition can be divided into several more, so that the resulting partition is called a sub-partition (subpartition). The partitioned table is logically a whole.
1. Advantages:
(1) Partitioning technology makes database manageability easier,
For example, the user can load data in a separate partition, without any effect on other partitions; The user can create an index on a separate partition.
(2) Partitioning can improve the query performance of the table, the WHERE clause of the SQL statement will filter out the partitions that are not needed, and Oracle will no longer scan those partitions that are not needed.
(3) Partitioning technology reduces the unavailability of data, and allows users to maintain data in one partition independently, without affecting the use of data in other partitions.
(4) Partitioning technology is done at the database level with little or no modification to the application.

2. Classification:
(1) Range partitioning
Divides the entire table into different parts based on the range of column values in the table, such as: partitioning by Time
(2) List partition
Use column values to divide a table into parts
(3) hash partition: The function hash function divides the table into several parts
(4) Composite partitioning: Partitioning a table using two partitioning methods

3. Create an example
(1) Scope partition table
Sql>create Table Sales (invoice_no number,sale_year int not null,sale_month int. not Null,sale_day int. NOT NULL)
Partition by range (Sale_year)
(partition P1 values less than (+) tablespace USERS,
Partition P2 values less than (2001) Tablespace LMTBSB,
Partition P3 values less than (2002) Tablespace bigtbs_01,
Partition P4 values less than (2003) tablespace tsseg_manual);

(2) hash partition (divides the table into several partitions based on the hash value). Law one: Specify the number of partitions; second, specify the partition name)
A. Specify the number of partitions, keyword partitions the number of partitions specified
Sql>create Table Dept (deptno number,deptname varchar (32))
Partition by hash (DEPTNO) partitions 4;
Specifies that the number of partitions is 4, and all partitions are placed in the same table space.
B. Partition by the name of the specified partition
Sql>create Table Dept (deptno number,deptname varchar (32))
Partition by hash (DEPTNO)
(Partition P1 tablespace USERS,
Partition P2 tablespace LMTBSB,
Partition P3 tablespace bigtbs_01,
Partition P4 tablespace tsseg_manual);

(3) List partition
Sql>create Table Sales (item integer,qty integer,store_name varchar (), city varchar2, sale_date date)
Partition by List
(Partition Region_east values (' Hangzhou ', ' Shanghai ') tablespace USERS,
Partition region_west values (' Chengdu ', ' Chongqing ') tablespace LMTBSB,
Partition Region_south values (' Guangzhou ', ' Guilin ') tablespace bigtbs_01);

4. Other examples
(1) Get all sub-partitions of a partitioned table
Sql>select Table_name,partition_name,subpartition_name,tablespace_name
From Dba_tab_subpartitions
where Table_name= ' Composite_sales '
ORDER BY Table_name,partition_name
(2) move a partition of a table from one table space to another table space
A. Confirm which tablespace SP1 the partition
Sql>select Table_name,partition_name,subpartition_name,tablespace_name
From Dba_tab_subpartitions
Where table_name= ' sales ' and table_owner= ' test '
Query to know: SP1 in P1 table space
B. Moving a partition SP1 to a table space p
Sql>alter table Sales Move partition SP1 tablespace TP;
C. Moving the partition invalidates the index and requires rebuilding the index, such as:
Sql>alter index SALES3_PK rebuild;
(3) See if a table is a table space
Sql>select owner,table_name,partitioned from Dba_tables where owner= ' test ' and table_name= ' sales3 ';
View the user test table Sales3 is not a partitioned table.
If Partitioned=yes indicates that the table is a partitioned table
(4) Get the SQL statement that created the partition
Sql>select dbms_metadata.get_ddl (' TABLE ', ' sales3 ', ' test ') from dual;
Add a range partition, note: The new partition boundary value must be larger than the existing partition boundary
Sql>alter table Sales3 Add partition p1999 values less then (1999);
(5) Merge partition, note: The name of the partition after merging cannot be the name of the partition with the lower boundary value, but it can be the name of another partition.
Sql>alter table Sales3 Merge partitions sp1,sp3 into partitions SP3
(6) Partition switching
Sql>alter table Sales3 Exchange partition SP1 with table tar_sales;
Or:
Sql>alter table Sales3 Exchange partition SP3 with table tar_sales including indexes without validation;
Note: There is an index on the partition table when the partition is exchanged, and there should be an index on the target table
To see which columns comprise the index Ind_map:
Sql>select index_owner,index_name,table_name,column_name from Dba_ind_columns where index_owner= ' test ' and index_ Name= ' Ind_map ';

Oracle 11g notes--partition table