Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Partitions & Global Indexes

Re: Oracle Partitions & Global Indexes

From: Dusan Bolek <spambin_at_seznam.cz>
Date: 6 Feb 2006 01:11:08 -0800
Message-ID: <1139217068.052345.73340@g43g2000cwa.googlegroups.com>


> 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)

  5 )
  6 PARTITION BY RANGE (ID)
  7 (
  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> /
Table created.

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 Bolek
Received on Mon Feb 06 2006 - 03:11:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US