Oracle分区表基础运维-05组合分区

IT小Chen 2021-04-13

287

组合分区

Composite Partitioning

Composite partitioning is a combination of the basic data distribution methods.

With composite partitioning, a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning. Figure 2-3 offers a graphical view of range-hash and range-list composite partitioning, as an example.

The types of composite partitioning are:

• Composite Range-Range Partitioning

• Composite Range-Hash Partitioning

• Composite Range-List Partitioning

• Composite List-Range Partitioning

• Composite List-Hash Partitioning

• Composite List-List Partitioning

• Composite Hash-Hash Partitioning

• Composite Hash-List Partitioning

• Composite Hash-Range Partitioning

一：组合分区range-list举例

--注意，此分区为范围分区

create table range_list_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by range (deal_date)
subpartition by list (area_code)
subpartition TEMPLATE
(subpartition p_591 values  (591),
subpartition p_592 values  (592),
subpartition p_593 values  (593),
subpartition p_594 values  (594),
subpartition p_595 values  (595),
subpartition p_596 values  (596),
subpartition p_597 values  (597),
subpartition p_598 values  (598),
subpartition p_599 values  (599),
subpartition p_other values (DEFAULT))
(
partition p1 values less than (TO_DATE('2019-05-01', 'YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2019-06-01', 'YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-07-01', 'YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-08-01', 'YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-09-01', 'YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-10-01', 'YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-11-01', 'YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-12-01', 'YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2020-01-01', 'YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2020-02-01', 'YYYY-MM-DD')),
partition p11 values less than (TO_DATE('2020-03-01', 'YYYY-MM-DD')),
partition p12 values less than (TO_DATE('2020-04-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
);

表已创建。

--以下是插入一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：

insert into range_list_part_tab
  (id, deal_date, area_code, contents)
  select rownum,
         to_date(to_char(sysdate - 365, 'J') +
                 TRUNC(DBMS_RANDOM.VALUE(0, 365)),
                 'J'),
         ceil(dbms_random.value(590, 599)),
         rpad('*', 400, '*')
    from dual
  connect by rownum <= 100000;
commit;

---查看当前用户下有哪些分区表

select TABLE_NAME from user_tables a where a.partitioned='YES';

---查看分区表名，分区名，表空间等信息

select table_name,
       partition_name,
       tablespace_name,
       high_value,
       subpartition_count
  from user_tab_partitions
 where table_name = 'RANGE_LIST_PART_TAB';

---查看子分区表名，分区名，表空间等信息

select table_name, partition_name, subpartition_name, tablespace_name
  from user_tab_subpartitions
 where table_name = 'RANGE_LIST_PART_TAB'
 order by 2, 3;

---查看P2分区数据

select * from RANGE_LIST_PART_TAB partition(P2);

---查看P2分区P_591子分区数据

select * from RANGE_LIST_PART_TAB subpartition(P2_P_591);

---查看对象信息

select count(*), object_type
  from dba_objects
 where object_name = 'RANGE_LIST_PART_TAB'
 group by object_type
 order by 2;

二：其他组合分区创建语句举例

---1

---range-range

---Creating Composite Range-Range Partitioned Tables

CREATE TABLE shipments
( order_id NUMBER NOT NULL
, order_date DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id NUMBER NOT NULL
, sales_amount NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
 ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))
 , SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))
 , SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
 )
);

---range-has

---Example 4-25 Creating a range-hash partitioned table with a subpartition template

CREATE TABLE employees_sub_template (department_id NUMBER(4) NOT NULL, 
 last_name VARCHAR2(25), job_id VARCHAR2(10)) 
 PARTITION BY RANGE(department_id) SUBPARTITION BY HASH(last_name)
 SUBPARTITION TEMPLATE
 (SUBPARTITION a TABLESPACE ts1,
 SUBPARTITION b TABLESPACE ts2,
 SUBPARTITION c TABLESPACE ts3,
 SUBPARTITION d TABLESPACE ts4
 )
 (PARTITION p1 VALUES LESS THAN (1000),
 PARTITION p2 VALUES LESS THAN (2000),
 PARTITION p3 VALUES LESS THAN (MAXVALUE)
 );

SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
  FROM DBA_TAB_SUBPARTITIONS
 WHERE TABLE_NAME = 'EMPLOYEEES_SUB_TEMPLATE'
 ORDER BY TABLESPACE_NAME;

---range-list

---Example 4-26 Creating a range-list partitioned table with a subpartition template

CREATE TABLE stripe_regional_sales
 ( deptno number, item_no varchar2(20),
 txn_date date, txn_amount number, state varchar2(2))
 PARTITION BY RANGE (txn_date)
 SUBPARTITION BY LIST (state)
 SUBPARTITION TEMPLATE 
 (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1,
 SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,
 SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,
 SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,
 SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,
 SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,
 SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7
 )
 (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
 PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
 PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
 PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
 );

---range-list

---Example 4-24 Creating a composite range-list partitioned table

CREATE TABLE quarterly_regional_sales
 (deptno number, item_no varchar2(20),
 txn_date date, txn_amount number, state varchar2(2))
 TABLESPACE ts4
 PARTITION BY RANGE (txn_date)
 SUBPARTITION BY LIST (state)
 (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
 (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
 SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
 SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
 SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
 SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
 SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
 ),
 PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
 (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
 SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
 SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
 SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
 SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
 SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
 ),
 PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
 (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
 SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
 SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
 SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
 SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
 SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
 ),
 PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
 (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
 SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
 SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
 SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
 SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
 SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
 )
 );

----2

---list-list

---Example 4-21 Creating a composite list-list partitioned table

CREATE TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (status)
( PARTITION p_northwest VALUES ('OR', 'WA')
 ( SUBPARTITION p_nw_bad VALUES ('B')
 , SUBPARTITION p_nw_average VALUES ('A')
 , SUBPARTITION p_nw_good VALUES ('G')
 )
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
 ( SUBPARTITION p_sw_bad VALUES ('B')
 , SUBPARTITION p_sw_average VALUES ('A')
 , SUBPARTITION p_sw_good VALUES ('G')
 )
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
 ( SUBPARTITION p_ne_bad VALUES ('B')
 , SUBPARTITION p_ne_average VALUES ('A')
 , SUBPARTITION p_ne_good VALUES ('G')
 )
, PARTITION p_southeast VALUES ('FL', 'GA')
 ( SUBPARTITION p_se_bad VALUES ('B')
 , SUBPARTITION p_se_average VALUES ('A')
 , SUBPARTITION p_se_good VALUES ('G')
 )
, PARTITION p_northcentral VALUES ('SD', 'WI')
 ( SUBPARTITION p_nc_bad VALUES ('B')
 , SUBPARTITION p_nc_average VALUES ('A')
 , SUBPARTITION p_nc_good VALUES ('G')
 )
, PARTITION p_southcentral VALUES ('OK', 'TX')
 ( SUBPARTITION p_sc_bad VALUES ('B')
 , SUBPARTITION p_sc_average VALUES ('A')
 , SUBPARTITION p_sc_good VALUES ('G')
 )
);

---list-range

---Example 4-22 Creating a composite list-range partitioned table

CREATE TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p_northwest VALUES ('OR', 'WA')
 ( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
 , SUBPARTITION p_nw_average VALUES LESS THAN (10000)
 , SUBPARTITION p_nw_high VALUES LESS THAN (100000)
 , SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
 ( SUBPARTITION p_sw_low VALUES LESS THAN (1000)
 , SUBPARTITION p_sw_average VALUES LESS THAN (10000)
 , SUBPARTITION p_sw_high VALUES LESS THAN (100000)
 , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
 ( SUBPARTITION p_ne_low VALUES LESS THAN (1000)
 , SUBPARTITION p_ne_average VALUES LESS THAN (10000)
 , SUBPARTITION p_ne_high VALUES LESS THAN (100000)
 , SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southeast VALUES ('FL', 'GA')
 ( SUBPARTITION p_se_low VALUES LESS THAN (1000)
 , SUBPARTITION p_se_average VALUES LESS THAN (10000)
 , SUBPARTITION p_se_high VALUES LESS THAN (100000)
 , SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_northcentral VALUES ('SD', 'WI')
 ( SUBPARTITION p_nc_low VALUES LESS THAN (1000)
 , SUBPARTITION p_nc_average VALUES LESS THAN (10000)
 , SUBPARTITION p_nc_high VALUES LESS THAN (100000)
 , SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southcentral VALUES ('OK', 'TX')
 ( SUBPARTITION p_sc_low VALUES LESS THAN (1000)
 , SUBPARTITION p_sc_average VALUES LESS THAN (10000)
 , SUBPARTITION p_sc_high VALUES LESS THAN (100000)
 , SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)
 )
) ENABLE ROW MOVEMENT;

---list-hash

---Example 4-20 Creating a composite list-hash partitioned table

CREATE TABLE accounts
( id NUMBER
 , account_number NUMBER
 , customer_id NUMBER
 , balance NUMBER
 , branch_id NUMBER
 , region VARCHAR(2)
 , status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8
( PARTITION p_northwest VALUES ('OR', 'WA')
 , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
 , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
 , PARTITION p_southeast VALUES ('FL', 'GA')
 , PARTITION p_northcentral VALUES ('SD', 'WI')
 , PARTITION p_southcentral VALUES ('OK', 'TX')
);

---3

---hash-hash

---Example 4-17 Creating a composite hash-hash partitioned table

CREATE TABLE departments_courses_hash (
 department_id NUMBER(4) NOT NULL, 
 department_name VARCHAR2(30),
 course_id NUMBER(4) NOT NULL)
 PARTITION BY HASH(department_id)
 SUBPARTITION BY HASH (course_id) SUBPARTITIONS 32 PARTITIONS 16;

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle分区表基础运维-05组合分区

评论