一个二级分区的oracle表,该表还有一级子分区索引,truncate该表的内容后,需要重建该表的索引。表结构如下:CREATE TABLE EVENT ("OCCUR_TIME" DATE NOT NULL,"ID" NUMBER(10), "TYPE" NUMBER(10), CONSTRAINT "PK_EVENT" PRIMARY KEY("OCCUR_TIME") USING INDEX TABLESPACE "PLE_INDEX") TABLESPACE "SAMPLE"
PARTITION BY RANGE ("OCCUR_TIME") SUBPARTITION BY LIST ("ID")
SUBPARTITION TEMPLATE
(
SUBPARTITION REGION_1 values(1) TABLESPACE RT_SAMPLE, SUBPARTITION REGION_2 values(2) TABLESPACE RT_SAMPLE
)
(
PARTITION "EVENT_1" VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE "SAMPLE",
PARTITION "EVENT_2" VALUES LESS THAN (TO_DATE('2010-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE "RT_SAMPLE")索引:
CREATE INDEX "EVENT_IDX" ON EVENT ("OCCUR_TIME")LOGGING LOCAL( PARTITION "EVENT_IDX_1" TABLESPACE "PLE_INDEX" , PARTITION "EVENT_IDX_2" TABLESPACE "PLE_INDEX" )
还有一个分区表的问题,一张大表建按时间建子分区,用一个表空间和用多个表空间是否在效率上有差别(都在一个物理硬盘上)?
PARTITION BY RANGE ("OCCUR_TIME") SUBPARTITION BY LIST ("ID")
SUBPARTITION TEMPLATE
(
SUBPARTITION REGION_1 values(1) TABLESPACE RT_SAMPLE, SUBPARTITION REGION_2 values(2) TABLESPACE RT_SAMPLE
)
(
PARTITION "EVENT_1" VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE "SAMPLE",
PARTITION "EVENT_2" VALUES LESS THAN (TO_DATE('2010-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE "RT_SAMPLE")索引:
CREATE INDEX "EVENT_IDX" ON EVENT ("OCCUR_TIME")LOGGING LOCAL( PARTITION "EVENT_IDX_1" TABLESPACE "PLE_INDEX" , PARTITION "EVENT_IDX_2" TABLESPACE "PLE_INDEX" )
还有一个分区表的问题,一张大表建按时间建子分区,用一个表空间和用多个表空间是否在效率上有差别(都在一个物理硬盘上)?
因为放到一个表空间中 表空间肯定大 搜索需要的表空间也需要时间
但是估计差别不会太大
因为比起和不分区的差别 这个差别就可以忽略了