Oracle11g在分区方面做了很大的提高，不但新增了4种复合分区类型，还增加了虚拟列分区、系统分区、INTERVAL分区等功能。
9i开始，Oracle就包括了2种复合分区，RANGE-HASH和RANGE-LIST。在11g，Oracle一下就提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。
SQL
>
CREATE
TABLE
T_RANGE_RANGE
2
PARTITION
BY
RANGE (CREATED)
3
SUBPARTITION
BY
RANGE (LAST_DDL_TIME)
4
(
5
PARTITION P1
VALUES
LESS THAN (TO_DATE(
'
2007-9-1
'
,
'
YYYY-MM-DD
'
))
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
LESS THAN (TO_DATE(
'
2007-10-1
'
,
'
YYYY-MM-DD
'
))
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
LESS THAN (MAXVALUE)
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;
表已创建。
在没有RANGE-RANGE复合分区之前，RANGE分区指定两个分区列可以达到类似的效果。不过采用RANGE-RANGE分区逻辑上也清晰得多，而且可以利用很多处理子分区功能。
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行。
![浅谈---测试Native Windows Command与Native PowerShell Command哪个效率高[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)