ORACLE 19c 新特性之混合分割槽表

描述                                           

1.1 混合分割槽表的介紹

從 Oracle Database 19c 開始， Oracle 資料庫支援 Hybridpartitioned tables ，也就是混合分割槽表，進一步擴充套件了 Oracle 分割槽技術。這裡的混合指的是資料的分佈，一些分割槽可以位於資料庫中，另一些可以是位於資料庫外部的檔案（比如作業系統檔案或 Hadoop Distributed File System (HDFS)  檔案）。從 12.2 開始就支援了外部表分割槽、只讀分割槽，在 19c 中只是將內部分割槽（ internal partitions ）與 Oracle 外部分割槽（ external partitions ）特性結合起來，形成一個更通用的分割槽，稱為混合分割槽表。

 

Oracle 混合分割槽表的意義是透過將傳統的內部分割槽表和外部分割槽表結合起來，從而形成了更加通用的分割槽，啟用混合分割槽表更易將內部分割槽和外部分割槽整合到單個分割槽表中。該特性還可以很容易的將非活躍分割槽移動到外部檔案中，從而得降低儲存解決方案成本。

1.2 支援外部分割槽的所有外部表型別

混合分割槽表支援外部分割槽的所有現有外部表型別 :

        • ORACLE_DATAPUMP

        • ORACLE_LOADER

        • ORACLE_HDFS

        • ORACLE_HIVE

1.3 混合分割槽表支援的操作

混合分割槽表支援的操作

（ 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 操作只能針對混合分割槽表的內部分割槽

混合分割槽表測試

構造外部資料

VI 編輯器編輯

sales_2016.txt

region,time_id,amount

EAST,20160101,6000

SOUTH,20160101,4000

WEST,20160101,2467

NORTH,20160101,2600

 

sales_2017.txt

region,time_id,amount

EAST,20170101,8000

SOUTH,20170101,2000

WEST,20170101,3400

NORTH,20170101,2000

 

sales_2018.txt

region,time_id,amount

EAST,20180101,2100

SOUTH,20180101,1300

WEST,20180101,3800

NORTH,20180101,2120

 

 

建立目錄物件

mkdir /home/oracle/extfiles/2016

mkdir /home/oracle/extfiles/2017

 

CREATE DIRECTORY sales_data as '/home/oracle/extfiles';

CREATE DIRECTORY sales_data_2016 as '/home/oracle/extfiles/2016';

CREATE DIRECTORY sales_data_2017 as '/home/oracle/extfiles/2017';

 

將 sales_2016.txt 移動到 /home/oracle/extfiles/2016 下做為外部檔案

將 sales_2017.txt 移動到 /home/oracle/extfiles/2017 下做為外部檔案

將 sales_2018.txt 移動到 /home/oracle/extfiles 下做為外部檔案

建立混合分割槽表

CREATE TABLE hybrid_partition_table

 (

    region        varchar2(6)   NOT NULL,

    time_id       DATE          NOT NULL,

    amount        NUMBER(10,2)

 )

   EXTERNAL PARTITION ATTRIBUTES (

     TYPE ORACLE_LOADER

     DEFAULT DIRECTORY sales_data

      ACCESS PARAMETERS(

        FIELDS TERMINATED BY ','

                      (region,time_id DATE 'yyyy-mm-dd',amount)

                        )

     REJECT LIMIT UNLIMITED

    )

PARTITION BY RANGE (time_id)

(

  PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd'))

EXTERNAL DEFAULT DIRECTORY sales_data_2016 LOCATION ('sales_2016.txt'),

  PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd'))

EXTERNAL DEFAULT DIRECTORY sales_data_2017 LOCATION ('sales_2017.txt'),

  PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL LOCATION ('sales_2018.txt'),

  PARTITION sales_2019 VALUES LESS THAN (TO_DATE('2020-01-01','yyyy-mm-dd'))

);

 

REJECT LIMIT UNLIMITED ：指定在資料庫返回錯誤和查詢終止前，允許出現多少行轉換錯誤發生

TYPE ：定義外部檔案訪問驅動型別 ORACLE_LOADER ORACLE_DATAPUMP

ACCESS PARAMETERS ：定義訪問屬性

 

EXTERNAL 子句的 PARTITION 子句將分割槽定義為外部分割槽。如果沒有 EXTERNAL 子句，則該分割槽是內部分割槽。

 

如果沒有為外部分割槽定義外部檔案，則外部分割槽為空。可以使用 ALTER TABLE MODIFY PARTITION 語句用外部檔案填充它。

PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')) EXTERNAL,

 

檢視是否為混合分割槽表

檢視是否為混合分割槽表：

SQL> select table_name,partitioned, hybrid from dba_tables where owner='SYS' and table_name='HYBRID_PARTITION_TABLE';

TABLE_NAME                       PAR HYB

------------------------------ --- ---

HYBRID_PARTITION_TABLE    YES YES

混合分割槽表的DML 操作

SQL> select * from HYBRID_PARTITION_TABLE partition (sales_2019);

 

no rows selected

 

SQL> select * from HYBRID_PARTITION_TABLE partition (sales_2016);

REGION TIME_ID       AMOUNT

------ --------- ----------

EAST   01-JAN-16       6000

SOUTH  01-JAN-16       4000

WEST   01-JAN-16       2467

NORTH  01-JAN-16       2600

 

SQL> select * from HYBRID_PARTITION_TABLE partition (sales_2017);

REGION TIME_ID       AMOUNT

------ --------- ----------

EAST   01-JAN-17       8000

SOUTH  01-JAN-17       2000

WEST   01-JAN-17       3400

NORTH  01-JAN-17       2000

 

 

SQL> select * from HYBRID_PARTITION_TABLE partition (sales_2018);

REGION TIME_ID       AMOUNT

------ --------- ----------

EAST   01-JAN-18       2100

SOUTH  01-JAN-18       1300

WEST   01-JAN-18       3800

NORTH  01-JAN-18       2120

 

內部分割槽支援 DML 操作，外部分割槽表不支援 DML 操作

內部分割槽表：

insert into hybrid_partition_table values('EAST', to_date('20190101','yyyy-mm-dd'),1032);

insert into hybrid_partition_table values('EAST', to_date('20190102','yyyy-mm-dd'),2371);

commit;

 

SQL> select * from HYBRID_PARTITION_TABLE partition (sales_2019);

REGION TIME_ID       AMOUNT

------ --------- ----------

EAST   01-JAN-19       1032

EAST   02-JAN-19       2371

 

SQL> delete from HYBRID_PARTITION_TABLE partition (sales_2019);

2 rows deleted.

 

 

外部分割槽表：

insert into hybrid_partition_table values('EAST', to_date('20160102','yyyy-mm-dd'),2371);

ERROR at line 1:

ORA-14466: Data in a read-only partition or subpartition cannot be modified.

 

SQL> delete from HYBRID_PARTITION_TABLE partition (sales_2016);

ERROR at line 1:

ORA-14354: operation not supported for a hybrid-partitioned table