Oracle分区表 linst分区

创建表空间

create  tablespace PARTITION_TABLESPACE_DATA datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_01.dbf' size 100Mautoextend on  maxsize unlimited;

 

alter  tablespace PARTITION_TABLESPACE_DATA add  datafile

' C:\oradata\PARTITION_TABLESPACE_DATA_02.dbf'size 100M autoextend on  maxsizeunlimited;

 

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

 

alter tablespace PARTITION_TABLESPACE_DATAadd  datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_04.dbf'size 100M autoextend on  maxsizeunlimited;

 

alter tablespace PARTITION_TABLESPACE_DATAadd  datafile 'C:\oradata\PARTITION_TABLESPACE_DATA_05.dbf'size 100M autoextend on  maxsizeunlimited;

 

创建list分区索引 使用oracle 11g 新特性隐示列 

CREATETABLE 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) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P2 VALUES (2) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P3 VALUES (3) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P4 VALUES (4) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P5 VALUES (5) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P6 VALUES (6) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P7 VALUES (7) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P8 VALUES (8) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P9 VALUES (9) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P10 VALUES (10) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P11 VALUES (11) tablespacePARTITION_TABLESPACE_DATA,

   PARTITION P12 VALUES (12) tablespacePARTITION_TABLESPACE_DATA

  );

 

每个分区都进行数据段压缩 并开启8个并行度  

  altertable aia_ft_gps_location_data move partition "P1" compress parallel8;

  alter table aia_ft_gps_location_data movepartition "P2" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P3" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P4" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P5" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P6" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P7" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P8" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P9" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P10" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P11" compress parallel 8;

  alter table aia_ft_gps_location_data movepartition "P12" compress parallel 8;

允许分区字段进行update操作

alter tableaia_ft_gps_location_data enable row movement;

 

创建分区索引

CREATEINDEX AIA_IDX_AFGLD_GD ON aia_ft_gps_location_data(GPS_DATETIME) LOCAL

(  PARTITION p1 TABLESPACEPARTITION_TABLESPACE_DATA,

　 PARTITION p2 TABLESPACEPARTITION_TABLESPACE_DATA,

  PARTITION p3 TABLESPACE PARTITION_TABLESPACE_DATA,

   PARTITION p4 TABLESPACEPARTITION_TABLESPACE_DATA,

　 PARTITION p5 TABLESPACEPARTITION_TABLESPACE_DATA,

   PARTITION p6 TABLESPACEPARTITION_TABLESPACE_DATA,

   PARTITION p7 TABLESPACE PARTITION_TABLESPACE_DATA,

　 PARTITION p8 TABLESPACEPARTITION_TABLESPACE_DATA,

   PARTITION p9 TABLESPACEPARTITION_TABLESPACE_DATA,

   PARTITION p10 TABLESPACEPARTITION_TABLESPACE_DATA,

　 PARTITION p11 TABLESPACEPARTITION_TABLESPACE_DATA,

   PARTITION p12 TABLESPACEPARTITION_TABLESPACE_DATA

)

 

hins使用建议

select /*+index(aia_ft_gps_location_data AIA_IDX_AFGLD_GD) */  * from aia_ft_gps_location_data  partition(分区号)

标红为可选项,可不填

/*+ index(aia_ft_gps_location_dataAIA_IDX_AFGLD_GD) */ 强制sql查询走索引

partition(分区号) 从哪个分区查询数据库