Log in
updated 11:29 AM UTC, May 4, 2016

Local Indexes for Partitioning

  • Written by Manjunatha Srinivas
  • Published in Database

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

Powered by Bullraider.com
Follow Us on Twitter
Find Us on Facebook
Follow Us on Google
Follow Us on Pinterest