表分区（三）

列表加散列分区





CREATE TABLE LIST_HASH_TAB
(
ID NUMBER
, CODE VARCHAR2(10)
, DESCRIPTION VARCHAR2(50)
, CREATED_DATE DATE
)
PARTITION BY LIST (CODE)
SUBPARTITION BY HASH (ID)
(
PARTITION PART_AA VALUES (‘AA’)
(
SUBPARTITION PART_AA_01
, SUBPARTITION PART_AA_02
)
, PARTITION PART_BB VALUES (‘BB’)
(
SUBPARTITION PART_BB_01
, SUBPARTITION PART_BB_02
)
);



插入数据测试
DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 … 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := ‘BB’;
ELSE
l_code := ‘AA’;
END IF;

INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' || i || ' ' || l_code, SYSDATE);

END LOOP;
COMMIT;
END;
/


EXEC DBMS_STATS.gather_table_stats(USER, ‘LIST_HASH_TAB’, granularity=>‘ALL’);

user_tab_subpartitions：显示当前用户拥有的所有分区对象的子分区的此类信息
table_name,：表名
PARTITION_NAME：分区名称
SUBPARTITION_NAME：子分区的名称
NUM_ROWS：子分区中的行数
SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;

间格分区
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,‘MONTH’))
(
PARTITION part_01 values LESS THAN (TO_DATE(‘01-03-2020’,‘DD-MM-YYYY’))
);

EXEC DBMS_STATS.gather_table_stats(USER, ‘INTERVAL_TAB’);

HIGH_VALUE：分区绑定值表达式
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
where table_name=‘INTERVAL_TAB’
ORDER BY table_name, partition_name;

日期小于设置的日期，数据将被放置在现有分区中，并且不会创建新分区。
INSERT INTO interval_tab VALUES (1, ‘ONE’, ‘One’, TO_DATE(‘16-01-2020’, ‘DD-MM-YYYY’));
INSERT INTO interval_tab VALUES (2, ‘TWO’, ‘Two’, TO_DATE(‘31-01-2020’, ‘DD-MM-YYYY’));
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, ‘INTERVAL_TAB’);

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
where table_name=‘INTERVAL_TAB’
ORDER BY table_name, partition_name;


添加的数据超出了现有分区的范围，则会创建一个新分区。
INSERT INTO interval_tab VALUES (1, ‘ONE’, ‘One’, TO_DATE(‘01-03-2020’, ‘DD-MM-YYYY’));
INSERT INTO interval_tab VALUES (1, ‘ONE’, ‘One’, TO_DATE(‘31-03-2020’, ‘DD-MM-YYYY’));
COMMIT;


EXEC DBMS_STATS.gather_table_stats(USER, ‘INTERVAL_TAB’);


INSERT INTO interval_tab VALUES (5, ‘ONE’, ‘One’, TO_DATE(‘01-04-2020’, ‘DD-MM-YYYY’));
INSERT INTO interval_tab VALUES (5, ‘ONE’, ‘One’, TO_DATE(‘01-05-2020’, ‘DD-MM-YYYY’));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, ‘INTERVAL_TAB’);



引用分区
创建父表





CREATE TABLE PARENT_TAB
(
ID NUMBER NOT NULL
, CODE VARCHAR2(10) NOT NULL
, DESCRIPTION VARCHAR2(50)
, CREATED_DATE DATE
, CONSTRAINT PARENT_TAB_PK PRIMARY KEY
(
ID
)
ENABLE
)
PARTITION BY RANGE (CREATED_DATE)
(
PARTITION PART_2020 VALUES LESS THAN (TO_DATE(‘01-12-2020’,‘DD-MM-YYYY’))
, PARTITION PART_2021 VALUES LESS THAN (TO_DATE(‘01-12-2021’,‘DD-MM-YYYY’))
);

创建子表




CREATE TABLE CHILD_TAB
(
ID NUMBER NOT NULL
, PARENT_TAB_ID NUMBER NOT NULL
, CODE VARCHAR2(10)
, DESCRIPTION VARCHAR2(50)
, CREATED_DATE DATE
, CONSTRAINT CHILD_TAB_PK PRIMARY KEY
(
ID
)
ENABLE
, CONSTRAINT CHILD_PARENT_TAB_FK FOREIGN KEY
(
ID
)
REFERENCES PARENT_TAB
(
ID
)
ENABLE
)
PARTITION BY REFERENCE (CHILD_PARENT_TAB_FK);





INSERT INTO parent_tab VALUES (1, ‘ONE’, ‘1 ONE’, SYSDATE);
INSERT INTO parent_tab VALUES (2, ‘TWO’, ‘2 TWO’, SYSDATE);
INSERT INTO parent_tab VALUES (4, ‘THREE’, ‘3 THREE’, ADD_MONTHS(SYSDATE,11));

INSERT INTO child_tab VALUES (1, 1, ‘ONE’, ‘1 1 ONE’, SYSDATE);
INSERT INTO child_tab VALUES (2, 3, ‘TWO’, ‘2 3 TWO’, SYSDATE);
INSERT INTO child_tab VALUES (4, 3, ‘THREE’, ‘3 3 THREE’, SYSDATE);
COMMIT;


EXEC DBMS_STATS.gather_table_stats(USER, ‘PARENT_TAB’);
EXEC DBMS_STATS.gather_table_stats(USER, ‘CHILD_TAB’);

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;