由于数据量越来越大，查询效率越来越慢，考虑到大部分的查询按照年度查询，所以按照年做分区。

--备份数据
create table BAK_TRM_SCORE0104 as
select * from TB_TRM_SCORE;

--删除表，不进回收站
drop table TB_TRM_SCORE purge;

--创建分区表
create table TB_TRM_SCORE
(
  C_OID             NUMBER(38) not null,
  C_OPERATETIME     DATE,
  C_OPERATOR        NUMBER(38),
  C_EMPID           NUMBER(38),
  C_SCORESOURCE     VARCHAR2(64),
  C_ITEM            NUMBER(38),
  C_GETDATE         DATE,
  C_GETSCORE        NUMBER(16,6),
  C_TRAINCLASS      NUMBER(38),
  C_OBJECT          NUMBER(38),
  C_SCORETYPE       VARCHAR2(64),
  C_SCORESTATUS     VARCHAR2(64),
  C_YEAR            VARCHAR2(32),
  C_COURSEHOUR      NUMBER(16,6),
  C_CLASSNAME       VARCHAR2(128),
  C_BEGINTIME       VARCHAR2(32),
  C_ENDTIME         VARCHAR2(32),
  C_GRADE           VARCHAR2(32),
  C_SCORESTATUSNAME VARCHAR2(64),
  C_JOBLEVELNAME    VARCHAR2(128),
  C_RYLBNAME        VARCHAR2(64),
  C_TYPEFORREP      VARCHAR2(32)
)
partition by range (C_YEAR)
(
partition p_2005 values less than ('2006'),
partition p_2006 values less than ('2007'),
partition p_2007 values less than ('2008'),
partition p_2008 values less than ('2009'),
partition p_2009 values less than ('2010'),
partition p_2010 values less than ('2011'),
partition p_2011 values less than ('2012'),
partition p_2012 values less than ('2013'),
partition p_2013 values less than ('2014'),
partition p_2014 values less than ('2015'),
partition p_2015 values less than ('2016'),
partition p_2016 values less than ('2017'),
partition p_2017 values less than ('2018'),
partition p_2018 values less than ('2019'),
partition p_2019 values less than ('2020'),
partition p_2020 values less than ('2021'),
partition p_2021 values less than ('2022'),
partition p_2022 values less than ('2023'),
partition p_2023 values less than ('2024'),
partition p_2024 values less than ('2025'),
partition p_2025 values less than ('2026'),
partition p_2026 values less than ('2027'),
partition p_2027 values less than ('2028'),
partition p_2028 values less than ('2029'),
partition p_2029 values less than ('2030'),
partition p_2030 values less than ('2031'),
partition p_2031 values less than ('2032'),
partition p_2032 values less than ('2033'),
partition p_2033 values less than ('2034'),
partition p_2034 values less than ('2035'),
partition p_2035 values less than ('2036'),
partition p_max values less than (maxvalue)
);

--创建局部索引
create index index_2015010401 on TB_TRM_SCORE (C_EMPID, C_YEAR) local
  tablespace LGEHR
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

--按分区truncate数据，会导致全局索引失效，局部索引不会失效
alter table TB_TRM_SCORE truncate partition p_2014;

--重建全局索引
alter index SYS_C0022340 rebuild;

--还原数据
insert into TB_TRM_SCORE
select * from BAK_TRM_SCORE0104 s
where s.c_year = '2014';

--按分区查找数据
select * from TB_TRM_SCORE partition(p_2014);

--查看分区数目
select partitioning_type, 
       subpartitioning_type, 
       partition_count
  from user_part_tables
 where table_name ='TB_TRM_SCORE';

--查看在哪一列做分区       
select column_name, 
       object_type, 
       column_position
  from user_part_key_columns
 where name ='TB_TRM_SCORE';
 
 --查看各分区大小
 select partition_name, 
       segment_type, 
       bytes
  from user_segments
 where segment_name ='TB_TRM_SCORE';