CREATE TABLE MYDB
(SID NUMBER(2) PRIMARY KEY,
SNAME CHAR(5) NOT NULL,
SPHONE NUMBER(11) NULL,
SADDR VARCHAR2(10) DEFAULT '廣東')
PARTITION BY LIST (SADDR)--清單分區:根據某個字段的具體值分區(list)
(PARTITION PA_ADD1 VALUES ('廣東') TABLESPACE SYSTEM,
PARTITION PA_ADD2 VALUES ('廣西') TABLESPACE USERS
)
CREATE TABLE MYDB
(SID NUMBER(2) PRIMARY KEY,
SNAME CHAR(5) NOT NULL,
SPHONE NUMBER(11) NULL,
SADDR VARCHAR2(10) DEFAULT '廣東')
PARTITION BY RANGE (SID)--範圍分區:根據某個字段在某個範圍内進行分區(range...less)
(PARTITION PA_ADD1 VALUES LESS THAN(100) TABLESPACE SYSTEM,--list than
PARTITION PA_ADD2 VALUES LESS THAN(MAXVALUE) TABLESPACE USERS
)
CREATE TABLE MYDB
(SID NUMBER(2) PRIMARY KEY,
SNAME CHAR(5) NOT NULL,
SPHONE NUMBER(11) NULL,
SADDR VARCHAR2(10) DEFAULT '廣東')
PARTITION BY HASH (SID)--散列分區:根據字段的hash值進行均勻分布(hash)
(PARTITION PA_ADD1 TABLESPACE SYSTEM,
PARTITION PA_ADD2 TABLESPACE USERS
)
CREATE TABLE MYDB
(SID NUMBER(2) PRIMARY KEY,
SNAME CHAR(5) NOT NULL,
SPHONE NUMBER(11) NULL,
SADDR VARCHAR2(10) DEFAULT '廣東')
PARTITION BY RANGE (SADRR) SUBPARTITION BY LIST (SID)--複合分區(範圍+散列、範圍+清單)
(PARTITION PA_ID1 VALUES LESS THAN(100) TABLESPACE SP1
(
SUBPARTITIONS PA_ADRR1 VALUES('廣東') TABLESPACE SP1,
SUBPARTITIONS PA_ADRR2 VALUES('廣西') TABLESPACE SP1
)
PARTITION PA_ID2 VALUES LESS THAN(MAXVALUE) TABLESPACE SP1
(
SUBPARTITIONS PA_ADRR3 VALUES('廣東') TABLESPACE SP1,
SUBPARTITIONS PA_ADRR4 VALUES('廣西') TABLESPACE SP1
)
)
--拆分分區(sblit)
alter table tb_name sblit partition p2 at () into (partition p11,partition p22);
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
--合并分區、MERGE(融入的意思)
alter table tb_name merge partitions sp1,sp2 into partition sp2;
ALTER TABLE TB_NAME MERGE PARTITIONS SP1,SP2 INTO PARTITION SP2;
--接合分區(隻用于散列分區中)(coalesca)
alter table tb_name coalesca partitiion;
ALTER TABLE SALES COALESCA PARTITION;
--重名名分區(rename partition...to...)
alter table tb_name rename partition sp1 to sp2;
--查詢某個表存在的分區資訊
select * from user_tab_partitions where table_name='tb_name';
--使用union實作跨分區查詢
--alter table tab_name truncate partition p_name; 清除表中某個分區的資訊(截斷分區 )
--練習
--檢視表分區資訊
select * from user_tab_partitions where table_name='stues';
--班級表
create table classes (
cid number(6) primary key,
cnum number(10) unique,
cname varchar(10) not null,
cmax_age number(2),
cmin_age number(2),
csex number(1) default 1--性別1女2男
)
partition by list(csex)(
partition class_sex_l01 values(1) tablespace users,
partition class_sex_l02 values(2) tablespace users
)
select * from classes partition(class_sex_l01);
--學生表
create table stues (
sid number(6),
snum number(10),
sname varchar(10) not null,
sage number(2),
ssex number(1) default 1,
scid number(6),
constraint pk_con primary key(sid),
constraint un_con unique(snum),
constraint ck_age_con check (sage between 1 and 99),
constraint ck_sex_con check (ssex between 1 and 2),
constraint fk_con foreign key(scid) references classes(cid)
) partition by range(sage)(
partition class_age_r01 values less than(30) tablespace users,
partition class_age_r02 values less than(60) tablespace users,
partition class_age_r03 values less than(99) tablespace users
)
select * from stues partition(class_age_r02);
select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual;
select cast('123' as number(5)) from dual;
select cast(dbms_random.value as varchar2(38)) from dual;
--根據區分表空間建表(例子)
CREATE TABLE MYDB
(SID NUMBER(2) PRIMARY KEY,
SNAME CHAR(5) NOT NULL,
SPHONE NUMBER(11) NULL,
SADDR VARCHAR2(10) DEFAULT '廣東')
PARTITION BY LIST (SADDR)--清單分區
(PARTITION PA_ADD1 VALUES ('廣東') TABLESPACE SYSTEM,
PARTITION PA_ADD2 VALUES ('廣西') TABLESPACE USERS
)
ALTER TABLE MYDB
MODIFY (SNAME VARCHAR2(10) );
ALTER TABLE MYDB
MODIFY (SADDR DEFAULT '廣東廣州' );
ALTER TABLE MYDB
MOVE TABLESPACE USERS;--移動表到其他表空間(索引會失效!)
select * from user_tab_partitions where table_name='mydb'; --檢視表的分區資訊