droping index partitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: droping index partitions

  1. #1
    Join Date
    Aug 2005
    Posts
    11

    droping index partitions

    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!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Aug 2005
    Posts
    11
    I dropped partitions p1996, p1995 on the table. The partitions of p1996, p1995 on every local partitioned indexes are still there.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by 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)?
    you can query ALL_PART_INDEXES.LOCALITY for that info.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2005
    Posts
    11
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by slimdave
    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.

    Quote Originally Posted by summer06
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width