droping index partitions

[summer06]

According to oracle documentation, you cannot explicitly drop a parition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
There are some local indexes on one of the list partitioned tables. I dropped a couple of partitions, but the corresponding indexes are still there. Anybody knows why this happens?

Thanks!

[slimdave]

The indexes would still be there, but the partitions corresponding the the dropped table partitions would have gone if the index is locally partitioned -- if it is globally partitioned then the index partitions would not be affected: http://download-west.oracle.com/docs...c.htm#CNCPT312

[jmodic]

Are you sure those are LOCAL partitioned indexes? Can you show us a list of table partitions and a list of their corresponding index partitions from the data dictionary (USER_TAB_PARTITIONS, USER_INDEXES, USER_IND_PARTITIONS)?

[summer06]

I dropped partitions p1996, p1995 on the table. The partitions of p1996, p1995 on every local partitioned indexes are still there.

[slimdave]

Are you sure those are LOCAL partitioned indexes? Can you show us a list of table partitions and a list of their corresponding index partitions from the data dictionary (USER_TAB_PARTITIONS, USER_INDEXES, USER_IND_PARTITIONS)?

you can query ALL_PART_INDEXES.LOCALITY for that info.

[summer06]

SQL> select index_name, locality from user_part_indexes
2 where table_name = 'VEHICLE';

INDEX_NAME LOCALI
------------------------------ ------
UQ_VIN LOCAL
XIF12VEHICLE LOCAL
XIF4VEHICLE LOCAL
XIF9VEHICLE LOCAL
XPK_VEHICLE LOCAL


1 select partition_name from user_tab_partitions
2* where table_name = 'VEHICLE'
SQL> /

PARTITION_NAME
------------------------------
P_2005
P_2004
P_2003
P_2002
P_2001
P_2000
P_1999
P_1998
P_1997
P_2006
P_2007


Here is one of the indexes name with it's corresponding partition_name. The weird thing is that the partition_name in index is different from partition_name in the table. What happened?


XIF9VEHICLE P_2000
XIF9VEHICLE P_2001
XIF9VEHICLE P_2007
XIF9VEHICLE P_2006
XIF9VEHICLE P_1996
XIF9VEHICLE P_1995
XIF9VEHICLE P_1994
XIF9VEHICLE P_2005
XIF9VEHICLE P_2004
XIF9VEHICLE P_2003
XIF9VEHICLE P_2002

[jmodic]

you can query ALL_PART_INDEXES.LOCALITY for that info.

Sure. But with those three views I was not aiming for the OP to find out if the index is indeed local or not. I ment if he is allready certain that index indeed is local, with those three views he would be able to find out himself that the HIGH_VALUE in both corresponding table and index partitions are the same (to show that there is no bug in oracle - when you drop the partition, the corresponding local partition of the index is dropped too). However I didn't realized until now that he is not using RANGE partitioning, but rather LIST. So indeed those three views are rather useless in his situation. My bad.

The weird thing is that the partition_name in index is different from partition_name in the table. What happened?

The name of the partition does not tell anything about the deffinition or the organization of the partition. So you can't tell absolutely anything about the partition from its name, realy. What realy happened in your example, apparantly, is that someone named partitions with no actual correspondence between their names and their partition key values.

BTW, from the names of your table and index partitions I would deduce that you are partitioning by some date column. Wouldn't RANGE partitioning scheme be more appropriate than a LIST one?