Oracle分区实战

前言

　　由于近期做的一个项目每个月需要插入大约一百万条数据，这样的话，一年的数据就是一千万以上的数据，而且项目的业务要求的查询条件都是模糊查询，所以索引不起作用，最后决定对表进行分表分区，由于分表需要分析项目的业务作出最适合的分表方式，分表只要选定了以什么来分，就不难了。这篇文章讲述的是对表的分区，由于数据是按月插入的，所以我按照的是按月分区（即Oracle按时间分区）。

分区的目的

　　数据库分区：就是减少SQL操作的数据量，从而提升查询效率。表分区后，逻辑上仍然是一张表，只不过将表中的数据在物理上存放到多个表空间上。这样在查询数据时，会查询相应分区的数据，避免了全表扫描。

　　分区又分为水平分区、垂直分区。

　　水平分区：就是对行进行分区，举个例子来说，就是一个表中有1000万条数据，每100万条数据划一个分区，这样就将表中数据分到10个分区中去。水平分区要通过某个特定的属性列进行分区，比如我用的列就是Date时间。

　　垂直分区：通过对标垂直划分来减少表的宽度，从而提升查询效率。比如一个学生表中，有他相关的信息列，还有论文列以CLOB存储。这些以CLOB存储的论文并不会经常被访问到，这时候就要把这些不经常使用的CLOB划分到另一个分区，需要访问时再调用它。

　　总的来说，分区的主要目的还是避免了全表扫描，从而提升查询速度。

实战开始

　　先来看一下我的建表语句。

-- Create table
create table BUS_DAILY_BALANCE_01
(
  opt_id          NUMBER(18) not null,
  nowdate         date,
  openbank_no     VARCHAR2(40),
  openbank_name   VARCHAR2(40),
  customer_name   VARCHAR2(40),
  customer_no     VARCHAR2(40),
  account_name    VARCHAR2(40),
  account_no      VARCHAR2(40),
  subaccount_no   VARCHAR2(40),
  account_type    VARCHAR2(80),
  account_quality VARCHAR2(80),
  sign_virtual    VARCHAR2(40),
  sign_collect    VARCHAR2(40),
  daily_average   NUMBER(18,2),
  balance         NUMBER(18,2)
)
tablespace TS_PJAVA_DATA
PARTITION BY RANGE (nowdate)
(
  partition BUS_DAILY_P1800 values less than (to_date('2018-01-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1801 values less than (to_date('2018-02-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1802 values less than (to_date('2018-03-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1803 values less than (to_date('2018-04-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1804 values less than (to_date('2018-05-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1805 values less than (to_date('2018-06-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1806 values less than (to_date('2018-07-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1807 values less than (to_date('2018-08-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1808 values less than (to_date('2018-09-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1809 values less than (to_date('2018-10-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1810 values less than (to_date('2018-11-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1811 values less than (to_date('2018-12-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1812 values less than (to_date('2019-01-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1901 values less than (to_date('2019-02-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1902 values less than (to_date('2019-03-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1903 values less than (to_date('2019-04-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1904 values less than (to_date('2019-05-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1905 values less than (to_date('2019-06-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1906 values less than (to_date('2019-07-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1907 values less than (to_date('2019-08-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1908 values less than (to_date('2019-09-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1909 values less than (to_date('2019-10-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1910 values less than (to_date('2019-11-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1911 values less than (to_date('2019-12-01','yyyy-mm-dd')),
  partition BUS_DAILY_P1912 values less than (to_date('2020-01-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2001 values less than (to_date('2020-02-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2002 values less than (to_date('2020-03-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2003 values less than (to_date('2020-04-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2004 values less than (to_date('2020-05-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2005 values less than (to_date('2020-06-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2006 values less than (to_date('2020-07-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2007 values less than (to_date('2020-08-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2008 values less than (to_date('2020-09-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2009 values less than (to_date('2020-10-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2010 values less than (to_date('2020-11-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2011 values less than (to_date('2020-12-01','yyyy-mm-dd')),
  partition BUS_DAILY_P2012 values less than (to_date('2021-01-01','yyyy-mm-dd')),
  partition BUS_DAILY_PMAX values less than (maxvalue)
  tablespace TS_PJAVA_DATA
);
-- Add comments to the columns 
comment on column BUS_DAILY_BALANCE.opt_id
  is 'ID';
comment on column BUS_DAILY_BALANCE.nowdate
  is '日期';
comment on column BUS_DAILY_BALANCE.openbank_no
  is '开户机构名称';
comment on column BUS_DAILY_BALANCE.openbank_name
  is '开户机构';
comment on column BUS_DAILY_BALANCE.customer_name
  is '客户名称';
comment on column BUS_DAILY_BALANCE.customer_no
  is '客户编号';
comment on column BUS_DAILY_BALANCE.account_name
  is '账户名称';
comment on column BUS_DAILY_BALANCE.account_no
  is '账户号';
comment on column BUS_DAILY_BALANCE.subaccount_no
  is '子账户号';
comment on column BUS_DAILY_BALANCE.account_type
  is '账户类型';
comment on column BUS_DAILY_BALANCE.account_quality
  is '账户性质';
comment on column BUS_DAILY_BALANCE.sign_virtual
  is '是否签约虚拟账簿';
comment on column BUS_DAILY_BALANCE.sign_collect
  is '是否签约资金归集';
comment on column BUS_DAILY_BALANCE.daily_average
  is '日均余额';
comment on column BUS_DAILY_BALANCE.balance
  is '余额';

 上面的建表分区语句，没有采用Oracle自动分区，自动分区的分区表名不受控制，是以“SYS_P”开头，当然网上有通过存储过程来修改，可百度查询。

分区表的维护

（1）添加分区

ALTER TABLE tablename ADD PARTITION partition_name VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

 　　注意：以上添加的分区界限应该高于最后一个分区界限。

（2）删除分区

ALTER TABLE tablename DROP PARTITION partition_name;

　　注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

（3）截断分区

　　截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

ALTER TABLE tablename TRUNCATE PARTITION partition_name;

（4）合并分区

　　合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE tablename MERGE PARTITIONS partition_name1,partition_name2 INTO PARTITION partition_name2 UPDATE INDEXES;

　　如果省略update indexes子句的话，必须重建受影响的分区的index;

ALTER TABLE tablename MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;

（5）拆分分区

　　拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE tablename SPLIT PARTITION partition_name AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION partition_name1,PARTITION partition_name2);

（6）接合分区（coalesce）

　　分区接合是针对散列分区或者*-散列子分区的，目的是减少分区数。当某个散列分区接合后，Oracle将其分区的数据分散到其它分区中。被接合的分区是由数据库选择的，接合完成后该分区会被删除，且如果没有使用UPDATE INDEX子句，本地索引和全局索引均将变成不可用，一般需要重建索引。

--散列分区表的散列分区接合
ALTER TABLE table_name COALESCE PARTITION;

（7）重命名分区

ALTER TABLE table_name RENAME PARTITION old_name TO new_name;

（8）移动分区

alter table tablename move partition partition_name tablespace newtablespace;

　　分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。

Select index_name,status From user_indexes Where table_name='tablename';

分区表的查询

（1）跨分区查询

select sum( *) from
(select count(*) cn from t_table_SS PARTITION (partition_name1)
union all
select count(*) cn from t_table_SS PARTITION (partition_name2)
);

（2）查询表上有多少个分区

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tableName';

（3）其他的一些查询

--显示数据库所有分区表的信息：
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 partition_name;