知識星球
老張拉呱：thomas zhang，甲骨文雲平臺事業部資深技術顧問，2008年加入甲骨文公司資料庫諮詢部門，10+年甲骨文解決方案諮詢支援經驗，資深系統工程師、Oracle OCM認證專家，具有豐富的Cloud /IT專案經驗。目前主要負責甲骨文中國北方區（醫院/衛生、交通、製造、教育、政府、證券、媒體、金融、零售等行業）客戶的資料庫、中介軟體、IaaS/PaaS、整合系統等相關技術解決方案諮詢工作。
簽名：我為人人，人人為我，三人行，必有我師。
新浪微博： http://weibo.com/tomszrp
從Oracle Database 19c開始，Oracle資料庫支援Hybridpartitioned tables，也就是混合分割槽表，進一步擴充套件了Oracle分割槽技術。這裡的混合指的是資料的分佈，一些分割槽可以位於資料庫中，另一些可以是位於資料庫外部的檔案（比如作業系統檔案或Hadoop Distributed File System (HDFS) 檔案）。這個特性的出現，其實一點也不奇怪，因為從12.2開始就支援了外部表分割槽、只讀分割槽，在19c中只是將內部分割槽（internal partitions）與Oracle外部分割槽（external partitions）特性結合起來，形成一個更通用的分割槽，稱為混合分割槽表。
混合分割槽的現實意義還是比較明顯的，首先透過混合分割槽可以輕鬆地將內部（駐留在Oracle表空間中—internal partitions）和外部的資料（external partitions）整合到單個分割槽表中，其次可以方便地將非活躍資料移動到外部檔案，在而降低儲存成本的同時也更加方便資料交換。
1）混合分割槽表支援外部分割槽的所有現有外部表型別:
•ORACLE_DATAPUMP
•ORACLE_LOADER
•ORACLE_HDFS
•ORACLE_HIVE
2）所有外部表引數均適用於混合分割槽表的外部分割槽。
3）混合分割槽表可以跨內部、外部分割槽使用基於分割槽的最佳化技術，典型的比如：靜態分割槽修剪、動態分割槽修剪、布隆修剪
4）混合分割槽表支援的操作
（1）當前僅支援建立single-level的range和list分割槽，其中只有single-level LIST分割槽支援HIVE
（2）可以使用alter table …DDLs操作，比如ADD,DROP和RENAME partitions
（3）可以在分割槽級別修改external partitions的external data sources 位置
（4）可以將既有的內部分割槽表修改為混合分割槽表
（5）可以修改現有的location到empty location形成一個空的external partition
（6）可以針對內部分割槽建立global partial non-unique indexes
（7）可以針對內部分割槽建立materialized views
(8) 可以建立包含外部分割槽的materialized views，前提是QUERY_REWRITE_INTEGRITY必須為STALE_TOLERATED樣式
（9）DML操作只能針對混合分割槽表的內部分割槽
(10) Validatingwith ANALYZE TABLE … VALIDATE STRUCTURE on internal partitions only on hybridpartitioned tables
(11)Alteringan existing hybrid partitioned table with no external partitions to apartitioned table with internal partitions only（12）Anexternal partition can be exchanged with an external nonpartitioned table. Alsoan internal partition can be exchanged with an internal nonpartitioned table.
註意：1）不支援儲存在外部分割槽中的資料強制約束，例如不能在混合分割槽表上強制主鍵或外來鍵約束。在混合分割槽表上，只支援RELYDISABLE約束，要使用基於這種約束的最佳化特性，需要配合會話引數QUERY_REWRITE_INTEGRITY（設定為TRUSTED或STALE_TOLERATED）。
2）在混合分割槽表級別定義的AutomaticData Optimization (ADO)策略隻影響內部分割槽
◊Hybrid partitioned tables-混合分割槽表的限制
（1）Restrictionsthat apply to external tables also apply to hybrid partitioned tables unlessexplicitly noted
（2）不支援REFERENCE和SYSTEM分割槽方法
（3）No uniqueindexes or global unique indexes. Only partial indexes are allowed and uniqueindexes cannot be partial.
（4）Attributeclustering (CLUSTERING clause) is not allowed
（5）DMLoperations only on internal partitions of a hybrid partitioned table (externalpartitions are treated as read-only partitions)
（6）In-memorydefined on the table level only has an effect on internal partitions of thehybrid partitioned table
（7）No columndefault value
（8）Invisiblecolumns are not allowed
（9）TheCELLMEMORY clause is not allowed
（10）SPLIT,MERGE, and MOVE maintenance operations are not allowed on internal partitions
（11）不支援LOB, LONG和ADT型別
（12）只允許RELYconstraints
◊Hybrid partitioned tables-混合分割槽表初體驗
1）準備測試資料
sale_2016.txt
region,time_id,amount
EAST,20160101,6000
EAST,20160102,3000
EAST,20160103,9012
EAST,20160104,2450
EAST,20160105,6709
SOUTH,20160101,4000
SOUTH,20160102,2120
SOUTH,20160103,6300
SOUTH,20160104,3850
SOUTH,20160105,2090
WEST,20160101,2467
WEST,20160102,2140
WEST,20160103,5300
WEST,20160104,2470
WEST,20160105,4080
NORTH,20160101,2600
NORTH,20160102,1300
NORTH,20160103,1250
NORTH,20160104,4350
NORTH,20160105,3190
sale_2017.txt
region,time_id,amount
EAST,20170101,8000
EAST,20170102,7000
EAST,20170103,6500
EAST,20170104,3450
EAST,20170105,9000
SOUTH,20170101,2000
SOUTH,20170102,3120
SOUTH,20170103,2300
SOUTH,20170104,5850
SOUTH,20170105,1900
WEST,20170101,3400
WEST,20170102,2400
WEST,20170103,5900
WEST,20170104,5450
WEST,20170105,1780
NORTH,20170101,2000
NORTH,20170102,1000
NORTH,20170103,3000
NORTH,20170104,2350
NORTH,20170105,2190
sale_2018.txt
region,time_id,amount
EAST,20180101,2100
EAST,20180102,7800
EAST,20180103,6900
EAST,20180104,9450
EAST,20180105,9700
SOUTH,20180101,1300
SOUTH,20180102,2120
SOUTH,20180103,6300
SOUTH,20180104,2850
SOUTH,20180105,7900
WEST,20180101,3800
WEST,20180102,2600
WEST,20180103,5200
WEST,20180104,5250
WEST,20180105,2980
NORTH,20180101,2120
NORTH,20180102,1230
NORTH,20180103,3500
NORTH,20180104,2050
NORTH,20180105,1060
sale_2018.sql
insert into hybrid_test values(‘EAST’, to_date(‘20190101′,’yyyy-mm-dd’),1032);
insert into hybrid_test values(‘EAST’, to_date(‘20190102′,’yyyy-mm-dd’),2371);
commit;
2）定義DIRECTORY
CDB$ROOT@SYS>conn zrp/zrp@pdb1
PDB1@ZRP>CREATEDIRECTORY sales_data as ‘/u01/app/oracle/oradata/extfiles’;
PDB1@ZRP>CREATEDIRECTORY sales_data_2016 as ‘/u01/app/oracle/oradata/extfiles/2016’;
PDB1@ZRP>CREATEDIRECTORY sales_data_2017 as ‘/u01/app/oracle/oradata/extfiles/2017’;
3）建立Hybridpartitioned tables-混合分割槽表
PDB1@ZRP>CREATE TABLE hybrid_test
2 (
3 region varchar2(6) NOT NULL,
4 time_id DATE NOT NULL,
5 amount NUMBER(10,2)
6 )
7 EXTERNAL PARTITION ATTRIBUTES –必須加這個子句宣告
8 (
9 TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY sales_data
11 ACCESS PARAMETERS( FIELDS TERMINATED BY ‘,’
12 (region,time_id DATE ‘yyyy-mm-dd’,amount)
13 )
14 REJECT LIMIT UNLIMITED
15 )
16 PARTITION BY RANGE (time_id)
17 ( PARTITION sales_2015 VALUES LESS THAN (TO_DATE(‘2016-01-01′,’yyyy-mm-dd’)) EXTERNAL, –空的外部分割槽
18 PARTITION sales_2016 VALUES LESS THAN (TO_DATE(‘2017-01-01′,’yyyy-mm-dd’))
19 EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION (‘sales_2016.txt’),
20 PARTITION sales_2017 VALUES LESS THAN (TO_DATE(‘2018-01-01′,’yyyy-mm-dd’))
21 EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION (‘sales_2017.txt’),
22 PARTITION sales_2018 VALUES LESS THAN (TO_DATE(‘2019-01-01′,’yyyy-mm-dd’)) EXTERNAL LOCATION (‘sales_2018.txt’),
23 PARTITION sales_2019 VALUES LESS THAN (TO_DATE(‘2020-01-01′,’yyyy-mm-dd’)) –內部分割槽
24 );
Table created.
##透過dba_tables資料字典的hybrid欄位可以看出是否是混合分割槽
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;
TABLE_NAME PAR HYB
——————– — —
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner=’ZRP’;
PDB1@ZRP>
##插入幾行資料（只能插入到內部分割槽（internal partitions），我這裡是sales_2019).
PDB1@ZRP>select * from hybrid_test partition (sales_2015);
no rows selected
PDB1@ZRP>insert into hybrid_test values(‘EAST’, to_date(‘20190101′,’yyyy-mm-dd’),1032);
1 row created.
PDB1@ZRP>insert into hybrid_test values(‘EAST’, to_date(‘20190102′,’yyyy-mm-dd’),2371);
1 row created.
PDB1@ZRP>commit;
Commit complete.
PDB1@ZRP>
##檢視各分割槽的資料
PDB1@ZRP>select * from hybrid_test partition (sales_2019);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2019-01-01 00:00:00 1032
EAST 2019-01-02 00:00:00 2371
PDB1@ZRP>select * from hybrid_test partition (sales_2015);
no rows selected
PDB1@ZRP>select * from hybrid_test partition (sales_2016);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2016-01-01 00:00:00 6000
EAST 2016-01-02 00:00:00 3000
EAST 2016-01-03 00:00:00 9012
EAST 2016-01-04 00:00:00 2450
EAST 2016-01-05 00:00:00 6709
SOUTH 2016-01-01 00:00:00 4000
SOUTH 2016-01-02 00:00:00 2120
SOUTH 2016-01-03 00:00:00 6300
SOUTH 2016-01-04 00:00:00 3850
SOUTH 2016-01-05 00:00:00 2090
WEST 2016-01-01 00:00:00 2467
WEST 2016-01-02 00:00:00 2140
WEST 2016-01-03 00:00:00 5300
WEST 2016-01-04 00:00:00 2470
WEST 2016-01-05 00:00:00 4080
NORTH 2016-01-01 00:00:00 2600
NORTH 2016-01-02 00:00:00 1300
NORTH 2016-01-03 00:00:00 1250
NORTH 2016-01-04 00:00:00 4350
NORTH 2016-01-05 00:00:00 3190
20 rows selected.
PDB1@ZRP>select * from hybrid_test partition (sales_2017);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2017-01-01 00:00:00 8000
EAST 2017-01-02 00:00:00 7000
EAST 2017-01-03 00:00:00 6500
EAST 2017-01-04 00:00:00 3450
EAST 2017-01-05 00:00:00 9000
SOUTH 2017-01-01 00:00:00 2000
SOUTH 2017-01-02 00:00:00 3120
SOUTH 2017-01-03 00:00:00 2300
SOUTH 2017-01-04 00:00:00 5850
SOUTH 2017-01-05 00:00:00 1900
WEST 2017-01-01 00:00:00 3400
WEST 2017-01-02 00:00:00 2400
WEST 2017-01-03 00:00:00 5900
WEST 2017-01-04 00:00:00 5450
WEST 2017-01-05 00:00:00 1780
NORTH 2017-01-01 00:00:00 2000
NORTH 2017-01-02 00:00:00 1000
NORTH 2017-01-03 00:00:00 3000
NORTH 2017-01-04 00:00:00 2350
NORTH 2017-01-05 00:00:00 2190
20 rows selected.
PDB1@ZRP>select * from hybrid_test partition (sales_2018);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2018-01-01 00:00:00 2100
EAST 2018-01-02 00:00:00 7800
EAST 2018-01-03 00:00:00 6900
EAST 2018-01-04 00:00:00 9450
EAST 2018-01-05 00:00:00 9700
SOUTH 2018-01-01 00:00:00 1300
SOUTH 2018-01-02 00:00:00 2120
SOUTH 2018-01-03 00:00:00 6300
SOUTH 2018-01-04 00:00:00 2850
SOUTH 2018-01-05 00:00:00 7900
WEST 2018-01-01 00:00:00 3800
WEST 2018-01-02 00:00:00 2600
WEST 2018-01-03 00:00:00 5200
WEST 2018-01-04 00:00:00 5250
WEST 2018-01-05 00:00:00 2980
NORTH 2018-01-01 00:00:00 2120
NORTH 2018-01-02 00:00:00 1230
NORTH 2018-01-03 00:00:00 3500
NORTH 2018-01-04 00:00:00 2050
NORTH 2018-01-05 00:00:00 1060
20 rows selected.
PDB1@ZRP>select /*+ gather_plan_statistics */ * from hybrid_test where time_id=to_date(‘20160102′,’yyyymmdd’);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2016-01-02 00:00:00 3000
SOUTH 2016-01-02 00:00:00 2120
WEST 2016-01-02 00:00:00 2140
NORTH 2016-01-02 00:00:00 1300
PDB1@ZRP>select * from table(dbms_xplan.display_cursor(format=>’IOSTATS PARTITION LAST’)) ;
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 71z2djd7chmxh, child number 0
————————————-
select /*+ gather_plan_statistics */ * from hybrid_test where
time_id=to_date(‘20160102′,’yyyymmdd’)
Plan hash value: 2383463387
Predicate Information (identified by operation id):
—————————————————
2 – filter((SYS_OP_XTNN(“HYBRID_TEST”.”TIME_ID”,”HYBRID_TEST”.”REGION”) AND “TIME_ID”=TO_DATE(‘
2016-01-02 00:00:00′, ‘syyyy-mm-dd hh24:mi:ss’)))
21 rows selected.
PDB1@ZRP>
4）將混合分割槽表轉為Internal Partitioned Tables（傳統分割槽）
1）第一步刪除external partitions
2）第二步刪除external partition attributes
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;
TABLE_NAME PAR HYB
——————– — —
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner=’ZRP’;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
——————– ——————– —————————— ——- —-
HYBRID_TEST SALES_2015 SYSTEM NO YES
HYBRID_TEST SALES_2016 SYSTEM NO YES
HYBRID_TEST SALES_2017 SYSTEM NO YES
HYBRID_TEST SALES_2018 SYSTEM NO YES
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>
然後執行下麵刪除分割槽陳述句
alter table hybrid_test drop partition sales_2015;
alter table hybrid_test drop partition sales_2016;
alter table hybrid_test drop partition sales_2017;
alter table hybrid_test drop partition sales_2018;
alter table hybrid_test drop EXTERNAL PARTITION ATTRIBUTES();
再檢視資料字典
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;
TABLE_NAME PAR HYB
——————– — —
HYBRID_TEST YES NO
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner=’ZRP’;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
——————– ——————– —————————— ——- —-
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>
已經轉換為傳統分割槽表
5）將Internal Partitioned Tables（傳統分割槽）轉為混合分割槽表
1）首先要增加EXTERNAL PARTITION ATTRIBUTES
2）第二步增加external partitions
註：至少要有一個internal partition
PDB1@ZRP>alter table hybrid_test
2 ADD EXTERNAL PARTITION ATTRIBUTES
3 ( TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY sales_data
5 ACCESS PARAMETERS ( FIELDS TERMINATED BY ‘,’
6 (region,time_id DATE ‘yyyy-mm-dd’,amount)
7 )
8 REJECT LIMIT UNLIMITED
9 );
Table altered.
PDB1@ZRP>
然後將之前的測試外部資料作為外部分割槽新增進去
PDB1@ZRP>alter table hybrid_test
2 ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE(‘2016-01-01′,’yyyy-mm-dd’)) EXTERNAL;
ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE(‘2016-01-01′,’yyyy-mm-dd’)) EXTERNAL
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
發現不行，因為我不想破壞資料，順道練習了個split和exchange動作
##將hybrid_test的分割槽sales_2019拆分
alter table hybrid_test
split partition sales_2019 into
(partition sales_2014 VALUES LESS THAN (TO_DATE(‘2015-01-01′,’yyyy-mm-dd’)),
partition sales_2019
);
##建立一個中間表
create table hybrid_test_temp
(
region varchar2(6) NOT NULL,
time_id DATE NOT NULL,
amount NUMBER(10,2)
);
##把裡面的資料交換出去
alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp;
##然後刪除這個sales_2019
alter table hybrid_test drop partition sales_2019;
##接下來就可以新增外部分割槽了
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE(‘2016-01-01′,’yyyy-mm-dd’)) EXTERNAL;
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2016 VALUES LESS THAN (TO_DATE(‘2017-01-01′,’yyyy-mm-dd’))
EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION (‘sales_2016.txt’);
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2017 VALUES LESS THAN (TO_DATE(‘2018-01-01′,’yyyy-mm-dd’))
EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION (‘sales_2017.txt’);
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2018 VALUES LESS THAN (TO_DATE(‘2019-01-01′,’yyyy-mm-dd’))
EXTERNAL LOCATION (‘sales_2018.txt’);
Table altered.
PDB1@ZRP>alter table hybrid_test
ADD PARTITION sales_2019 VALUES LESS THAN (TO_DATE(‘2020-01-01′,’yyyy-mm-dd’));
Table altered.
#最後再把剛才交換出去的資料交換回來，這樣就恢複原樣了
PDB1@ZRP>alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp;
Table altered.
PDB1@ZRP>select * from hybrid_test partition (sales_2019);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2019-01-01 00:00:00 1032
EAST 2019-01-02 00:00:00 2371
##把這個臨時過渡分割槽刪除
PDB1@ZRP>alter table hybrid_test drop partition sales_2014;
Table altered.
這樣就又恢復到最初的混合分割槽的樣子了
PDB1@ZRP>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;
TABLE_NAME PAR HYB
——————– — —
HYBRID_TEST YES YES
PDB1@ZRP>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner=’ZRP’;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME LOGGING READ
——————– ——————– —————————— ——- —-
HYBRID_TEST SALES_2015 USERS NO YES
HYBRID_TEST SALES_2016 USERS NO YES
HYBRID_TEST SALES_2017 USERS NO YES
HYBRID_TEST SALES_2018 USERS NO YES
HYBRID_TEST SALES_2019 USERS YES NO
PDB1@ZRP>select * from hybrid_test partition(sales_2016);
REGION TIME_ID AMOUNT
—— ——————- ———-
EAST 2016-01-01 00:00:00 6000
EAST 2016-01-02 00:00:00 3000
EAST 2016-01-03 00:00:00 9012
EAST 2016-01-04 00:00:00 2450
EAST 2016-01-05 00:00:00 6709
SOUTH 2016-01-01 00:00:00 4000
SOUTH 2016-01-02 00:00:00 2120
SOUTH 2016-01-03 00:00:00 6300
SOUTH 2016-01-04 00:00:00 3850
SOUTH 2016-01-05 00:00:00 2090
WEST 2016-01-01 00:00:00 2467
WEST 2016-01-02 00:00:00 2140
WEST 2016-01-03 00:00:00 5300
WEST 2016-01-04 00:00:00 2470
WEST 2016-01-05 00:00:00 4080
NORTH 2016-01-01 00:00:00 2600
NORTH 2016-01-02 00:00:00 1300
NORTH 2016-01-03 00:00:00 1250
NORTH 2016-01-04 00:00:00 4350
NORTH 2016-01-05 00:00:00 3190
20 rows selected.
PDB1@ZRP>
原創：老張拉呱
