0

Oracle 19c 新特性:混合分区表Hybrid partitioned tables强体验

老张拉呱 2019-02-24
786



老张拉呱: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

    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> 


原创:老张拉呱


资源下载

关注公众号:数据和云(OraNews)回复关键字获取

2018DTCC , 数据库大会PPT

2018DTC,2018 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2018OOW ,Oracle OpenWorld 资料

产品推荐

云和恩墨Bethune Pro企业版,集监控,巡检,安全于一身,你的专属数据库实时监控和智能巡检平台,漂亮的不像实力派,你值得拥有!

云和恩墨zData一体机现已发布超融合版本和精简版,支持各种简化场景部署,零数据丢失备份一体机ZDBM也已发布,欢迎关注。


「喜欢文章,快来给作者赞赏墨值吧」
文章转载自老张拉呱,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部