Local Indexes for Partitioning

Local Indexes for Partitioning


SELECT i.table_name,
  i.index_name,
  ip.partition_name,
  ip.NUM_ROWS,
  ip.blevel,
  ip.LAST_ANALYZED,
  i.status  AS index_status,
  ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name = ip.index_name )
where table_name ='&tbl'
ORDER BY i.table_name,
  i.index_name,
  ip.partition_name;

If partition_status is "USABLE" then everything is ok. You don't need to rebuild anything.

If partition_status is "UNUSABLE" then you can rebuild the unusable partition by doing:

ALTER INDEX Index Name
REBUILD PARTITION partition name;

-- to confirm that indexes are global or local 

select Index_name,LOCALITY FROM  ALL_PART_INDEXES 
where  table_name='table_name'  ;

select INDEX_NAME, UNIQUENESS, TABLESPACE_NAME,NUM_ROWS, DISTINCT_KEYS 

from user_indexes
where table_name='table_name'
; 

To gather stats of the partition of the local index

  SELECT i.table_name,
  ip.index_name,
  ip.partition_name,
  ip.NUM_ROWS,
  ip.DISTINCT_KEYS,
  ip.HIGH_VALUE,
  IP.PARTITION_POSITION "POS",  
  ip.blevel,
  ip.LAST_ANALYZED,
  --ip.status  AS index_status,
  ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name = ip.index_name )
where table_name ='&tbl'
--AND PARTITION_POSITION=12
ORDER BY i.table_name,
  i.index_name,
  ip.partition_name;

  EXEC  DBMS_STATS.GATHER_INDEX_STATS('USER','INDNAME','PART_NAME',20);