1. 分区的好处。
- Reduce the possibility of data corruption in multiple partitions
- Back up and recover each partition independently
- Control the mapping of partitions to disk drives (important for balancing I/O load)
- Improve manageability, availability, and performance
2. 分区的方法。
- Range partitioning
- Hash partitioning
- List partitioning
- Composite range-hash partitioning
- Composite range-list partitioning
3.分区表的创建
（1）Range partitioning
SQL> CREATE TABLE range
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE (sale_year, sale_month, sale_day)
( PARTITION p1 VALUES LESS THAN (2007, 04, 01)
TABLESPACE tsreg_01,
PARTITION p2 VALUES LESS THAN (2007, 07, 01)
TABLESPACE tsreg_02,
PARTITION p3 VALUES LESS THAN (2007, 10, 01)
TABLESPACE tsreg_03,
PARTITION p4 VALUES LESS THAN (2008, 01, 01)
TABLESPACE tsreg_04 ); 2 3 4 5 6 7 8 9 10 11 12 13 14
Table created.
(2)Hash Partitioning
CREATE TABLE scubagear
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (tsreg_01,tsreg_02, tsreg_03, tsreg_04);
4.为分区表添加分区
SQL>
ALTER TABLE hash
ADD PARTITION sys_p9 TABLESPACE tsreg_05;SQL> 2
Table altered.
ALTER TABLE quarterly_regional_sales
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);5.move partition
SQL> select segment_name,segment_type,partition_name,tablespace_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
TEST TABLE USERS .125
PLAN_TABLE TABLE USERS .0625
DEMO TABLE USERS .0625
SALES_1 TABLE USERS .0625
SALES_3 TABLE USERS .0625
SALES TABLE PARTITION SALES_MON_1 TSREG_01 2
SALES TABLE PARTITION SALES_MON_2 TSREG_02 2
SALES TABLE PARTITION SALES_MON_3 TSREG_03 3
SALES TABLE PARTITION SALES_MON_4 TSREG_04 .0625
SALES TABLE PARTITION SALES_MON_5 TSREG_05 .0625
SALES TABLE PARTITION SALES_MON_6 TSREG_06 .0625
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
SALES TABLE PARTITION SALES_MON_7 TSREG_07 .0625
SALES TABLE PARTITION SALES_MON_8 TSREG_08 .0625
SALES TABLE PARTITION SALES_MON_9 TSREG_09 .0625
SALES TABLE PARTITION SALES_MON_10 TSREG_10 .0625
SALES TABLE PARTITION SALES_MON_11 TSREG_11 .0625
SALES TABLE PARTITION SALES_MON_12 TSREG_12 .0625
SALES TABLE PARTITION SALES_BAD_MON TSREG_BAD .0625
RANGE TABLE PARTITION P1 TSREG_01 .0625
RANGE TABLE PARTITION P2 TSREG_02 .0625
RANGE TABLE PARTITION P3 TSREG_03 .0625
RANGE TABLE PARTITION P4 TSREG_04 .0625
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
HASH TABLE PARTITION SYS_P5 TSREG_01 10
HASH TABLE PARTITION SYS_P6 TSREG_02 10
HASH TABLE PARTITION SYS_P7 TSREG_03 10
HASH TABLE PARTITION SYS_P8 TSREG_04 10
IDX_TEST INDEX IDX .125
IDX_SALES INDEX USERS 6
28 rows selected.
SQL> alter table hash move partition sys_p6 PARALLEL (DEGREE 2);
Table altered.
SQL> c/6/7
1* alter table hash move partition sys_p7 PARALLEL (DEGREE 2)
SQL> /
Table altered.
SQL> c/7/8
1* alter table hash move partition sys_p8 PARALLEL (DEGREE 2)
SQL> /
Table altered.
SQL> select segment_name,segment_type,partition_name,tablespace_name,bytes/1024/1024 from user_segments;
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
TEST TABLE USERS .125
PLAN_TABLE TABLE USERS .0625
DEMO TABLE USERS .0625
SALES_1 TABLE USERS .0625
SALES_3 TABLE USERS .0625
SALES TABLE PARTITION SALES_MON_1 TSREG_01 2
SALES TABLE PARTITION SALES_MON_2 TSREG_02 2
SALES TABLE PARTITION SALES_MON_3 TSREG_03 3
SALES TABLE PARTITION SALES_MON_4 TSREG_04 .0625
SALES TABLE PARTITION SALES_MON_5 TSREG_05 .0625
SALES TABLE PARTITION SALES_MON_6 TSREG_06 .0625
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
SALES TABLE PARTITION SALES_MON_7 TSREG_07 .0625
SALES TABLE PARTITION SALES_MON_8 TSREG_08 .0625
SALES TABLE PARTITION SALES_MON_9 TSREG_09 .0625
SALES TABLE PARTITION SALES_MON_10 TSREG_10 .0625
SALES TABLE PARTITION SALES_MON_11 TSREG_11 .0625
SALES TABLE PARTITION SALES_MON_12 TSREG_12 .0625
SALES TABLE PARTITION SALES_BAD_MON TSREG_BAD .0625
RANGE TABLE PARTITION P1 TSREG_01 .0625
RANGE TABLE PARTITION P2 TSREG_02 .0625
RANGE TABLE PARTITION P3 TSREG_03 .0625
RANGE TABLE PARTITION P4 TSREG_04 .0625
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME BYTES/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------------
HASH TABLE PARTITION SYS_P5 TSREG_01 .0625
HASH TABLE PARTITION SYS_P6 TSREG_02 .0625
HASH TABLE PARTITION SYS_P7 TSREG_03 .0625
HASH TABLE PARTITION SYS_P8 TSREG_04 .0625
HASH TABLE PARTITION SYS_P9 TSREG_05 .0625
IDX_TEST INDEX IDX .125
IDX_SALES INDEX USERS 6
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-232340/ ，如需转载，请注明出处，否则将追究法律责任。
