day11_分区表——分区表常用维护
分区表存放在不同表空间，放在不同磁盘中，I/O就分散了，性能提高
公司的主要表一定是分区表
SQL> sqlplus / as sysdba
SQL> grant create tablespace to scott;
conn scott/lipengfei
create tablespace ts01 logging datafile '/oracle/app/oradata/ecom/ts01.dbf' size 10m;
create tablespace ts02 logging datafile '/oracle/app/oradata/ecom/ts02.dbf' size 10m;
create tablespace ts03 logging datafile '/oracle/app/oradata/ecom/ts03.dbf' size 10m;
create tablespace ts04 logging datafile '/oracle/app/oradata/ecom/ts04.dbf' size 10m;
范围分区
SQL> create table test123(id number,createdate date)
partition by range(createdate)
(
partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace ts01,
partition p2 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace ts02,
partition p3 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace ts03,
partition pmax values less than (maxvalue) tablespace ts04
);
------------------------------------------------------
创建hash 分区表(按hash算法打散了,再分区,通常应用在不能划分范围的表)
SQL> create table test6 (id number,name varchar2(10))
partition by hash(name)
partitions 4
store in (ts01,ts02,ts03,ts04);
------------------------------------------------------
创建列表分区
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
比如城市,省份
SQL> create table test122 (id number,name varchar2(10),city varchar2(10))
partition by list(city)
(
partition p1 values('bj','sh') tablespace ts01,
partition p2 values('gz','sz') tablespace ts02,
partition p3 values('cc','jl') tablespace ts03,
partition p4 values(default) tablespace ts04
);
查看分区表
SQL> select * from user_tab_partitions
===========================================
分区合并
范围分区合并
SQL> alter table test123 merge partitions p1,p2 into partition p12;
SQL> select * from user_tab_partitions order by table_name
----------------------
拆分分区
SQL> alter table test123 split partition pmax at(to_date('2004-01-01','yyyy-mm-dd')) into (partition p5,partition pmax);
把原来的pmax分区 拆分成2个分区
-----------------------
创建新的分区
create tablespace ts05 logging datafile '/oracle/app/oradata/ecom/ts05.dbf' size 10m;
SQL> alter table test123 drop partition pmax;
SQL> alter table test123 add partition p15 values less than(to_date('2005-01-01','yyyy-mm-dd'))tablespace ts05;
create tablespace ts06 logging datafile '/oracle/app/oradata/ecom/ts06.dbf' size 10m;
SQL> alter table test123 add partition pnimei values less than(maxvalue)tablespace ts06;
----------------------额外赠送-------------------------
有关表分区的一些维护性操作：
一、添加分区
以下代码给SALES表添加了一个P3分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
二、删除分区
以下代码删除了P3表分区：
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
三、截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
五、拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
六、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
八、查询
select count(*) from t_table_SS PARTITION (P200709_1);【查看指定分区数据】
查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName';
--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;
