Altering Index Attributes

Just as the syntax and operation of the CREATE INDEX statement closely followed that of the CREATE TABLE index, you can alter an existing index with the ALTER INDEX statement. There are not as many options for modifying an index as for a table because an index is used for a more focused purpose.

• Types of modifications
  There are several ways you can alter an existing index. You can:
  1. Rename an index.
  2. Alter the storage characteristics of the index, to move it to a different tablespace.
  3. Change the degree of parallelism for an index.
  4. Modify the partitions used by a partitioned index.
  5. Turn logging on and off for the index. You might do this to avoid the overhead of logging as you create a new index on a table. Because the index build can be redone without losing any data, you are not risking much to turn logging off, and you are saving some overhead.
  6. Change the index to a reverse order index, described in a sidebar in the next lesson.
  7. REBUILD or COALESCE an index, described in the next module.
  
  You cannot change the columns in an index through the ALTER INDEX command.

Example
If you want to change the name of the BIDDER_CLIENT_ID index, use the following syntax.

ALTER INDEX bid_client_idx 
RENAME TO bidder_cliend_idx;

The presence of a new index may alter how an existing application works. In most cases, this is the intended behavior: You are creating the index in order to change the execution path chosen by a query. The new index should be designed to support the known query execution paths, and it will slow the performance of inserts and other DML operations. As of Oracle 11g, you can introduce an index to your environment in stages, first creating it and then making it visible to the optimizer. After creating an index, you can tell the optimizer to not consider it when generating execution plans via the alter index command

alter index BA$TITLE_AUTHOR invisible;

With the index in an invisible state, you can now test your commands to determine exactly how the index will impact your operations. The invisible index will only be used if you use an INDEX hint that explicitly names the index. The index will not be considered by the optimizer for general usage while it is invisible. Select the Visibility column from the USER_INDEXES data dictionary view to determine if an index has been set to be invisible to the optimizer. Regardless of its visibility status, the index will continue to use space and impact DML operations.

Several hints are available to direct the optimizer in its use of indexes. The INDEX hint is the most commonly used index-related hint. The INDEX hint tells the optimizer to use an index-based scan on the specified table. You do not need to mention the index name when using the INDEX hint, although you can list specific indexes if you choose.
For example, the following query uses the INDEX hint to suggest the use of an index on the BOOKSHELF table during the resolution of the query:

select /*+ index(bookshelf bookshelf$category) */ Title
from BOOKSHELF
where CategoryName = 'ADULTNF';

SQL> select address from address 
where upper(name) like 'JO%' AND 
(name like 'J%' or name like 'j%');

According to the rules provided earlier in this section, the preceding query should use the index without the hint being needed. However, if the index is nonselective or the table is small, the optimizer may choose to ignore the index. If you know that the index is selective for the data values given, you can use the INDEX hint to force an index-based data access path to be used. In the hint syntax, name the table (or its alias, if you give the table an alias) and the name of the suggested index(optional ). The optimizer may choose to disregard any hints you provide. If you do not list a specific index in the INDEX hint, and multiple indexes are available for the table, the optimizer evaluates the available indexes and chooses the index whose scan is likely to have the lowest cost. The optimizer could also choose to scan several indexes and merge them via the

AND-EQUAL operation.

A second hint, INDEX_ASC, functions the same as the INDEX hint: INDEX_ASC suggests an ascending index scan for resolving queries against specific tables. A third index-based hint, INDEX_DESC, tells the optimizer to scan the index in descending order (from its highest value to its lowest). To suggest an index fast full scan, use the INDEX_FFS hint. The ROWID hint is similar to the INDEX hint, suggesting the use of the TABLE ACCESS BY INDEX ROWID method for the specified table. The AND_EQUAL hint suggests the optimizer merge the results of multiple index scans. The next lesson explores rebuilding an index.

For table accesses, there are 2 relevant hints:

1. FULL
2. ROWID

The FULL hint tells Oracle to perform a full table scan on the listed table.

SELECT /*+ FULL(EMP) */ * FROM EMP
WHERE EMPNO = 499;

The ROWID hint tells the optimizer to use a Table Access by Rowid operation to access the rows in the table. In general, you should use a "Table Access by Rowid" operation whenever you need to return rows quickly to users and whenever the tables are large. To use the "Table Access by Rowid" operation, you need to know either 1) the ROWID values or 2) use an index.
If a large table has not been marked as a cached table and you wish for its data to stay in the SGA after the query completes, you can use the CACHE hint to tell the optimizer to keep the data in the SGA for as long as possible. The CACHE hint is usually used in conjunction with the FULL hint.

SELECT /*+ FULL(WORKER) CACHE(WORKER) */ * 
FROM WORKER;

The INDEX hint tells the optimizer to use an index-based scan on the specified table. You do not need to mention the index name when using the INDEX hint, although you can list specific indexes if you choose.

SELECT /* + INDEX(LODGING) */ LODGING FROM LODGING
WHERE MANAGER = 'GLENN GOULD';

The above query should use the index without the hint being needed. However, if the index is non-selective and you are using the CBO, then the optimizer may choose to ignore the index during the processing. In that case, you can use the "INDEX hint" to force an index-based data access path to be used.
There are several hints available in ORACLE such as ALL_ROWS, FIRST_ROWS, RULE, USE_NL, USE_MERGE, USE_HASH for tuning the queries.