Indexes: Alter Index

Description: Example of altering an index (rename, collect statistics, etc).

ALTER INDEX index_name
  RENAME TO new_index_name;
 
-- for w3mentor:
 
    ALTER INDEX supplier_sales
      RENAME TO supplier_metrics;
 
-- rename the index called supplier_sales to supplier_metrics.
 
 
-- you can use the ALTER INDEX command to collect statistics if
-- none were collected when the index was created
-- syntax for collecting statistics on an index is:
 
    ALTER INDEX index_name
      REBUILD COMPUTE STATISTICS;
 
 
-- for w3mentor:
 
ALTER INDEX supplier_metrics
  REBUILD COMPUTE STATISTICS;
 
 
 
-- Alter index monitor usage
-- syntax:
ALTER INDEX <index_name> MONITORING USAGE;
 
-- for w3mentor:
ALTER INDEX idx_city_state MONITORING USAGE;
 
exec DBMS_STATS.some_stats(OWNNAME=>'REGION', INDNAME=>'IDX_CITY_STATE');
 
SELECT COUNT(*)
FROM city_state
WHERE city = 'TULSA';
 
SELECT *
FROM v$object_usage;
 
ALTER INDEX idx_city_state NOMONITORING USAGE;
 
 
-- Alter index, rebuild and change tablespace
-- syntax:
ALTER INDEX <index_name>
REBUILD TABLESPACE <tablspace_name>;
 
-- for w3mentor:
SELECT index_name, tablespace_name
FROM user_indexes;
 
ALTER INDEX idx_city_state
REBUILD TABLESPACE sales_us;
 
SELECT index_name, tablespace_name
FROM user_indexes;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.