[bbk2901] 第57集 Chapter 14Using 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 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
);

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.

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 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