Oracle table partitions learning

Today, I was perplexed with a problem related to a partitioned table in oracle. My concern was to determine when I drop a partition of a table what happens the indexes on that table. I figured it out like this.

I created a partitioned test table as follows

CREATE TABLE TEST (
COLUMN1 NUMBER(9),
COLUMN2 NUMBER(9),
)
PARTITION BY LIST (COLUMN1)
(PARTITION LOW_PART VALUES (-1)
);

I created local bitmap indexes on COLUMN1 column of this table using a stored procedure

EXEC CREATE_BITMAP_INDEXES('TEST', 'COLUMN1');

This procedure created a local bitmap index IX_TEST_COLUMN1 on this table.
I then queried oracle about the state of bitmap indexes just created by this query

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS WHERE PARTITION_NAME = 'LOW_PART';

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence the created bitmap index is in usable state just after creation.
Now I inserted lots of data into this table from another staging table

INSERT /*+ APPEND */ INTO TEST
(COLUMN1, COLUMN2)
(SELECT COLUMN1, COLUMN2 FROM TEST_STAGING);
COMMIT;

Again I queried oracle about the state of local bitmap index.

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS WHERE PARTITION_NAME = 'LOW_PART';

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Again this index is in usable state. Hence local indexes status does not change upon insertion of data.
Now I tried to be tricky. I just created a new partition named as HIGH_PART on the table TEST.

ALTER TABLE TEST ADD PARTITION HIGH_PART VALUES (1000);

I then again queried oracle about the state of bitmap indexes

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS;

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence the created bitmap index is in usable state even after new partition addition.

Now I dropped the default partition as follows

ALTER TABLE TEST DROP PARTITION LOW_PART;

I then again queried oracle about the state of bitmap indexes

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS;

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence it is verified that local bitmap indexes on a partitioned table does not become unusable on
1. insertion of data
2. addition of new partition(When you add a partition, actually, a new partition is created for the local bitmap index as well)
3. drop of a partition (When you drop a partition, actually, the corresponding partition from the local bitmap index is dropped as well)

So when and which kind of indexes become unusable.
If you have global indexes on a partitioned table, it will become unusable when
1. you are trying to drop a partition.
2. you are splitting a partition to two new partitions
3. you are moving partitions

In that cases you will have to explicitly rebuild those global indexes as follows.

ALTER INDEX G_IX_TEST_COLUMN1 REBUILD PARTITION HIGH_PART;

Here it is assumed that G_IX_TEST_COLUMN1 is a global index on HIGH_PART partition of TEST table.

Sphere: Related Content