DROP TABLE t_part;
create table t_part(object_id int,object_name varchar2(1000)) partition by range(object_id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (4000),
partition pm values less than (maxvalue)
);
--插入测试数据
INSERT INTO t_part SELECT ROWNUM,t.index_name FROM dba_indexes t;
commit;
--创建本地分区索引
create index IDX_PART_LOCAL on t_part(object_name) local;
--清除分区前,查看索引占用的空间 1600K
SELECT SEGMENT_NAME AS INDEX_NAME,
SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
GROUP BY SEGMENT_NAME;
ALTER TABLE t_part TRUNCATE PARTITION p4 DROP STORAGE;
--清除分区后,查看索引占用的空间 1344K
SELECT SEGMENT_NAME AS INDEX_NAME,
SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
GROUP BY SEGMENT_NAME;
create table t_part(object_id int,object_name varchar2(1000)) partition by range(object_id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (4000),
partition pm values less than (maxvalue)
);
--插入测试数据
INSERT INTO t_part SELECT ROWNUM,t.index_name FROM dba_indexes t;
commit;
--创建本地分区索引
create index IDX_PART_LOCAL on t_part(object_name) local;
--清除分区前,查看索引占用的空间 1600K
SELECT SEGMENT_NAME AS INDEX_NAME,
SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
GROUP BY SEGMENT_NAME;
ALTER TABLE t_part TRUNCATE PARTITION p4 DROP STORAGE;
--清除分区后,查看索引占用的空间 1344K
SELECT SEGMENT_NAME AS INDEX_NAME,
SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
GROUP BY SEGMENT_NAME;