Oracle表分区例子

1aia_ft_gps_location_data表
1.1创建表空间和数据文件
数据文件路径可根据实际生产环境系统变更
create  tablespace  PARTITION_TABLESPACE_DATA datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_01.dbf' size 100M autoextend on  maxsize unlimited; 


alter  tablespace  PARTITION_TABLESPACE_DATA add  datafile 
' C:\oradata\PARTITION_TABLESPACE_DATA_02.dbf' size 100M autoextend on  maxsize unlimited;


alter tablespace PARTITION_TABLESPACE_DATA add  datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_03.dbf' size 100M autoextend  on  maxsize unlimited;


alter tablespace PARTITION_TABLESPACE_DATA add  datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_04.dbf' size 100M autoextend on  maxsize unlimited;


alter tablespace PARTITION_TABLESPACE_DATA add  datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_05.dbf' size 100M autoextend on  maxsize unlimited;


1.2创建分区表
CREATE TABLE aia_ft_gps_location_data
  (
   PUC_ID       VARCHAR2(64) not null,
   SYSTEM_ID    VARCHAR2(16),
   DEVICE_ID    VARCHAR2(16) not null,
   GPS_DATETIME VARCHAR2(10) not null,
   LONGITUDE    NUMBER not null,
   LATITUDE     NUMBER not null,
   WEIGHT       NUMBER not null,
   PARTITION_MONTH  AS (TO_NUMBER(TO_CHAR(to_date(GPS_DATETIME,'yyyy-MM-dd'), 'MM')))
  )
  PARTITION BY LIST (PARTITION_MONTH)
  (
   PARTITION P1 VALUES (1) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P2 VALUES (2) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P3 VALUES (3) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P4 VALUES (4) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P5 VALUES (5) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P6 VALUES (6) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P7 VALUES (7) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P8 VALUES (8) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P9 VALUES (9) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P10 VALUES (10) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P11 VALUES (11) tablespace PARTITION_TABLESPACE_DATA, 
   PARTITION P12 VALUES (12) tablespace PARTITION_TABLESPACE_DATA
  );




1.3开启数据段压缩和表操作并行度
  alter table aia_ft_gps_location_data move partition "P1" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P2" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P3" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P4" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P5" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P6" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P7" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P8" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P9" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P10" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P11" compress parallel 8;
  alter table aia_ft_gps_location_data move partition "P12" compress parallel 8;


1.4更改分区表可修改分区键
alter table aia_ft_gps_location_data enable row movement;


1.5创建分区索引


CREATE INDEX AIA_IDX_AFGLD_GD ON aia_ft_gps_location_data(GPS_DATETIME) LOCAL 
(  PARTITION p1 TABLESPACE PARTITION_TABLESPACE_DATA, 
　 PARTITION p2 TABLESPACE PARTITION_TABLESPACE_DATA, 
   PARTITION p3 TABLESPACE PARTITION_TABLESPACE_DATA,
   PARTITION p4 TABLESPACE PARTITION_TABLESPACE_DATA, 
　 PARTITION p5 TABLESPACE PARTITION_TABLESPACE_DATA, 
   PARTITION p6 TABLESPACE PARTITION_TABLESPACE_DATA,
   PARTITION p7 TABLESPACE PARTITION_TABLESPACE_DATA, 
　 PARTITION p8 TABLESPACE PARTITION_TABLESPACE_DATA, 
   PARTITION p9 TABLESPACE PARTITION_TABLESPACE_DATA,
   PARTITION p10 TABLESPACE PARTITION_TABLESPACE_DATA, 
　 PARTITION p11 TABLESPACE PARTITION_TABLESPACE_DATA, 
   PARTITION p12 TABLESPACE PARTITION_TABLESPACE_DATA
)


1.6使用建议
select /*+ index(aia_ft_gps_location_data AIA_IDX_AFGLD_GD) */  * from aia_ft_gps_location_data  partition(分区号)
标红为可选项,可不填
/*+ index(aia_ft_gps_location_data AIA_IDX_AFGLD_GD) */ 强制sql查询走索引
partition(分区号) 从哪个分区查询数据库








select partition_name from user_tab_partitions where table_name='T_RANGE';


select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';


按天自动添加分区 (按周)
----创建分区（每7天添加一个分区） 
create table t_day
( seq  number not null,
  update_time  date
  )tablespace PARTITION_TABLESPACE_DATA
  partition by range(update_time)
  interval(numtodsinterval(14,'day'))
  SUBPARTITION subhash
   ( SUBPARTITION p1 TABLESPACE PARTITION_TABLESPACE_DATA,
     SUBPARTITION p2 TABLESPACE PARTITION_TABLESPACE_DATA,
     SUBPARTITION p3 TABLESPACE PARTITION_TABLESPACE_DATA,
     SUBPARTITION P4 TABLESPACE PARTITION_TABLESPACE_DATA
   )
  (partition t_day1 values less than(to_date('2016-12-31','yyyy-mm-dd')) tablespace PARTITION_TABLESPACE_DATA);
  


create table t_day
( seq  number not null,
  update_time  date
  )tablespace PARTITION_TABLESPACE_DATA
  partition by range(update_time)
  interval(numtodsinterval(14,'day'))
  subpartition by hash(seq)
  SUBPARTITION template
(SUBPARTITION p1 TABLESPACE PARTITION_TABLESPACE_DATA,SUBPARTITION p2 TABLESPACE PARTITION_TABLESPACE_DATA,SUBPARTITION p3 TABLESPACE PARTITION_TABLESPACE_DATA,SUBPARTITION P4 TABLESPACE PARTITION_TABLESPACE_DATA)
(partition t_day1 values less than(to_date('2016-12-31','yyyy-mm-dd')) tablespace PARTITION_TABLESPACE_DATA);




set linesize 999
set pagesize 999
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a10
col TABLESPACE_NAME for a10
 
select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_DAY';




select * from t_day partition(sys_p83);


select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_YEAR';




partition list single     --扫描单个分区
partition list iterator   --连续扫描N个分区
partition list inlist       --不连续扫描N个分区
partition list full         --扫描全部分区




select * from t_day where update_time between to_date('2017-01-01','yyyy-MM-dd') and to_date('2017-06-30','yyyy-MM-dd');




begin
 for i in 1..7 loop
  insert into t_day values(i, sysdate+i*7);
 end loop;
commit;
end;
/