Oracle table partitioning detailed (by day, month, year, etc.)

The concept of partitioned tables:

When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so 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 table spaces (physical files) so that querying the data does not always scan the entire table.


Advantages of Partitioned Tables:


1) Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.


2) Enhanced usability: If one partition of the table fails, the data for the table on the other partitions is still available;


3) Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;


4) Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance.


Types of partition tables:


1. Range Partitioning
Concept: Range partitioning maps data to each partition based on its scope, which is determined by the partition key you specify when you create the partition. This partitioning method is most commonly used, and the partitioning key often takes the date. For example, you might partition sales data by month.




--Partitioning by row
sql> CREATE TABLE part_andy1
2 (
3 andy_id number not NULL PRIMARY KEY,
4 first_name VARCHAR2 (+) not NULL,
5 last_name VARCHAR2 (+) not NULL,
6 PHONE VARCHAR2 () not NULL,
7 EMAIL VARCHAR2 (80),
8 STATUS CHAR (1)
9)
PARTITION by RANGE (andy_id)
11 (
PARTITION PART1 VALUES less THAN (10000),
PARTITION PART2 VALUES less THAN (20000)
14);


Table created.


--Partitioning by Time


sql> CREATE TABLE Part_andy2
2 (
3 order_id Number (7) Not NULL,
4 Order_date DATE,
5 Otal_amount number,
6 custotmer_id Number (7),
7 PAID CHAR (1)
8)
9 PARTITION by RANGE (order_date)
10 (
One PARTITION p1 VALUES less THAN (to_date (' 2014-10-1 ', ' yyyy-mm-dd ')),
PARTITION P2 VALUES Less THAN (to_date (' 2015-10-1 ', ' yyyy-mm-dd '),
PARTITION P3 VALUES Less THAN (to_date (' 2016-10-1 ', ' yyyy-mm-dd '),
Partition P4 values less than (MaxValue)
15);


Table created.


2. Hash partition


Concept:
Hash partitioning can be used for tables that do not effectively partition the scope, which helps to improve performance. The hash partition distributes the data in the table evenly to several partitions that you specify, and the column partition is automatically assigned according to the hash value of the partition column, so you cannot control or know which partition the record will be placed in, and the hash partition can support multiple dependent columns.


Attention:
Hash partition The main mechanism is based on the hash algorithm to calculate the specific record should be inserted into which partition, hash algorithm is the most important hash function, Oracle if you want to use the hash partition, only specify the number of partitions. It is recommended that the number of partitions be 2 N, which makes the data distribution more evenly between partitions.


--Partition by hash
Sql> CREATE TABLE Part_andy3
2 (
3 transaction_id number primary key,
4 item_id Number (8) NOT NULL
5)
6 partition by hash (transaction_id)
7 (
8 Partition part_01,
9 Partition part_02,
Ten partition part_03
11);


Table created.


3. List partition


Concept:
The list partition also needs to specify the value of the column, its partition value must be explicitly specified, the partition column can only have one, and cannot be specified as a range or hash partition as a partition dependent column, but its individual partition corresponding value can be multiple.


Attention:
You must determine the possible values for the partition column at the time of partitioning, and once the inserted column values are not within the partition range, the Insert/update fails, so it is generally recommended to create a default partition that stores records that are not in the specified range, similar to the MaxValue partition in the range partition, when using the list partition.


--by list partition
Sql> CREATE TABLE Part_andy4
2 (
3 ID VARCHAR2 (byte) not NULL,
4 City Varchar2 (20)
5)
6 Partition by List
7 (
8 partition t_list025 values (' Beijing '),
9 Partition t_list372 values (' Shanghai '),
Ten partition t_list510 values (' Changsha '),
Partition P_other values (default)
12);


Table created.


4. Grouping partitions


ORACLE10G offers two different partitioning combinations
–range-hash
Sql> CREATE TABLE Part_andy5
2 (
3 transaction_id number primary key,
4 item_id Number (8) NOT NULL,
5 item_description VARCHAR2 (300),
6 Transaction_date Date
7)
8 partition by Range (transaction_date) subpartition by hash (transaction_id)
9 (
Ten partition part_01 values less than (to_date (' 2014-10-1 ', ' yyyy-mm-dd '),
One partition part_02 values less than (to_date (' 2015-10-1 ', ' yyyy-mm-dd ')),
Partition part_03 values less than (MaxValue)
13);


Table created.






–range-list
sql> CREATE TABLE SALES
2 (
3 product_id VARCHAR2 (5),
4 Sales_date DATE,
5 sales_cost Number (10),
6 STATUS VARCHAR2 (20)
7)
8 PARTITION by RANGE (sales_date) subpartition by LIST (STATUS)
9 (
PARTITION P1 VALUES Less THAN (to_date (' 2014-10-1 ', ' yyyy-mm-dd ')
One (subpartition p1sub1 values (' ACTIVE '), subpartition p1sub2 values (' INACTIVE ')
PARTITION P2 VALUES Less THAN (to_date (' 2015-10-1 ', ' yyyy-mm-dd ')
13 (
Subpartition p2sub1 VALUES (' ACTIVE '),
Subpartition p2sub2 VALUES (' INACTIVE ')
16)
17);


Table created.




ORACLE11G adds four different combinations
–range-range
–list-range
–list-hash
–list-list


Virtual columns in Oracle 11g. The partition key of the partitioned table before 11g must be physically present. The 11g starts with a virtual column and can be used as partition key.
--Partition by week
sql> CREATE TABLE Part_andy6
2 (
3 getdate date not NULL,
4 WD number GENERATED always as (To_number (To_char (getdate, ' D '))) VIRTUAL
5)
6 PARTITION by LIST (WD)
7 (
8 PARTITION Mon VALUES (1),
9 PARTITION Tue VALUES (2),
Ten PARTITION Wed VALUES (3),
PARTITION Thu VALUES (4),
PARTITION Fri VALUES (5),
PARTITION Sat VALUES (6),
PARTITION Sun VALUES (7)
15);


Table created.


Sql>
sql> INSERT INTO Part_andy6 (getdate) values (sysdate);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-1);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-2);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-3);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-4);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-5);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-6);


1 row created.


sql> INSERT INTO Part_andy6 (getdate) values (sysdate-7);


1 row created.


--Check Test success
Sql> select * from Part_andy6;


GETDATE WD
------------------- ----------
2014-11-23 16:35:07 1
2014-11-24 16:35:07 2
2014-11-25 16:35:07 3
2014-11-26 16:35:07 4
2014-11-27 16:35:07 5
2014-11-28 16:35:07 6
2014-11-29 16:35:07 7
2014-11-22 16:35:08 7


8 rows selected.


Oracle Database 11g,interval type partition table, which automatically creates partitions of the specified interval based on the loading data.


To create a partition table that is partitioned by month:


A. Creating a partitioned table


Sql> CREATE TABLE Interval_andy7 (A1 number, A2 DATE)
2 PARTITION by RANGE (A2)
3 INTERVAL (Numtoyminterval (1, ' MONTH '))
4 (PARTITION part1
5 VALUES Less THAN (to_date (' 2014-11-1 ', ' yyyy-mm-dd ')),
6 PARTITION Part2
7 VALUES Less THAN (to_date (' 2014-12-1 ', ' yyyy-mm-dd '))
8);


Table created.
Note: If the interval partition table is not written for all partitions, the partition will be generated automatically after inserting the relevant data
B. View the partition of the current table:
Sql> Select Table_name,partition_name from user_tab_partitions where table_name= ' interval_andy7 ';
TABLE_NAME Partition_name
------------------------------ ------------------------------
Intervalpart PART1
Intervalpart PART2
C. Insert test data:
Sql> begin
2 for I in 0.. Loop
3 INSERT into INTERVAL_ANDY7 values (i,add_months (to_date (' 2014-11-1 ', ' yyyy-mm-dd '), i));
4 End Loop;
5 commit;
6 end;
7/


PL/SQL procedure successfully completed.


The PL/SQL process has completed successfully.
Add: the Add_months () function gets the month of the first one months or the next one months, the negative number in the parameter represents forward, and the positive number represents the future.
--Last one months
Select To_char (Add_months (Trunc (sysdate), -1), ' Yyyymm ') from dual;
--Next one months
Select To_char (Add_months (Trunc (sysdate), 1), ' Yyyymm ') from dual;
D. Observe the automatically created partition:
Sql> Select Table_name,partition_name from user_tab_partitions where table_name= ' interval_andy7 ';


TABLE_NAME Partition_name
------------------------------ ------------------------------
Interval_andy7 PART1
Interval_andy7 PART2
Interval_andy7 SYS_P24
Interval_andy7 Sys_p25
Interval_andy7 sys_p26
Interval_andy7 sys_p27
Interval_andy7 SYS_P28
Interval_andy7 sys_p29
Interval_andy7 SYS_P30
Interval_andy7 sys_p31
Interval_andy7 SYS_P32


TABLE_NAME Partition_name
------------------------------ ------------------------------
Interval_andy7 SYS_P33
Interval_andy7 sys_p34


Rows selected.








The following creates a partitioned table with a day interval:


1. Create a partitioned table:
Sql> CREATE TABLE Interval_andy8
2 (
3 ID number,
4 DT Date
5)
6 partition by range (DT)
7 INTERVAL (Numtodsinterval (1, ' Day '))
8 (
9 partition p20141101 values less than (to_date (' 2014-11-1 ', ' yyyy-mm-dd ')
10);


Table created.


2. View Table partitions:
Sql> Select Table_name,partition_name from user_tab_partitions where table_name= ' interval_andy8 ';


TABLE_NAME Partition_name
------------------------------ ------------------------------
Interval_andy8 P20141101


3. Insert the test data:
Begin
For I in 1.. Loop
INSERT into interval_andy8 values (I,trunc (to_date (' 2014-11-1 ', ' yyyy-mm-dd ') +i);
End Loop;
Commit
End
/
The PL/SQL process has completed successfully.
4. Observe the automatically created partitions:
Sql> Select Table_name,partition_name from user_tab_partitions where table_name= ' interval_andy8 ';


TABLE_NAME Partition_name
------------------------------ ------------------------------
Interval_andy8 P20141101
Interval_andy8 Sys_p35
Interval_andy8 sys_p36
Interval_andy8 sys_p37
Interval_andy8 sys_p38
Interval_andy8 sys_p39
Interval_andy8 SYS_P40
Interval_andy8 sys_p41
Interval_andy8 sys_p42
Interval_andy8 sys_p43
Interval_andy8 SYS_P44


TABLE_NAME Partition_name
------------------------------ ------------------------------
Interval_andy8 Sys_p45
Interval_andy8 sys_p46


Rows selected.


Description: Personal Summary + web blog
Reference: http://blog.csdn.net/tianlesoftware/article/details/5662337

Oracle table partitioning detailed (by day, month, year, etc.)