[bbk2901] 第57集 - Chapter 14-Using Oracle Dasta Storage Structures Efficiently(03)
Partitioning Methods
The following partitioning methods are available:
- Range
- Hash
- List
- Composite
按年度分区，2009年、2010年、...每年一张表；
自oracle 9i以后，每个表空间可以指定不同的block_size，但是在分区表中，每个不同的分区必须使用相同的db_block_size;
CREATE TABLE sales ( acct_no NUMBER(5), person VARCHAR2(30), sales_amount NUMBER(8), week_no NUMBER(2)--a ) PARTITION BY RANGE(week_no)--b ( PARTITION p1 VALUES LESS THAN (4) TABLESPACE data0,--c PARTITION p2 VALUES LESS THAN (8) TABLESPACE data1, ... PARTITION p13 VALUES LESS THAN (53) TABLESPACE data12, );
a:The partition key is week_no
b:VALUES LESS THAN must be specified as a literal
c:Physical attributes can be set per partition
Example:Create Partition table by range
create partition table
CREATE TABLE range1 ( rk date, data varchar2(20) ) PARTITION BY RANGE(rk) ( PARTITION p1 VALUES LESS THAN (to_date('01/01/2014','dd/mm/yyyy')) TABLESPACE ts0, PARTITION p2 VALUES LESS THAN (to_date('01/01/2015','dd/mm/yyyy')) TABLESPACE ts1, PARTITION p3 VALUES LESS THAN (to_date('01/01/2016','dd/mm/yyyy')) TABLESPACE ts2, PARTITION p4 VALUES LESS THAN (to_date('01/01/2017','dd/mm/yyyy')) TABLESPACE ts3 );
view partition info from user_objects
SQL> @pt_range_02.sql Table created. SQL> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- RANGE1 TABLE RANGE1 TABLE PARTITION RANGE1 TABLE PARTITION RANGE1 TABLE PARTITION RANGE1 TABLE PARTITION 5 rows selected.
View Partition info from user_segments
SQL> col segment_name format a30 SQL> SELECT segment_name,partition_name,segment_type FROM user_segments; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE ------------------------------ ------------------------------ ------------------ RANGE1 P1 TABLE PARTITION RANGE1 P2 TABLE PARTITION RANGE1 P3 TABLE PARTITION RANGE1 P4 TABLE PARTITION
insert into data to RANGE1
INSERT INTO RANGE1 VALUES(to_date('09/05/2013','dd/mm/yyyy'),'arcerzhang test date'); INSERT INTO RANGE1 VALUES(to_date('09/05/2014','dd/mm/yyyy'),'arcerzhang test date'); INSERT INTO RANGE1 VALUES(to_date('09/05/2015','dd/mm/yyyy'),'arcerzhang test date'); INSERT INTO RANGE1 VALUES(to_date('09/05/2016','dd/mm/yyyy'),'arcerzhang test date'); 1 row created. 1 row created. 1 row created. 1 row created. SQL> commit; Commit complete. SQL> select * from range1; RK DATA ------------------ -------------------- 09-MAY-13 arcerzhang test date 09-MAY-14 arcerzhang test date 09-MAY-15 arcerzhang test date 09-MAY-16 arcerzhang test date SQL> select * from range1 partition(p1); RK DATA ------------------ -------------------- 09-MAY-13 arcerzhang test date SQL> select * from range1 partition(p2); RK DATA ------------------ -------------------- 09-MAY-14 arcerzhang test date SQL> select * from range1 partition(p3); RK DATA ------------------ -------------------- 09-MAY-15 arcerzhang test date SQL> select * from range1 partition(p4); RK DATA ------------------ -------------------- 09-MAY-16 arcerzhang test date
以上数据插入都符合条件限制，如果插入一条大于01/01/2017的数据，将会报错,如下:
SQL> INSERT INTO RANGE1 VALUES(to_date('09/05/2017','dd/mm/yyyy'),'arcerzhang test date'); INSERT INTO RANGE1 VALUES(to_date('09/05/2017','dd/mm/yyyy'),'arcerzhang test date') * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
解决办法:
1、创建一个表空间，用于存放不在分区部分之内的数据
SQL> CREATE TABLESPACE TS4 DATAFILE '/RealData/oradata/DATACENTER/ts4_01.dbf' SIZE 200M 2 EXTENT MANAGEMENT LOCAL UNIFORM 3 SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. SQL> select tablespace_name,contents from dba_tablespaces; TABLESPACE_NAME CONTENTS ------------------------------ --------- SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT TAB_U2 PERMANENT TAB_U3 PERMANENT TS0 PERMANENT TS1 PERMANENT TS2 PERMANENT TABLESPACE_NAME CONTENTS ------------------------------ --------- TS3 PERMANENT TS4 PERMANENT 13 rows selected.
2、重新插入上述非法数据
SQL> cl@pt_range_03.sql Table altered. SQL> INSERT INTO RANGE1 VALUES(to_date('09/05/2017','dd/mm/yyyy'),'arcerzhang test date'); 1 row created. SQL> commit; Commit complete SQL> select * from range1 partition(p1); RK DATA ------------------ -------------------- 09-MAY-13 arcerzhang test date SQL> select * from range1 partition(p2); RK DATA ------------------ -------------------- 09-MAY-14 arcerzhang test date SQL> select * from range1 partition(p3); RK DATA ------------------ -------------------- 09-MAY-15 arcerzhang test date SQL> select * from range1 partition(p4); RK DATA ------------------ -------------------- 09-MAY-16 arcerzhang test date SQL> select * from range1 partition(p5); RK DATA ------------------ -------------------- 09-MAY-17 arcerzhang test date SQL> INSERT INTO RANGE1 VALUES(to_date('09/05/2099','dd/mm/yyyy'),'arcerzhang test date'); 1 row created. SQL> commit; Commit complete. SQL> select * from range1 partition(p5); RK DATA ------------------ -------------------- 09-MAY-17 arcerzhang test date 09-MAY-99 arcerzhang test date
Partitioning Table相关资料
<<tom 大师书籍第13章 Partitioning>>
<<VLDB and Partitioning Guide>>VLDB->Very Large Database
