大数据量之表分区处理

1.创建两个表空间，数据存入GIS_DATA，索引存入GIS_INDEX

create tablespace GIS_DATA LOGGING DATAFILE 'sde_data.dbf' size 500m autoextend on next 50m maxsize unlimited;  
create tablespace GIS_INDEX LOGGING DATAFILE 'sde_index.dbf' size 500m autoextend on next 50m maxsize unlimited;  

 2.t_loc_space按周分区，采用interval分区。分别对SMX+SMY和type+type_id建立索引。

create table t_loc_space(
SMID  INTEGER not null,      
SMX  NUMBER(38,16)  default 0   not null,
SMY  NUMBER(38,16)  default  0  not null,    
LOCATION_TIME  NUMBER(37)  default  0   not null, 
)
partition by range(LOCATION_TIME)
--按周分区
interval(604800000)
store in (GIS_DATA)
(
	partition t_p1 values less than (1328054400000)
)
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

ALTER TABLE t_loc_space ADD (
  PRIMARY KEY
 (SMID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));
create index idx_loc_space_xy on t_loc_space(smx, smy) tablespace GIS_INDEX;
create index idx_loc_space_search on t_loc_space(TYPE, TYPE_ID) pctfree 30 storage(initial 200K next 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE GIS_INDEX