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