Pro Oracle Database 11g Administration (36 page)

• The redo isn’t generated that would be required to recover the index in the event of a media failure.

• Subsequent direct-path operations also won’t generate the redo required to recover the index information in the event of a media failure.

Here’s an example of creating an index with the NOLOGGING clause:

create index inv_idx1 on inv(inv_id, inv_id2)

nologging

tablespace inv_mgmt_index;

The main advantage of NOLOGGING is that when you create the index, a minimal amount of redo information is generated, which can have significant performance implications for a large index. The disadvantage is that if you experience a media failure soon after the index is created (or have records inserted via a direct-path operation), and you must do a restore from a backup that was created prior to the index, you may see this error when the index is accessed:

ORA-01578: ORACLE data block corrupted (file # 4, block # 11407)

ORA-01110: data file 4: '/ora01/dbfile/O11R2/inv_mgmt_index01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

This error indicates that the index is logically corrupt. In this scenario you must re-create or rebuild the index before it’s usable. In most scenarios, it’s acceptable to use the NOLOGGING clause when creating an index, because the index can be re-created or rebuilt without affecting the table on which the index is based.

You can run this query to view whether an index has been created with NOLOGGING: SQL> select index_name, logging from user_indexes;

Implementing Invisible Indexes

In Oracle Database 11
g
and higher, you have the option of making an index invisible to the optimizer.

Oracle still maintains invisible indexes but doesn’t make them available for use by the optimizer. If you want the optimizer to use an invisible index, you can do so with a SQL hint.

Invisible indexes have a couple of interesting uses:

183

CHAPTER 8 ■ INDEXES

• Altering an index to invisible before dropping it allows you to quickly recover if you later determine that the index is required.

• You can add an invisible index to a third-party application without affecting existing code or support agreements.

These two scenarios are discussed in the following subsections.

Making an Existing Index Invisible

Suppose you’ve identified an index that isn’t being used and are considering dropping it. In earlier releases of Oracle, you could mark the index as UNUSABLE and then later drop indexes that you were certain weren’t being used. If you later determined that you needed an unusable index, the only way to re-enable the index was to rebuild it. For large indexes, this could take a long time and a lot of database resources.

Making an index invisible has the advantage that it only tells the optimizer to not use the index. The invisible index is still maintained as the underlying table has records inserted, updated, or deleted. If you decide that you later need the index, there is no need to rebuild it.

Use the INVISIBLE clause to make an index invisible:

SQL> alter index inv_idx1 invisible;

Next, monitor SQL queries and applications for degraded performance. After you’re confident there will be no bad performance effects, you can drop the index:

SQL> drop index inv_idx1;

You can verify the visibility of an index via this query:

SQL> select index_name, status, visibility from user_indexes;

Here’s some sample output:

INDEX_NAME STATUS VISIBILITY

------------------------------ -------- ----------

INV_IDX1 VALID INVISIBLE

COMPUTER_SYSTEM_UK1 VALID VISIBLE

Use the VISIBLE clause to make an invisible index visible to the optimizer again: SQL> alter index inv_idx1 visible;

Guaranteeing Application Behavior Is Unchanged When You Add an Index

You can also use an invisible index is when you’re working with third-party applications. Often, third-party vendors don’t support customers adding their own indexes to an application. However, there may be a scenario in which you’re certain you can increase a query’s performance without impacting other queries in the application. You can create the index as invisible and then explicitly instruct a query to use the index via a hint:

SQL> create index inv_idx1 on inv(inv_id) invisible;

Next, ensure that the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter is set to true. This instructs the optimizer to consider invisible indexes:

SQL> alter system set optimizer_use_invisible_indexes=true;

184

CHAPTER 8 ■ INDEXES

Now, use a hint to tell the optimizer that the index exists:

SQL> select /*+ index (inv INV_IDX1) */ inv_id from inv where inv_id=1; You can verify that the index is being used by setting AUTOTRACE on and running the SELECT

statement:

SQL> set autotrace trace explain;

SQL> select /*+ index (inv INV_IDX1) */ inv_id from inv where inv_id=1; Here’s some sample output indicating that the optimizer chose to use the invisible index:

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| INV_IDX1 | 1 | 13 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Keep in mind that an invisible index only means the optimizer can’t see the index. Just like any other index, an invisible index consumes space and resources during DML statements.

Using Index-Naming Standards

When you’re creating and managing indexes, it’s highly desirable to develop some standards regarding naming. Index-naming standards are desirable for the following reasons:

• Diagnosing issues is simplified when error messages contain information that indicates the table, index type, and so on.

• Reports that display index information are more easily grouped and therefore more readable and easier in which to see patterns and issues.

I usually don’t care what the naming standards are as long as standards exist and are followed. Here are some sample index-naming guidelines:

• Primary-key index names should contain the table name and a suffix such as _PK.

• Unique-key index names should contain the table name and a suffix such as _UKN, where N is a number.

• Indexes on foreign-key columns should contain the foreign-key table and a suffix such as _FKN, where N is a number.

• Function-based index names should contain the table name and a suffix such as FCN, where N is a number.

Some shops use prefixes when naming indexes. All of these various naming standards are valid. The key is to use the standard consistently.

185

CHAPTER 8 ■ INDEXES

Specifying Index Tablespaces

When creating an index, if you don’t specify a tablespace, by default the index is created in the default tablespace for the user. That approach is acceptable for development databases where you may not be concerned about managing indexes separately from tables. Or perhaps you’re not concerned about the storage characteristics of the index because there’s only a small amount of data.

For production databases, you should consider the following with regard to index storage:

•

Creating indexes in a tablespace separate from table data

•

Creating several tablespaces specifically for use with indexes, depending on their storage requirements

These topics are discussed in detail in the following subsections.

Placing Indexes in Tablespaces Separate from Tables

DBAs debate the merits of separating tables and indexes into different tablespaces. If you have the luxury of setting up a storage system from scratch and can set up mount points that have their own sets of disks and controllers, you may see some I/O benefits by separating tables and indexes into different tablespaces.

Nowadays, storage administrators often give you a large slice of storage in a SAN, and there’s no way to guarantee that data and indexes will be stored physically on separate disks (and controllers). Thus you typically don’t gain any performance benefits by separating tables and indexes into different tablespaces. Nevertheless, there are still valid reasons to separate index tablespaces from table tablespaces:

•

You may want to implement different physical storage characteristics (such as extent size) for tables and indexes, and you prefer to allow the table and index to inherit their storage attributes from the tablespace.

•

You prefer to have the option of being able to back up, restore, and recover table and index tablespaces separately.

•

When running maintenance reports, you find it easier to manage tables and indexes when the reports have sections separated by tablespace.

If any of these reasons are valid for your environment, it’s probably worth the extra effort to employ different tablespaces for tables and indexes. Here’s a simple example of building a table and an index in separate tablespaces:

SQL> create table inv(inv_id number, inv_dtt date) tablespace inv_mgmt_data; SQL> create index inv_idx1 on inv(inv_dtt) tablespace inv_mgmt_index; I almost always use ampersand variables in scenarios like this. To do so, at the top of the script, define the names of the tablespaces to be used, and then refer to the ampersand variables within the script. This technique has the advantage of making scripts more portable from one environment (development, test, production, and so on) to another where the tablespaces may be differently named.

For example:

186

CHAPTER 8 ■ INDEXES

define tab_tbsp_small=INV_MGMT_DATA

define ind_tbsp_small=INV_MGMT_INDEX

--

create table inv(inv_id number, inv_dtt date) tablespace &&tab_tbsp_small; create index inv_idx1 on inv(inv_dtt) tablespace &&ind_tbsp_small; Placing Indexes in Tablespaces Based on Extent Size

If you know how large an index may initially be or what its growth requirements are, consider placing the index in a tablespace that is appropriate in terms of the size of the tablespace and the size of the extents. I usually create at least two index tablespaces per application. Here’s an example: create tablespace inv_idx_small

datafile '/ora01/dbfile/O11R2/inv_idx_small01.dbf'

size 100m

extent management local

uniform size 128k

segment space management auto;

--

create tablespace inv_idx_med

datafile '/ora01/dbfile/O11R2/inv_idx_med01.dbf'

size 1000m

extent management local

uniform size 4m

segment space management auto;

Indexes that have small space and growth requirements are placed in the INV_IDX_SMALL tablespace, and indexes that have medium storage requirements would be created in INV_IDX_MED. If you discover that an index is growing at an unpredicted rate, consider dropping the index and re-creating it in a different tablespace or rebuilding the index in a more appropriate tablespace.

Maintaining Indexes

As applications age, you invariably have to perform some maintenance activities on existing indexes.

You may need to rename an index to conform to newly implemented standards, or you may need to rebuild a large index to move it to a different tablespace that better suits the index’s storage requirements. The following list shows common tasks associated with index maintenance:

• Renaming an index

• Displaying the DDL for an index

• Rebuilding an index

• Setting indexes to be unusable

• Dropping an index

Each of these items is discussed in the following subsections.

187

CHAPTER 8 ■ INDEXES

Renaming an Index

Sometimes you need to rename an index. The index may have been erroneously misnamed when it was created, or perhaps you want a name that better conforms to naming standards. Use the ALTER INDEX

... RENAME TO statement to rename an index:

SQL> alter index user1_index rename to emp_idx1;

You can verify that the index was renamed by querying the data dictionary: select

table_name

,index_name

,index_type

,tablespace_name

,status

from user_indexes

order by table_name, index_name;

Displaying Code to Re-create an Index

You may be performing routine maintenance activities, such as moving an index to a different tablespace, and before you do so, you want to verify the current storage settings. You can use the DBMS_METADATA package to display the DDL required to re-create an index. If you’re using SQL*Plus, set the LONG variable to a value large enough to display all of the output. Here’s an example: SQL> set long 10000

SQL> select dbms_metadata.get_ddl('INDEX','INV_IDX1') from dual;

Here’s a partial listing of the output:

CREATE INDEX "DARL"."INV_IDX1" ON "DARL"."INV" ("INV_ID", "INV_ID2") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS

To show all index DDL for a user, run this query:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes; You can also display the DDL for a particular user. You must provide as input to the GET_DDL

function the object type, object name, and schema. For example:

select

dbms_metadata.get_ddl(object_type=>'INDEX', name=>'INV_IDX1', schema=>'INV') from dual;

Rebuilding an Index

There are a few good reasons to rebuild an index:

• The index has become corrupt (see the previous section on Avoiding Redo Generation When Creating an Index for the scenario in which you encounter logical corruption).

• You want to modify storage characteristics, such as changing the tablespace.

188