Oracle 19C 创建二级分区表利用分片集群

经过了一些测试，发现这个sharding不是很好用，很多功能限制较多，还是建议使用RAC

创建二级分区表

启动shard

SQL> alter session enable shard ddl;

1. DEMO1 应用于混杂模式

    CREATE SHARDED TABLE customers
    ( cust_id NUMBER NOT NULL
    , name VARCHAR2(50)
    , address VARCHAR2(250) 
    , location_id VARCHAR2(20) 
    , class VARCHAR2(3) 
    , signup_date DATE 
    , CONSTRAINT cust_pk PRIMARY KEY(cust_id, class) 
    )
    PARTITIONSET BY LIST (class) 
      PARTITION BY CONSISTENT HASH (cust_id)
      PARTITIONS AUTO
    (PARTITIONSET gold VALUES ('gld') TABLESPACE SET TSP_SET_1)
    ;
   

   PARTITIONSET中的值必须和shardspace的数量一致

2. DEMO2 应用于system模式

    CREATE SHARDED TABLE customers 
    ( cust_id     NUMBER NOT NULL
    , name        VARCHAR2(50)
    , address     VARCHAR2(250)
    , location_id VARCHAR2(20)
    , class       VARCHAR2(3)
    , signup_date DATE
    , CONSTRAINT cust_pk PRIMARY KEY(cust_id, signup_date)
    )
    TABLESPACE SET TSP_SET_1
    PARTITION BY CONSISTENT HASH (cust_id)
    SUBPARTITION BY RANGE (signup_date)
    SUBPARTITION TEMPLATE 
    ( SUBPARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
      SUBPARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
      SUBPARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY')),
      SUBPARTITION future VALUES LESS THAN (MAXVALUE)
    )
    PARTITIONS AUTO
    ;
   

3. demo

    CREATE SHARDED TABLE voduser(
          userid varchar(500) not null, 
          areaid varchar(50), 
          stbtype integer, 
          starttime date, 
          processendtime date, 
          endtime date, 
          vodname varchar(1000), 
          vodid varchar(1000), 
          vodlogid varchar(1000), 
          vodlength integer, 
          pnum integer, 
          sp varchar(1000), 
          cp varchar(1000), 
          vodsp varchar(1000), 
          vodcp varchar(1000), 
          vodspname varchar(1000), 
          vodcpname varchar(1000), 
          director varchar(1000), 
          performer varchar(1000), 
          damscategory varchar(1000), 
          cmscategory varchar(1000), 
          categorygroup varchar(1000), 
          seconds integer, 
          groupid varchar(1000), 
          streamgroupid varchar(1000), 
          flag varchar(1000), 
          secondarycategorygroup varchar(1000), 
          allseconds varchar(1000), 
          charges varchar(1000), 
          cmstagid varchar(2000), 
          ztid varchar(1000), 
          visitpath varchar(1000)
        )
        TABLESPACE SET TSP_SET_1
        PARTITION BY CONSISTENT HASH (userid)
        SUBPARTITION BY RANGE (starttime)
        SUBPARTITION TEMPLATE 
        ( SUBPARTITION voduser20210101 VALUES LESS THAN (TO_DATE('20210101','yyyymmdd')),
          SUBPARTITION voduser20210102 VALUES LESS THAN (TO_DATE('20210102','yyyymmdd')),
          SUBPARTITION voduser20210103 VALUES LESS THAN (TO_DATE('20210103','yyyymmdd')),
          SUBPARTITION future VALUES LESS THAN (MAXVALUE)
        )
        PARTITIONS AUTO
        ;
   

相关SQL查询

1. 登录catalog database验证所有的chunks都是统一分布式的

    SELECT a.name Shard,COUNT(b.chunk_number) Number_of_Chunks
      FROM gsmadmin_internal.database a,gsmadmin_internal.chunk_loc b
      WHERE a.database_num=b.database_num
      GROUP BY a.name
      ORDER BY a.name;
   

2. 确认shard和复制表创建完毕

    show parameter db_unique_name;
    select owner,object_id,object_name,object_type from dba_objects where  object_name='CUSTOMERS';
   

添加子分区

添加子分区

alter table VODUSER
	split subpartition VODUSER_P1_FUTURE at (TO_DATE('20210104','yyyymmdd')) into (
	subpartition VODUSER_P1_VODUSER20210104,
	subpartition VODUSER_P1_FUTURE
) online


alter table VODUSER
set subpartition template (
	SUBPARTITION voduser20210104 VALUES LESS THAN (TO_DATE('20210104','yyyymmdd'))
);

查看是否readonly

select partition_name,compress_for,read_only from user_tab_partitions;

开启readonly

alter table sales_range modify partition q1 read only;

关闭

alter table sales_range modify partition q1 read write;

jdbc下载

需要升级JDK11
https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19-9-c-downloads.html