Dpc架构

• 角色/台数----服务器1------ 服务器2----- 服务器3
• 元数据节点—MP_A----------MP_B--------MP_C
• 计算节点-----SP1-----------SP2-----------SP3
• 存储节点-----BP1-----------BP2-----------BP3

调整参数：

sp_set_para_value(1,'HP_TAB_COUNT_PER_BP',4)；
--默认为1
sp_set_para_value(1,'DPC_TABLESPACE_BALANCE',0);
--默认为0

创建表空间

CREATE TABLESPACE TS_001 DATAFILE 'TS_001.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_001' STORAGE(ON RAFT_1) ;
CREATE TABLESPACE TS_002 DATAFILE 'TS_002.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_002' STORAGE(ON RAFT_1) ;
CREATE TABLESPACE TS_003 DATAFILE 'TS_003.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_003' STORAGE(ON RAFT_2) ;
CREATE TABLESPACE TS_004 DATAFILE 'TS_004.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_004' STORAGE(ON RAFT_2) ;
CREATE TABLESPACE TS_005 DATAFILE 'TS_005.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_005' STORAGE(ON RAFT_3) ;
CREATE TABLESPACE TS_006 DATAFILE 'TS_006.DBF' SIZE 20480 AUTOEXTEND ON NEXT 2048 WITH HUGE PATH 'HTS_TS_006' STORAGE(ON RAFT_3) ;

创建表空间组

SP_TS_GROUP_CREATE('TSG_2', 'tablespace group1');
SP_TS_GROUP_ADD_TS('TSG_2','TS_001');
SP_TS_GROUP_ADD_TS('TSG_2','TS_002');
SP_TS_GROUP_ADD_TS('TSG_2','TS_003');
SP_TS_GROUP_ADD_TS('TSG_2','TS_004');
SP_TS_GROUP_ADD_TS('TSG_2','TS_005');
SP_TS_GROUP_ADD_TS('TSG_2','TS_006');

创建用户

CREATE USER SJZ IDENTIFIED BY 123123123  DEFAULT TABLESPACE GROUP "TSG_2"

Grant dba TO SJZ;

1、创建范围分区表初始化表结构

CREATE TABLE sales_data  
(      sales_id INT,  
    sales_date DATE,  
    amount NUMBER  )  
PARTITION BY RANGE (sales_date)  (  
PARTITION sales_1 VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')),  
PARTITION sales_2 VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD')),  
PARTITION sales_3 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),  
PARTITION sales_4 VALUES LESS THAN (TO_DATE('2023-05-01', 'YYYY-MM-DD')), 
PARTITION sales_5 VALUES LESS THAN (TO_DATE('2023-06-01', 'YYYY-MM-DD')),  
PARTITION sales_6 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')), 
PARTITION sales_max VALUES LESS THAN (MAXVALUE) -- 用于捕获未来日期的数据  
);

对应的分区存储位置

结论：分区以BP为单位进行跨越。
2、创建list分区表初始化表结构

CREATE TABLE special_dates_data  
(  
    event_id INT,  
    special_date DATE,  
    event_description VARCHAR2(255)  
)  
PARTITION BY LIST (special_date)  
(  
    PARTITION special_20230101 VALUES ('2023-01-01'),  
    PARTITION special_20230214 VALUES ('2023-02-14'), 
    PARTITION special_20230105 VALUES ('2023-01-02'),  
    PARTITION special_20230216 VALUES ('2023-02-15'),   
    PARTITION special_20230108 VALUES ('2023-01-03'),  
    PARTITION special_20230219 VALUES ('2023-02-16'), 
    PARTITION special_dates_others VALUES (DEFAULT)  
);

结论：分区以BP为单位进行跨越存储。
3、创建hash分区表

CREATE TABLE SPECIAL_HASH 
(  
    event_id INT,  
    special_date DATE,  
    event_description VARCHAR2(255)  
)  
PARTITION BY HASH(event_id) PARTITIONS DEFAULT;

结论：分区以BP为单位进行跨越存储。
4、创建rang-hash

create table test_range_hash(sno number, sname varchar2(10))
partition by range(sno)
subpartition by hash(sname)   
subpartitions 4
(
  partition p1 values less than(1000),
  partition p2 values less than(2000),
  partition p3 values less than(maxvalue)
);

结论：分区以BP为单位进行跨越存储，但规定几个分区就是都存储在前四个表空间上
5、创建list-hash

CREATE TABLE TEST_LIST_HASH (v11 number,vl2 varchar(12))
PARTITION BY LIST (vl2)
subpartition by hash(v11)  
SUBPARTITIONS 4
(
      partition p1 values ('FJ'), 
            partition p3 values ('F1'), 
       partition p4 values ('F2'), 
      partition p2 values (default)
);

结论：分区以BP为单位进行跨越存储，但规定几个分区就是都存储在前四个表空间上