Alter Index Tips Oracle Tips by Burleson Consulting

If the Remote DBA suspects that an index’s storage clause is improper, he or she can run the script in Source 6.4 to show the space used for the average entry in the index. This data can then be input to the space calculation formula to get a more accurate sizing estimate. The Remote DBA can then use the ALTER INDEX command to alter the index’s storage clause for future extents, or drop and re-create the index with better parameters.

Indexes can be altered to change their storage clauses, and in version 7.3.4 and all 8 versions, they can be rebuilt on the fly. Oracle8i and Oracle9i also allow either online (where table access is permitted) or offline (where table access is restricted). The alteration will affect only the storage allocations of future extents. To alter rows used in the index, unique versus nonunique, or all of the storage extents for an existing index, it must be dropped and re-created. To alter the storage within an existing index, use the ALTER INDEX command.

 Script to calculate average length of an index entry.

rem  *******************************************************
rem
rem  NAME:  IN_CM_SZ.sql    
rem
rem  HISTORY:
rem  Date             Who              What
rem  --------- -------------------  ----------
rem  01/20/93  Michael Brouillette  Creation
rem  09/22/01  Mike Ault            Updated to 9i
rem   
rem  FUNCTION:  Compute the space used by an entry for an
rem     existing index.
rem
rem  NOTES:  Currently requires Remote DBA.
rem
rem  INPUTS:
rem         tname  = Name of table.
rem         towner = Name of owner of table.
rem         iname  = Name of index.
rem         iowner = Name of owner of index.
rem         cfile  = Name of output file SQL Script.
rem  *******************************************************
COLUMN dum1       NOPRINT
COLUMN isize      FORMAT 999,999,999.99
COLUMN rcount     FORMAT 999,999,999 NEWLINE
ACCEPT tname  PROMPT 'Enter table name: '
ACCEPT towner PROMPT 'Enter table owner name: '
ACCEPT iname  PROMPT 'Enter index name: '
ACCEPT iowner PROMPT 'Enter index owner name: '
ACCEPT cfile  PROMPT 'Enter name for output SQL file: '
SET PAGESIZE 0 HEADING OFF VERIFY OFF TERMOUT OFF
SET feedback OFF TRIMSPOOL ON SQLBL OFF
SET SQLCASE UPPER NEWPAGE 3
SPOOL &cfile..sql
SELECT -1 dum1,
       'SELECT ''Index '||'&iowner..&iname'||' on table '
  FROM dual
UNION
SELECT 0,
       '&towner..&tname'||' has '',
       nvl(COUNT(*),0) rcount,'' entries of '', ('
  FROM dual
UNION
SELECT column_id,
      'SUM(NVL(vsize('||column_name||'),0)) + 1 +'
  FROM Remote DBA_tab_columns
 WHERE table_name = '&tname'
   AND owner = upper('&towner') AND column_name IN
                   (SELECT column_name FROM Remote DBA_ind_columns
                     WHERE table_name = upper('&tname')
                       AND table_owner = upper('&towner')
                       AND index_name = upper('&iname')
                       AND index_owner = upper('&iowner'))
                       AND column_id <> (select max(column_id)
                                        FROM Remote DBA_tab_columns
                                        WHERE table_name = upper('&tname')
                                        AND owner = upper('&towner')
                                        AND column_name IN
                      (SELECT column_name FROM Remote DBA_ind_columns
                        WHERE table_name = upper('&tname')
                          AND table_owner = upper('&towner')
                          AND index_name = upper('&iname')
                          AND index_owner = upper('&iowner')))
UNION
See code depot for full script
SELECT column_id,
      'SUM(NVL(vsize('||column_name||'),0)) + 1)'
  FROM Remote DBA_tab_columns
  WHERE table_name = upper('&tname') AND owner = upper('&towner')
   AND column_name IN
                   (SELECT column_name FROM Remote DBA_ind_columns
                     WHERE table_name = upper('&tname')
                       AND table_owner = upper('&towner')
                       AND index_name = upper('&iname')
                       AND index_owner = upper('&iowner'))
                       AND column_id = (SELECT MAX(column_id)
                     FROM Remote DBA_tab_columns
                     WHERE table_name = upper('&tname')
                      AND owner = upper('&towner')
                      AND column_name IN
                      (SELECT column_name FROM Remote DBA_ind_columns
                        WHERE table_name = upper('&tname')
                          AND table_owner = upper('&towner') 
                          AND index_name = upper('&iname')
                          AND index_owner = upper('&iowner')))
UNION
SELECT 997,
       '/ COUNT(*) + 11 isize, '' bytes each.'''  from dual
UNION
SELECT 999,  'FROM &towner..&tname.;'  FROM dual;
SPOOL OFF
SET TERMOUT ON feedback 15 PAGESIZE 20 SQLCASE MIXED
SET NEWPAGE 1
START &cfile
CLEAR columns
UNDEF tname
UNDEF towner
UNDEF iname
UNDEF iowner
UNDEF cfile

The ALTER INDEX command is detailed in the SQL reference manual, in the documentation on http://technet.oracle.com. Instead of filling pages with syntax and definitions, let’s look at some specific ALTER INDEX examples.

The ALTER command can be used to change the storage and physical characteristics of an index, but not the contents of the index. To change the actual columns an index applies to, or to change the order of columns in a concatenated index, the index must be dropped and rebuilt. Changes to storage or physical characteristics apply only to new index extents, not existing extents, unless the index is rebuilt using the REBUILD clause. Let’s look at some specific examples using the ALTER INDEX command.

The most simple use of the ALTER INDEX command is to alter an index’s storage clause for future extents:

ALTER INDEX pk_clientsv8i
STORAGE (NEXT 2m);

In some cases, an index may have been misnamed and thus must be renamed to fit into the  application’s naming guidelines:

ALTER INDEX test_new_index RENAME TO lu2_clientsv8i;

For a more complex example, assume we want to rebuild an index into a new location. Prior to the addition of the REBUILD clause in late 7.3 versions of Oracle, this would have involved dropping related constraints, dropping the index, and re-creating the index in the new location. Since Oracle8i, the index can be rebuilt online with access still available to the underlying tables:

ALTER INDEX pk_clientsv8i REBUILD
TABLESPACE tele_index
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
ONLINE;

TIP: To use the ONLINE clause, the COMPATIBLE parameter must be set to 8.1.3.0.0 or later.

Another new clause since Oracle8 is the COALESCE command. In cases where an index has experienced browning, and the percentage of browning exceeds 15 to 30 percent, it is no longer required that the index be dropped and rebuilt; instead, the COALESCE option can be specified with the ALTER INDEX command:

ALTER INDEX pk_clientsv9i COALESCE;

Rebuilding Indexes

Occasionally, the Remote DBA may be required to rebuild indexes. In late Oracle version 7.3 and in Oracle8,Oracle8i, and Oracle9i, the ALTER INDEX . . . REBUILD command can be used to rebuild indexes on the fly. In releases prior to 8i, the rebuild was done offline, restricting access to the table to read-only. The ONLINE clause new in Oracle8i allows online rebuild of indexes. In earlier versions (pre-7.3), the indexes have to be rebuilt using drop and re-create scripts. If the scripts used to initially create the system are available, this is a relatively simple matter.

IMPORTANT - See my updates notes on the issues surrounding index rebuilding.

The Remote DBA can use the ANALYZE command to validate an index’s structures. The format of this command follows. This data can help the Remote DBA determine if a specific index has become corrupted and must be rebuilt.

ANALYZE INDEX [schema.]index
VALIDATE STRUCTURE;

The results are supplied to the Remote DBA on-screen and are placed in a view called index_stats, which is dropped upon session exit.

In Oracle9i, the new package DBMS_METADATA can be used to get DML to rebuild complex indexes. The DBMS_METADATA function FETCH_DDL returns a CLOB data item that contains the text to rebuild any database object.

Dropping Indexes

Indexes occasionally have to be dropped. Sometimes they are built incorrectly or shouldn’t have been built at all. Other times, especially in early Oracle7 releases (prior to 7.3), in order to rebuild an index it had to be dropped first. Finally, dropping an index may be required to speed import or SQLLOADER during large data loads.

The DROP INDEX Command

The DROP INDEX command has the following format:

DROP INDEX [schema.]index_name [FORCE];

Of course, you must own the index or have the DROP ANY INDEX privilege to drop an index. The FORCE clause only applies to DOMAIN type indexes and forces a drop of the index even if its status is LOADING or its invocation returns an error.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.