歡迎光臨
每天分享高質量文章

Oracle 19c 新特性:混合分割槽表Hybrid partitioned tables強體驗

 

老張拉呱: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-levelrangelist分割槽,其中只有single-level LIST分割槽支持HIVE

    2)可以使用alter table …DDLs操作,比如ADD,DROPRENAME partitions

    3)可以在分割槽級別修改external partitionsexternal data sources 位置

    4)可以將既有的內部分割槽表修改為混合分割槽表

    5)可以修改現有的locationempty location形成一個空的external partition

    6)可以針對內部分割槽創建global partial non-unique indexes

    7)可以針對內部分割槽創建materialized views

      (8) 可以創建包含外部分割槽的materialized views,前提是QUERY_REWRITE_INTEGRITY必須為STALE_TOLERATED樣式

    9DML操作只能針對混合分割槽表的內部分割槽

    (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

    12Anexternal 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(設置為TRUSTEDSTALE_TOLERATED)。

        2)在混合分割槽表級別定義的AutomaticData Optimization (ADO)策略隻影響內部分割槽

Hybrid partitioned tables-混合分割槽表的限制

    1Restrictionsthat apply to external tables also apply to hybrid partitioned tables unlessexplicitly noted
2)不支持REFERENCESYSTEM分割槽方法
3No uniqueindexes or global unique indexes. Only partial indexes are allowed and uniqueindexes cannot be partial.
4Attributeclustering (CLUSTERING clause) is not allowed
5DMLoperations only on internal partitions of a hybrid partitioned table (externalpartitions are treated as read-only partitions)
6In-memorydefined on the table level only has an effect on internal partitions of thehybrid partitioned table
7No columndefault value
8Invisiblecolumns are not allowed
9TheCELLMEMORY clause is not allowed
10SPLIT,MERGE, and MOVE maintenance operations are not allowed on internal partitions
11)不支持LOB, LONGADT型別
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

    [email protected]>conn zrp/[email protected]
[email protected]>CREATEDIRECTORY sales_data as ‘/u01/app/oracle/oradata/extfiles’;
[email protected]>CREATEDIRECTORY sales_data_2016 as ‘/u01/app/oracle/oradata/extfiles/2016’;
[email protected]>CREATEDIRECTORY sales_data_2017 as ‘/u01/app/oracle/oradata/extfiles/2017’;

3)創建Hybridpartitioned tables-混合分割槽表

[email protected]>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欄位可以看出是否是混合分割槽

   [email protected]>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;

   TABLE_NAME           PAR HYB

   ——————– — —

   HYBRID_TEST          YES YES

   [email protected]>select table_name,partition_name,tablespace_name,logging,read_only from dba_tab_partitions where table_owner=’ZRP’;

  

   [email protected]>

  

 ##插入幾行資料(只能插入到內部分割槽(internal partitions),我這裡是sales_2019).

   [email protected]>select * from hybrid_test partition (sales_2015);

   no rows selected

   [email protected]>insert into hybrid_test values(‘EAST’, to_date(‘20190101′,’yyyy-mm-dd’),1032);

   1 row created.

   [email protected]>insert into hybrid_test values(‘EAST’, to_date(‘20190102′,’yyyy-mm-dd’),2371);

   1 row created.

   [email protected]>commit;

   Commit complete.

   [email protected]>

   

##查看各分割槽的資料

   [email protected]>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

   [email protected]>select * from hybrid_test partition (sales_2015);

   no rows selected

 

   [email protected]>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.

 

   [email protected]>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.

   [email protected]>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.

   [email protected]>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

   [email protected]>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.

   [email protected]>

 

4)將混合分割槽表轉為Internal Partitioned Tables(傳統分割槽)

  

1)第一步刪除external partitions

2)第二步刪除external partition attributes

 

   [email protected]>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;

   TABLE_NAME           PAR HYB

   ——————– — —

   HYBRID_TEST          YES YES

   [email protected]>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

   [email protected]>

  

然後執行下麵刪除分割槽陳述句

   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();

 

再查看資料字典

[email protected]>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;

   TABLE_NAME           PAR HYB

   ——————– — —

   HYBRID_TEST          YES NO

   [email protected]>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

   [email protected]

 

已經轉換為傳統分割槽表

 

5)將Internal Partitioned Tables(傳統分割槽)轉為混合分割槽表

    1)首先要增加EXTERNAL PARTITION ATTRIBUTES
2
)第二步增加external partitions
註:至少要有一個internal partition

 

[email protected]>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.

   [email protected]>

 

然後將之前的測試外部資料作為外部分割槽添加進去

 

   [email protected]>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;

 

   ##接下來就可以添加外部分割槽了

   [email protected]>alter table hybrid_test

              ADD PARTITION sales_2015  VALUES LESS THAN (TO_DATE(‘2016-01-01′,’yyyy-mm-dd’)) EXTERNAL;

   Table altered.

   [email protected]>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.

   [email protected]>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.

   [email protected]>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.

 

   [email protected]>alter table hybrid_test

              ADD PARTITION sales_2019  VALUES LESS THAN (TO_DATE(‘2020-01-01′,’yyyy-mm-dd’));

   Table altered.

 

  

 #最後再把剛纔交換出去的資料交換回來,這樣就恢複原樣了

   [email protected]>alter table hybrid_test exchange partition sales_2019 with table hybrid_test_temp;      

   Table altered.

   [email protected]>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

 

 

  ##把這個臨時過渡分割槽刪除

   [email protected]>alter table hybrid_test drop partition sales_2014;

   Table altered.

   這樣就又恢復到最初的混合分割槽的樣子了

   [email protected]>select table_name,partitioned, hybrid from dba_tables where owner=’ZRP’ and table_name=’HYBRID_TEST’;

   TABLE_NAME           PAR HYB

   ——————– — —

   HYBRID_TEST          YES YES

   [email protected]>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

 

   [email protected]>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.

   [email protected]

 

原創:老張拉呱

    赞(0)

    分享創造快樂