oracle 子分区

--1.创建表空间
create tablespace tbs1 datafile
'e:\oracle\tbs1_1_1.dbf' size 5m;

create tablespace tbs2 datafile
'e:\oracle\tbs2_1_1.dbf' size 5m;

-------------
--2.建表
create table AGG_BILL_BY_CITY (
    HOUR_ID INTEGER not null,
   CITY_ID INTEGER not null,
  MONTH_ID INTEGER,
  CALL_TYPE_ID INTEGER not null,
  DEAL_ID INTEGER not null,
  OPERATOR_ID INTEGER not null,
  FEE_TYPE_ID INTEGER not null,
  ROAM_TYPE_ID INTEGER not null,
   CALL_FEE NUMBER,
   LONG_FEE NUMBER,
  CALL_NUM INTEGER,
  BILL_DURATION NUMBER,
  CALL_DURATION NUMBER,
 GPRS_IN NUMBER,
  GPRS_OUT NUMBER,
   LOAD_TIME DATE default sysdate,
 SUM_NUM NUMBER
   )
  partition by range(HOUR_ID)  --partition attri
  subpartition by 
	hash(CITY_ID, CALL_TYPE_ID, DEAL_ID, OPERATOR_ID, FEE_TYPE_ID, ROAM_TYPE_ID)--subpartition attri
  subpartitions 2 store in(TBS1,tBS2) 
  (
  partition P20060527 values less than(2006052800), --partition
  partition P20060528 values less than(2006052900)
  );
alter table AGG_BILL_BY_CITY MODIFY DEFAULT ATTRIBUTES tablespace tbs1;--default tablespace

alter table AGG_BILL_BY_CITY add partition P20060530 values less than(2006053000);--add partition

select table_name,partition_name,subpartition_name,tablespace_name 
from user_tab_subpartitions;



alter table AGG_BILL_BY_CITY   --change tablespace
move subpartition sys_subp25 tablespace tbs1;

alter table AGG_BILL_BY_CITY 
move subpartition sys_subp32 tablespace tbs2;

drop table agg_bill_by_city;

-----------------------------------
create table AGG_BILL_BY_CITY
    (
    HOUR_ID INTEGER not null,
    CITY_ID INTEGER not null,
    MONTH_ID INTEGER,

    CALL_TYPE_ID INTEGER not null,

    DEAL_ID INTEGER not null,

    OPERATOR_ID INTEGER not null,

  FEE_TYPE_ID INTEGER not null,
  ROAM_TYPE_ID INTEGER not null,

   CALL_FEE NUMBER,

   LONG_FEE NUMBER,

   CALL_NUM INTEGER,

  BILL_DURATION NUMBER,

   CALL_DURATION NUMBER,

  GPRS_IN NUMBER,

  GPRS_OUT NUMBER,

  LOAD_TIME DATE default sysdate,

 SUM_NUM NUMBER
   )
   partition by range(HOUR_ID)
  subpartition by hash(CITY_ID, CALL_TYPE_ID, DEAL_ID, OPERATOR_ID, FEE_TYPE_ID, ROAM_TYPE_ID)
 SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE tbs1,
	  SUBPARTITION sp2 TABLESPACE tbs2)
   (
   partition P20060527 values less than(2006052800),
  partition P20060528 values less than(2006052900)
   );
	 
alter table AGG_BILL_BY_CITY set subpartition template(SUBPARTITION sp1 TABLESPACE tbs1,
	  SUBPARTITION sp2 TABLESPACE tbs2,
		SUBPARTITION sp3 TABLESPACE tbs2)

select * from AGG_BILL_BY_CITY
insert into AGG_BILL_BY_CITY(HOUR_ID,CITY_ID, CALL_TYPE_ID, DEAL_ID, OPERATOR_ID, FEE_TYPE_ID, ROAM_TYPE_ID)
values(2006052800,1,1,1,1,1,1);

select distinct table_name,partition_name,tablespace_name ,subpartition_name
from user_tab_subpartitions
order by tablespace_name;

 select * from user_subpartition_templates;

alter table AGG_BILL_BY_CITY add partition P20060530 values less than(2006053000);

alter table AGG_BILL_BY_CITY add partition Pmax values less than(maxvalue);
alter table AGG_BILL_BY_CITY 
move subpartition sys_subp32 tablespace tbs2;

alter table ac82 modify partition part_
add subpartition P20060527_SP3 values(3) tablespace ;

PART_2010_PART_350106 AC82_2010



declare
cursor cur is select distinct table_name,partition_name ,tablespace_name
from user_tab_subpartitions where 1=1
order by table_name,tablespace_name,partition_name;
begin
	for c in cur loop
		execute immediate 'alter table '||c.table_name||' modify partition '||c.partition_name||
		' add subpartition '||c.partition_name||'_PART_350106'||' values(''350106'') tablespace '
		||c.tablespace_name;
	end loop;
end;




----------------------------
drop table AGG_BILL_BY_CITY
create table AGG_BILL_BY_CITY
    (
    HOUR_ID INTEGER not null,
    CITY_ID INTEGER not null,
    MONTH_ID INTEGER,

    CALL_TYPE_ID INTEGER,

    DEAL_ID INTEGER,

    OPERATOR_ID INTEGER,

  FEE_TYPE_ID INTEGER ,
  ROAM_TYPE_ID INTEGER,

   CALL_FEE NUMBER,

   LONG_FEE NUMBER,

   CALL_NUM INTEGER,

  BILL_DURATION NUMBER,

   CALL_DURATION NUMBER,

  GPRS_IN NUMBER,

  GPRS_OUT NUMBER,

  LOAD_TIME DATE default sysdate,

 SUM_NUM NUMBER
   )
   partition by range(HOUR_ID)
  subpartition by list(CITY_ID)
 SUBPARTITION TEMPLATE(SUBPARTITION sp1 values(1) TABLESPACE tbs1 ,
	  SUBPARTITION sp2 values(2) TABLESPACE tbs2)
   (
   partition P20060527 values less than(2006052800),
  partition P20060528 values less than(2006052900)
   );

insert into AGG_BILL_BY_CITY (hour_id,city_id) values(1,3);
insert into AGG_BILL_BY_CITY (hour_id,city_id) values(2006052910,3);

alter table AGG_BILL_BY_CITY set subpartition template(SUBPARTITION sp1 values(1) TABLESPACE tbs1 ,
	  SUBPARTITION sp2 values(2) TABLESPACE tbs2,
		SUBPARTITION sp3 values(3) TABLESPACE tbs2)
-------------------------------------------
-- 不指定表空间 分区表的默认空间->表的默认空间->用户的默认空间
分区索引 本地索引会随着分区的操作自动增加
建立分区索引没默认表空间,新增索引所在表空间和新增表空间一致

复核分区索引给出分区默认表空间,新建索引子分区建立在索引分区表空间


--例一：分区表增加分区

SELECT DEFAULT_TABLESPACE FROM USER_USERS;

create tablespace test datafile
'e:\oracle\test.dbf' size 5m;

select * from test;
drop table test;

CREATE TABLE TEST (ID NUMBER)
TABLESPACE USERS  --table tablespace
PARTITION BY RANGE (ID)
(
PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST --partition  tablespace
);

select * from test1
drop table test1;

CREATE TABLE TEST1 (ID NUMBER)
 PARTITION BY RANGE (ID)
 (
 PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST
 );

 ALTER TABLE TEST ADD PARTITION P2 VALUES LESS THAN (200);--table tablespace

ALTER TABLE TEST1 ADD PARTITION P2 VALUES LESS THAN (200);--user tablespace




 DROP TABLE TEST;
 DROP TABLE TEST1;

create tablespace tools 
datafile 'e:\oracle\tools.dbf'
size 5m;

--例二：复合分区表增加子分区
CREATE TABLE TEST (TIME DATE, STATE VARCHAR2(10))
TABLESPACE tools
 PARTITION BY RANGE (TIME) 
 SUBPARTITION BY LIST (STATE) 
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD')) TABLESPACE USERS(--partition
 SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST ));

CREATE TABLE TEST1 (TIME DATE, STATE VARCHAR2(10))
 TABLESPACE tools  --table
 PARTITION BY RANGE (TIME) 
 SUBPARTITION BY LIST (STATE)
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD'))(--table tablespace
 SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST
 ));
 
CREATE TABLE TEST2 (TIME DATE, STATE VARCHAR2(10))--default
 PARTITION BY RANGE (TIME) SUBPARTITION BY LIST (STATE)
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD')) --table default
 ( SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST
 )
 )
 ;
SELECT partition_name,TABLE_NAME, TABLESPACE_NAME FROM USER_TAB_PARTITIONS
--WHERE PARTITION_NAME = 'P2'
order by table_name,partition_name
;

drop table AGG_BILL_BY_CITY;

select table_name,partition_name,subpartition_name,tablespace_name 
from user_tab_subpartitions
where subpartition_name='SP2';

ALTER TABLE TEST MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');

ALTER TABLE TEST1 MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');

ALTER TABLE TEST2 MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');

SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TAB_SUBPARTITIONS 
 WHERE SUBPARTITION_NAME = 'SP2';

DROP TABLE TEST;
DROP TABLE TEST1;
DROP TABLE TEST2;

--例三：本地索引分区

CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30))
 TABLESPACE USERS   --
 PARTITION BY RANGE (ID)
 (
 PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST --
 );

CREATE TABLE TEST1 (ID NUMBER, NAME VARCHAR2(30))
 PARTITION BY RANGE (ID)
 (
 PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST --
 );

CREATE INDEX IND_TEST_1 ON TEST (ID) LOCAL;

create tablespace indx 
datafile 'e:\oracle\indx.dbf'
size 1m;

CREATE INDEX IND_TEST_2 ON TEST (NAME) TABLESPACE INDX LOCAL; --

CREATE INDEX IND_TEST1_1 ON TEST1 (ID) LOCAL;

CREATE INDEX IND_TEST1_2 ON TEST1 (NAME)  TABLESPACE INDX LOCAL;


ALTER TABLE TEST ADD PARTITION P2 VALUES LESS THAN (200);
ALTER TABLE TEST1 ADD PARTITION P2 VALUES LESS THAN (200);

SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TAB_PARTITIONS
WHERE PARTITION_NAME = 'P2';


SELECT TABLE_NAME, A.INDEX_NAME, A.TABLESPACE_NAME 
 FROM USER_IND_PARTITIONS A, USER_INDEXES B
 WHERE PARTITION_NAME = 'P2'
 AND A.INDEX_NAME = B.INDEX_NAME
 ORDER BY 1, 2;

DROP TABLE TEST;
DROP TABLE TEST1;

--例四：复合分区索引


CREATE TABLE TEST (ID NUMBER, TIME DATE, STATE VARCHAR2(10))
 TABLESPACE TOOLS --
 PARTITION BY RANGE (TIME) SUBPARTITION BY LIST (STATE)
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD')) TABLESPACE USERS--
 (
 SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST--
 )
 )
;


CREATE TABLE TEST1 (ID NUMBER, TIME DATE, STATE VARCHAR2(10))
 TABLESPACE TOOLS ---
 PARTITION BY RANGE (TIME) SUBPARTITION BY LIST (STATE)
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD'))
 (
 SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST--
 )
 )
 ;


CREATE TABLE TEST2 (ID NUMBER, TIME DATE, STATE VARCHAR2(10))
 PARTITION BY RANGE (TIME) SUBPARTITION BY LIST (STATE)
 (
 PARTITION P1 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD'))
 (
 SUBPARTITION SP1 VALUES ('BJ') TABLESPACE TEST --
 )
 )
 ;

CREATE INDEX IND_TEST_1 ON TEST (ID) LOCAL;
CREATE INDEX IND_TEST_2 ON TEST (TIME) TABLESPACE INDX LOCAL;


CREATE INDEX IND_TEST_3 ON TEST (STATE) LOCAL (PARTITION P1 TABLESPACE SYSTEM) TABLESPACE INDX;
CREATE INDEX IND_TEST1_1 ON TEST1 (ID) LOCAL;

CREATE INDEX IND_TEST1_2 ON TEST1 (TIME) TABLESPACE INDX LOCAL;

CREATE INDEX IND_TEST1_3 ON TEST1 (STATE)
 LOCAL (PARTITION P1 TABLESPACE SYSTEM)
 TABLESPACE INDX;


CREATE INDEX IND_TEST2_1 ON TEST2 (ID) LOCAL;

CREATE INDEX IND_TEST2_2 ON TEST2 (TIME)
TABLESPACE INDX LOCAL;

CREATE INDEX IND_TEST2_3 ON TEST2 (STATE)
 LOCAL (PARTITION P1 TABLESPACE SYSTEM)
 TABLESPACE INDX;


ALTER TABLE TEST MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');

ALTER TABLE TEST1 MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');


ALTER TABLE TEST2 MODIFY PARTITION P1 ADD SUBPARTITION SP2 VALUES ('SH');

SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE SUBPARTITION_NAME = 'SP2';

TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------TEST USERS
TEST1 TOOLS
TEST2 YANGTK

SQL> SELECT TABLE_NAME, A.INDEX_NAME, A.TABLESPACE_NAME 
2 FROM USER_IND_SUBPARTITIONS A, USER_INDEXES B
3 WHERE SUBPARTITION_NAME = 'SP2'
4 AND A.INDEX_NAME = B.INDEX_NAME
5 ORDER BY 1, 2;

TABLE_NAME INDEX_NAME TABLESPACE_NAME
-------------------- -------------------- -----------------------TEST IND_TEST_1 USERS
TEST IND_TEST_2 INDX
TEST IND_TEST_3 SYSTEM
TEST1 IND_TEST1_1 TOOLS
TEST1 IND_TEST1_2 INDX
TEST1 IND_TEST1_3 SYSTEM
TEST2 IND_TEST2_1 YANGTK
TEST2 IND_TEST2_2 INDX
TEST2 IND_TEST2_3 SYSTEM

ALTER TABLE TEST_INSER ENABLE ROW MOVEMENT;