oracle 添加复合分区,Oracle11新特性：分区功能增强-Oracle新增复合分区 (转载)

SQL

>

CREATE

TABLE

T_LIST_RANGE

2

PARTITION

BY

LIST (OWNER)

3

SUBPARTITION

BY

RANGE (CREATED)

4

(

5

PARTITION P1

VALUES

(

'

SYS

'

,

'

SYSTEM

'

)

6

(

7

SUBPARTITION P1_SP1

VALUES

LESS THAN (TO_DATE(

'

2007-9-1

'

,

'

YYYY-MM-DD

'

)),

8

SUBPARTITION P1_SP2

VALUES

LESS THAN (TO_DATE(

'

2007-10-1

'

,

'

YYYY-MM-DD

'

)),

9

SUBPARTITION P1_SP3

VALUES

LESS THAN (MAXVALUE)

10

),

11

PARTITION P2

VALUES

(

'

YANGTK

'

)

12

(

13

SUBPARTITION P2_SP1

VALUES

LESS THAN (TO_DATE(

'

2007-9-1

'

,

'

YYYY-MM-DD

'

)),

14

SUBPARTITION P2_SP2

VALUES

LESS THAN (TO_DATE(

'

2007-10-1

'

,

'

YYYY-MM-DD

'

)),

15

SUBPARTITION P2_SP3

VALUES

LESS THAN (MAXVALUE)

16

),

17

PARTITION P3

VALUES

(

DEFAULT

)

18

(

19

SUBPARTITION P3_SP1

VALUES

LESS THAN (TO_DATE(

'

2007-9-1

'

,

'

YYYY-MM-DD

'

)),

20

SUBPARTITION P3_SP2

VALUES

LESS THAN (TO_DATE(

'

2007-10-1

'

,

'

YYYY-MM-DD

'

)),

21

SUBPARTITION P3_SP3

VALUES

LESS THAN (MAXVALUE)

22

)

23

)

24

AS

SELECT

*

FROM

DBA_OBJECTS; 表已创建。 SQL

>

CREATE

TABLE

T_LIST_HASH

2

PARTITION

BY

LIST (OWNER)

3

SUBPARTITION

BY

HASH (

OBJECT_ID

)

4

SUBPARTITION TEMPLATE

5

(

6

SUBPARTITION SP1,

7

SUBPARTITION SP2,

8

SUBPARTITION SP3,

9

SUBPARTITION SP4

10

)

11

(

12

PARTITION P1

VALUES

(

'

SYS

'

,

'

SYSTEM

'

),

13

PARTITION P2

VALUES

(

'

YANGTK

'

),

14

PARTITION P3

VALUES

(

DEFAULT

)

15

)

16

AS

SELECT

*

FROM

DBA_OBJECTS; 表已创建。 SQL

>

CREATE

TABLE

T_LIST_LIST

2

PARTITION

BY

LIST (OWNER)

3

SUBPARTITION

BY

LIST (OBJECT_TYPE)

4

(

5

PARTITION P1

VALUES

(

'

SYS

'

,

'

SYSTEM

'

)

6

(

7

SUBPARTITION P1_SP1

VALUES

(

'

TABLE

'

,

'

INDEX

'

),

8

SUBPARTITION P1_SP2

VALUES

(

'

VIEW

'

,

'

SYNONYM

'

),

9

SUBPARTITION P1_SP3

VALUES

(

DEFAULT

)

10

),

11

PARTITION P2

VALUES

(

'

YANGTK

'

)

12

(

13

SUBPARTITION P2_SP1

VALUES

(

'

TABLE

'

,

'

INDEX

'

),

14

SUBPARTITION P2_SP2

VALUES

(

'

VIEW

'

,

'

SYNONYM

'

),

15

SUBPARTITION P2_SP3

VALUES

(

DEFAULT

)

16

),

17

PARTITION P3

VALUES

(

DEFAULT

)

18

(

19

SUBPARTITION P3_SP1

VALUES

(

'

TABLE

'

,

'

INDEX

'

),

20

SUBPARTITION P3_SP2

VALUES

(

'

VIEW

'

,

'

SYNONYM

'

),

21

SUBPARTITION P3_SP3

VALUES

(

DEFAULT

)

22

)

23

)

24

AS

SELECT

*

FROM

DBA_OBJECTS; 表已创建。 三种LIST开头的复合分区的加入，使得Oracle支持除HASH开头之外的全部6种复合分区。这使得在设计复合分区的时候有更多的选择可供使用。 SQL

>

SELECT

TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT

2

FROM

USER_PART_TABLES; TABLE_NAME PARTITION SUBPART PARTITION_COUNT

--

---------------------------- --------- ------- ---------------

T_LIST_HASH LIST HASH

3

T_LIST_LIST LIST LIST

3

T_LIST_RANGE LIST RANGE

3

T_RANGE_RANGE RANGE RANGE

3

SQL

>

SELECT

TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME

FROM

USER_TAB_SUBPARTITIONS

2

ORDER

BY

1

,

2

,

3

; TABLE_NAME PARTITION_NAME SUBPARTITION_NAME

--

---------------------------- ------------------------------ -----------------

T_LIST_HASH P1 P1_SP1 T_LIST_HASH P1 P1_SP2 T_LIST_HASH P1 P1_SP3 T_LIST_HASH P1 P1_SP4 T_LIST_HASH P2 P2_SP1 T_LIST_HASH P2 P2_SP2 T_LIST_HASH P2 P2_SP3 T_LIST_HASH P2 P2_SP4 T_LIST_HASH P3 P3_SP1 T_LIST_HASH P3 P3_SP2 T_LIST_HASH P3 P3_SP3 T_LIST_HASH P3 P3_SP4 T_LIST_LIST P1 P1_SP1 T_LIST_LIST P1 P1_SP2 T_LIST_LIST P1 P1_SP3 T_LIST_LIST P2 P2_SP1 T_LIST_LIST P2 P2_SP2 T_LIST_LIST P2 P2_SP3 T_LIST_LIST P3 P3_SP1 T_LIST_LIST P3 P3_SP2 T_LIST_LIST P3 P3_SP3 T_LIST_RANGE P1 P1_SP1 T_LIST_RANGE P1 P1_SP2 T_LIST_RANGE P1 P1_SP3 T_LIST_RANGE P2 P2_SP1 T_LIST_RANGE P2 P2_SP2 T_LIST_RANGE P2 P2_SP3 T_LIST_RANGE P3 P3_SP1 T_LIST_RANGE P3 P3_SP2 T_LIST_RANGE P3 P3_SP3 T_RANGE_RANGE P1 P1_SP1 T_RANGE_RANGE P1 P1_SP2 T_RANGE_RANGE P1 P1_SP3 T_RANGE_RANGE P2 P2_SP1 T_RANGE_RANGE P2 P2_SP2 T_RANGE_RANGE P2 P2_SP3 T_RANGE_RANGE P3 P3_SP1 T_RANGE_RANGE P3 P3_SP2 T_RANGE_RANGE P3 P3_SP3 已选择39行。