A small case for Range Partitions for multiple columns
Author: Liuying Bo
Time: 2003-12-29
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
Practice
First build partition table
Create Table Simng_Part (
LATN_ID NUMBER (4) Not null,
PNAME Char (200),
MON_ID NUMBER (2) Not null
)
Partition By Range (latn_id, mon_id)
(
Partition Part_a01 Values Less Than (2000, 2) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_a02 Values Less Than (2000, 3) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_a12 Values Less Than (2000, 13) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_B01 Values Less Than (2003, 2) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_B02 Values Less Than (2003, 3) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_b12 Values Less Than (2003, 13) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024k MaxExtents 2000),
Partition Part_d12 Values Less Than (2020, 13) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MAXEXTENTS 2000)
)
/
INSERT INTO SIMNG_PART VALUES (2000, 'A', 1);
INSERT INTO SIMNG_PART VALUES (2000, 'A', 2);
INSERT INTO SIMNG_PART VALUES (2000, 'A', 3);
INSERT INTO SIMNG_PART VALUES (2002, 'A', 1);
INSERT INTO SIMNG_PART VALUES (2002, 'A', 3);
INSERT INTO SIMNG_PART VALUES (2002, 'A', 9);
INSERT INTO SIMNG_PART VALUES (2019, 'A', 1);
COMMIT;
CREATE TABLE SIMNG_PART1 (
LATN_ID NUMBER (4) Not null,
PNAME Char (200),
MON_ID NUMBER (2) Not null
)
Partition by Range (MON_ID, LATN_ID)
(
PARTITION PART_A01 VALUES LESS THAN (2,2000) TABLESPACE TBS_DATA PCTFREE 10 STORAGE (INITIAL 1024K NEXT 1024K MAXEXTENTS 2000), PARTITION PART_B01 VALUES LESS THAN (2,2003) TABLESPACE TBS_DATA PCTFREE 10 STORAGE (INITIAL 1024K NEXT 1024K MAXEXTENTS 2000),
Partition Part_a02 Values Less Than (3,2000) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024k MaxExtents 2000),
Partition Part_B02 Values Less Than (3,2003) TABLESPACE TBS_DATA PCTFREE 10 Storage (Initial 1024K Next 1024k MaxExtents 2000),
Partition Part_a12 Values Less Than (13,2000) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024k MaxExtents 2000),
Partition Part_b12 Values Less Than (13,2003) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024K Next 1024K MaxExtents 2000),
Partition Part_d12 Values Less Than (13,2020) TableSpace TBS_Data Pctfree 10 Storage (Initial 1024k Next 1024K MaxExtents 2000)
)
/
INSERT INTO SIMNG_PART1 VALUES (2000, 'A', 1);
INSERT INTO SIMNG_PART1 VALUES (2000, 'A', 2);
INSERT INTO SIMNG_PART1 VALUES (2000, 'A', 3);
INSERT INTO SIMNG_PART1 VALUES (2002, 'A', 1);
INSERT INTO SIMNG_PART1 VALUES (2002, 'A', 3);
INSERT INTO SIMNG_PART1 VALUES (2002, 'A', 9);
INSERT INTO SIMNG_PART1 VALUES (2019, 'A', 1);
COMMIT;
2. Problem
Think about two questions first:
There are several records in the SIMNG_PART: Part_b01 partition.
Several records in the SIMNG_PART1: Part_a01 partition.
the answer is:
LATN_ID NAME MON_ID
First
2002 a 3
2002 a 1
2002 a 9
the second
2000 a 1
2002 a 1
2019 a 1
The query is as follows:
SQL> SELECT * from SIMNG_PART Partition (Part_B01);
LATN_ID PNAME MON_ID
----------------------------------
2002 a 1
2002 a 3
2002 a 9
SQL> SELECT * from SIMNG_PART1 Partition (Part_a01); LATN_ID PNAME MON_ID
----------------------------------
2000 a 1
2002 a 1
2019 a 1
3. Conclusion
About the Range Partition partition,
First of all, the partition does not include the upper limit.
at the same time
For multiple columns to perform RANGE Partition
Follow this principle:
As long as you meet the nine conditions, put it in this partition, regardless of whether the N 1 column is satisfied!
......................................................................................
Thank JXC @ 花城
