Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Partitions & Global Indexes
> Since the data apparently is not required now, I am thinking of
> dropping the partitions which does not affect the status of the Global
> indexes.
As you can see from the little test case below, you're obviously
mistaken. Even when
using drop partition, global indexes on the table are going to be
invalidated.
1 CREATE TABLE PART_TEST
2 (
3 ID NUMBER, 4 TEXT VARCHAR2(50)
8 PARTITION P1 VALUES LESS THAN (5), 9 PARTITION P2 VALUES LESS THAN (10), 10 PARTITION P3 VALUES LESS THAN (MAXVALUE)11* )
SYSTEM_at_DB002_2> ALTER TABLE PART_TEST ADD (
2 CONSTRAINT PART_TEST_PK
3 PRIMARY KEY
4 (ID));
Table altered.
SYSTEM_at_DB002_2> insert into part_test values (1, 'NOTHING');
1 row created.
SYSTEM_at_DB002_2> insert into part_test values (6, 'NOTHING')
1 row created.
SYSTEM_at_DB002_2> insert into part_test values (12, 'NOTHING')
1 row created.
SYSTEM_at_DB002_2> commit;
Commit complete.
Elapsed: 00:00:00.00
SYSTEM_at_DB002_2> select index_name, status from dba_indexes
2 where table_name = 'PART_TEST';
INDEX_NAME STATUS ------------------------------ -------- PART_TEST_PK VALID
SYSTEM_at_DB002_2> alter table PART_TEST drop partition P3;
Table altered.
SYSTEM_at_DB002_2> select index_name, status from dba_indexes 2 where table_name = 'PART_TEST';
INDEX_NAME STATUS ------------------------------ -------- PART_TEST_PK UNUSABLE
-- Dusan BolekReceived on Mon Feb 06 2006 - 03:11:08 CST