Oracle分割槽表基礎運維-05組合分割槽

Oracle 分割槽表基礎運維 -05 組合分割槽

組合分割槽

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;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！!

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2692493/，如需轉載，請註明出處，否則將追究法律責任。