An index is a database object used primarily to improve the performance of SQL queries. The function of a database index is similar to an index in the back of a book. A book index associates a topic with a page number. When you're locating information in a book, it's usually much faster to inspect the index first, find the topic of interest, and identify associated page numbers. With this information, you can navigate directly to specific page numbers in the book. In this situation, the number of pages you need to inspect is minimal.
If there were no index, you would have to inspect every page of the book to find information. This results in a great deal of page turning, especially with large books. This is similar to an Oracle query that does not use an index and therefore has to scan every used block within a table. For large tables, this results in a great deal of I/O.
The book index's usefulness is directly correlated with the uniqueness of a topic within the book. For example, take this book; it would do no good to create an index on the topic of “performance” because every page in this book deals with performance. However, creating an index on the topic of “bitmap indexes” would be effective because there are only a few pages within the book that are applicable to this feature.
Keep in mind that the index isn't free. It consumes space in the back of the book, and if the material in the book is ever updated (like a second edition), every modification (insert, update, delete) potentially requires a corresponding change to the index. It's important to keep in mind that indexes consume space and require resources when updates occur.
Also, the person who creates the index for the book must consider which topics will be frequently looked up. Topics that are selective and frequently accessed should be included in the book index. If an index in the back of the book is never looked up by a reader, then it unnecessarily wastes space.
Much like the process of creating an index in the back of the book, there are many factors that must be considered when creating an Oracle index. Oracle provides a wide assortment of indexing features and options. These objects are manually created by the DBA or a developer. Therefore, you need to be aware of the various features and how to utilize them. If you choose the wrong type of index or use a feature incorrectly, there may be detrimental performance implications. Listed next are aspects to consider before you create an index:
SELECT
statements (improvement)INSERT
, UPDATE
, and DELETE
statementsWhen you create an index, you should give some thought to every aspect mentioned in the previous list. One of the first decisions you need to make is the type of index and the columns to include. Oracle provides a robust variety of index types. For most scenarios, you can use the default B-tree (balanced tree) index. Other commonly used types are concatenated, bitmap, and function-based indexes. Table 2-1 describes the types of indexes available with Oracle.
This chapter focuses on the most commonly used indexes and features. Hash cluster indexes, partitioned indexes, and domain indexes are not covered in this book. If you need more information regarding index types or features not covered in this chapter or book, see Oracle's SQL Reference Guide at http://otn.oracle.com
.
The first recipe in this chapter deals with the mechanics of B-tree indexes. It's critical that you understand how this database object works. Even if you've been around Oracle for a while, we feel it's useful to work through the various scenarios outlined in this first recipe to ensure that you know how the optimizer uses this type of index. This will lay the foundation for solving many different types of performance problems (especially SQL tuning).
You want to create an index. You understand that the default type of index in Oracle is the B-tree, but you don't quite understand how an index is physically implemented. You want to fully comprehend the B-tree index internals so as to make intelligent performance decisions when building database applications.
An example with a good diagram will help illustrate the mechanics of a B-tree index. Even if you've been working with B-tree indexes for quite some time, a good example may illuminate technical aspects of using an index. To get started, suppose you have a table created as follows:
create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30));
You determine that several SQL queries will frequently use LAST_NAME
in the WHERE
clause. This prompts you to create an index:
SQL> create index cust_idx1 on cust(last_name);
Several hundred rows are now inserted into the table (not all of the rows are shown here):
insert into cust values(7, 'ACER','SCOTT'),
insert into cust values(5, 'STARK','JIM'),
insert into cust values(3, 'GREY','BOB'),
insert into cust values(11,'KHAN','BRAD'),
.....
insert into cust values(274, 'ACER','SID'),
After the rows are inserted, we ensure that the table statistics are up to date so as to provide the query optimizer sufficient information to make good choices on how to retrieve the data:
SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT', -
tabname=>'CUST',cascade=>true);
As rows are inserted into the table, Oracle will allocate extents that consist of physical database blocks. Oracle will also allocate blocks for the index. For each record inserted into the table, Oracle will also create an entry in the index that consists of the ROWID
and column value (the value in LAST_NAME
in this example). The ROWID
for each index entry points to the datafile and block that the table column value is stored in. Figure 2-1 shows a graphical representation of how data is stored in the table and the corresponding B-tree index. For this example, datafiles 10 and 15 contain table data stored in associated blocks and datafile 22 stores the index blocks.
Figure 2-1. Physical layout of a table and B-tree index
There are two dotted lines in Figure 2-1. These lines depict how the ROWID
(in the index structure) points to the physical location in the table for the column values of ACER
. These particular values will be used in the scenarios in this solution.
When selecting data from a table and its corresponding index, there are three basic scenarios:
The prior situations are covered in the next three subsections.
There are two scenarios that will be shown in this section:
First the index range scan is demonstrated. For this example, suppose this query is issued that selects from the table:
SQL> select last_name from cust where last_name='ACER';
Before reading on, look at Figure 2-1 and try to answer this question: “What are the minimal number of blocks Oracle will need to read to return the data for this query?” In other words, what is the most efficient way to access the physical blocks in order to satisfy the results of this query? The optimizer could choose to read through every block in the table structure. However, that would result in a great deal of I/O, and thus it is not the most optimal way to retrieve the data.
For this example, the most efficient way to retrieve the data is to use the index structure. To return the rows that contain the value of ACER
in the LAST_NAME
column, Oracle will need to read three blocks: block 20, block 30, and block 39. We can verify that this is occurring by using Oracle's Autotrace utility:
SQL> set autotrace on;
SQL> select last_name from cust where last_name='ACER';
Here is a partial snippet of the output:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 808 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 101 | 808 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
The prior output shows that Oracle needed to use only the CUST_IDX1
index to retrieve the data to satisfy the result set of the query. The table data blocks were not accessed; only the index blocks were required. This is a particularly efficient indexing strategy for the given query. Listed next are the statistics displayed by Autotrace for this example:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
The consistent gets
value indicates that three blocks were read from memory (db block gets
plus consistent gets
equals the total blocks read from memory). Since the index blocks were already in memory, no physical reads were required to return the result set of this query.
Next an example that results in an index fast full scan is demonstrated. Consider this query:
SQL> select count(last_name) from cust;
Using SET AUTOTRACE ON
, an execution plan is generated. Here is the corresponding output:
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX FAST FULL SCAN| CUST_IDX1 | 1509 | 12072 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
The prior output shows that only the index structure was used to determine the count within the table. In this situation, the optimizer determined that a full scan of the index was more efficient than a full scan of the table.
Now consider this situation: suppose we need additional information from the CUST
table. This query additionally selects the FIRST_NAME
column:
SQL> select last_name, first_name from cust where last_name = 'ACER';
Using SET AUTOTRACE ON
and executing the prior query results in the following execution plan:
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 1414 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUST | 101 | 1414 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_IDX1 | 101 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
The prior output indicates that the CUST_IDX1
index was accessed via an INDEX RANGE SCAN
. The INDEX RANGE SCAN
identifies the index blocks required to satisfy the results of this query. Additionally the table is read by TABLE ACCESS BY INDEX ROWID
. The access to the table by the index's ROWID
means that Oracle uses the ROWID
(stored in the index) to locate the data contained within the table blocks. In Figure 2-1, this is indicated by the dotted lines that map the ROWID
to the appropriate table blocks that contain the value of ACER
in the LAST_NAME
column.
Again, looking at Figure 2-1, how many table and index blocks need to be read in this scenario? The index requires that blocks 20, 30, and 39 must be read. Since FIRST_NAME
is not included in the index, Oracle must read the table blocks to retrieve these values. Oracle knows the ROWID
of the table blocks and directly reads blocks 11 and 2500 to retrieve that data. That makes a total of five blocks. Here is a partial snippet of the statistics generated by Autotrace that confirms the number of blocks read is five:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
In some situations, even if there is an index, Oracle will determine that it's more efficient to use only the table blocks. When Oracle inspects every row within a table, this is known a full table scan. For example, take this query:
SQL> select * from cust;
Here are the corresponding execution plan and statistics:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1509 | 24144 | 12 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CUST | 1509 | 24144 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
119 consistent gets
0 physical reads
The prior output shows that a total of 119 blocks were inspected. Oracle searched every row in the table to bring back the results required to satisfy the query. In this situation, all blocks of the table must be read, and there is no way for Oracle to use the index to speed up the retrieval of the data.
Note For the examples in this recipe, your results may vary slightly, depending on how many rows you initially insert into the table. We used approximately 1,500 rows for this example.
The B-tree index is the default index type in Oracle. For most OLTP-type applications, this index type is sufficient. This index type is known as B-tree because the ROWID
and associated column values are stored within blocks in a balanced tree-like structure (see Figure 2-1). The B stands for balanced.
B-tree indexes are efficient because, when properly used, they result in a query retrieving data far faster than it would without the index. If the index structure itself contains the required column values to satisfy the result of the query, then the table data blocks need not be accessed. Understanding these mechanics will guide your indexing decision-making process. For example, this will help you decide which columns to index and whether a concatenated index might be more efficient for certain queries and less optimal for others. These topics are covered in detail in subsequent recipes in this chapter.
ESTIMATING THE SPACE AN INDEX WILL REQUIRE
A database you manage contains hundreds of tables. Each table typically contains a dozen or more columns. You wonder which columns should be indexed.
Listed next are general guidelines for deciding which columns to index.
WHERE
clause of frequently executed SQL queries.After you have decided to create indexes, we recommend that you adhere to index creation standards that facilitate the ease of maintenance. Specifically, follow these guidelines when creating an index:
INDEX_LARGE
, INDEX_MEDIUM
, and INDEX_SMALL
tablespaces, each defined with storage characteristics appropriate for the size of the index.Listed next is a sample script that encapsulates the foregoing recommendations from the prior two bulleted lists:
CREATE TABLE cust(
cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30));
--
ALTER TABLE cust ADD CONSTRAINT cust_pk PRIMARY KEY (cust_id)
USING INDEX TABLESPACE reporting_index;
--
ALTER TABLE cust ADD CONSTRAINT cust_uk1 UNIQUE (last_name, first_name)
USING INDEX TABLESPACE reporting_index;
--
CREATE TABLE address(
address_id NUMBER,
cust_id NUMBER
,street VARCHAR2(30)
,city VARCHAR2(30)
,state VARCHAR2(30))
TABLESPACE reporting_data;
--
ALTER TABLE address ADD CONSTRAINT addr_fk1
FOREIGN KEY (cust_id) REFERENCES cust(cust_id);
--
CREATE INDEX addr_fk1 ON address(cust_id)
TABLESPACE reporting_index;
In the prior script, two tables are created. The parent table is CUST
and its primary key is CUST_ID
. The child table is ADDRESS
and its primary key is ADDRESS_ID
. The CUST_ID
column exists in ADDRESS
as a foreign key mapping back to the CUST_ID
column in the CUST
table.
You should add an index only when you're certain it will improve performance. Misusing indexes can have serious negative performance effects. Indexes created of the wrong type or on the wrong columns do nothing but consume space and processing resources. As a DBA, you must have a strategy to ensure that indexes enhance performance and don't negatively impact applications.
Table 2-2 encapsulates many of the index management concepts covered in this chapter. These recommendations aren't written in stone: adapt and modify them as needed for your environment.
Refer to these guidelines as you create and manage indexes in your databases. These recommendations are intended to help you correctly use index technology.
INDEXES WITH NO SEGMENTS
You want to enforce that the primary key columns are unique within a table. Furthermore many of the columns in the primary key are frequently used within the WHERE
clause of several queries. You want to ensure that indexes are created on primary key columns.
When you define a primary key constraint for a table, Oracle will automatically create an associated index for you. There are several methods available for creating a primary key constraint. Our preferred approach is to use the ALTER TABLE...ADD CONSTRAINT
statement. This will create the index and the constraint at the same time. This example creates a primary key constraint named CUST_PK
and also instructs Oracle to create the corresponding index (also named CUST_PK
) in the USERS
tablespace:
alter table cust add constraint cust_pk primary key (cust_id)
using index tablespace users;
The following queries and output provide details about the constraint and index that Oracle created. The first query displays the constraint information:
select
constraint_name
,constraint_type
from user_constraints
where table_name = 'CUST';
CONSTRAINT_NAME C
------------------------------ -
CUST_PK P
This query displays the index information:
select
index_name
,tablespace_name
,index_type
,uniqueness
from user_indexes
where table_name = 'CUST';
INDEX_NAME TABLESPACE_NAME INDEX_TYPE UNIQUENESS
--------------- --------------- --------------- ---------------
CUST_PK USERS NORMAL UNIQUE
The solution for this recipe shows the method that we prefer to create primary key constraints and the corresponding index. In most situations, this approach is acceptable. However, you should be aware that there are several other methods for creating the primary key constraint and index. These methods are listed here:
ALTER TABLE...ADD CONSTRAINT
.CREATE TABLE
statement.CREATE TABLE
statement.These techniques are described in the next several subsections.
You have the option of first creating an index and then altering the table to apply the primary key constraint. Here's an example:
SQL> create index cust_pk on cust(cust_id);
SQL> alter table cust add constraint cust_pk primary key(cust_id);
The advantage to this approach is that you can drop or disable the primary key constraint independently of the index. If you work with large data volumes, you may require this sort of flexibility. This approach allows you to disable/re-enable a constraint without having to later rebuild the index.
You can directly create an index inline (with the column) in the CREATE TABLE
statement. This approach is simple but doesn't allow for multiple column primary keys and doesn't name the constraint:
SQL> create table cust(cust_id number primary key);
If you don't explicitly name the constraint (as in the prior statement), Oracle automatically generates a name like SYS_C123456
. If you want to explicitly provide a name, you can do so as follows:
create table cust(cust_id number constraint cust_pk primary key
using index tablespace users);
The advantage of this approach is that it's very simple. If you're experimenting in a development or test environment, this approach is quick and effective.
You can also define the primary key constraint out of line (from the column) within the CREATE TABLE
statement:
create table cust(cust_id number
,constraint cust_pk primary key (cust_id)
using index tablespace users);
The out-of-line approach has one advantage over the inline approach in that you can specify multiple columns for the primary key.
All of the prior techniques for creating a primary key constraint and corresponding index are valid. It's often a matter of DBA or developer preference as to which technique is used.
You have a column (or combination of columns) that contains values that should always be unique. You want to create an index on this column (or combination of columns) that enforces the uniqueness and also provides efficient access to the table when using the unique column in the WHERE
clause of a query.
Note If you want to create a unique constraint on the primary key column(s), then you should explicitly create a primary key constraint (see Recipe 2-3 for details). One difference between a primary key and a unique key is that you can have only one primary key definition per table, whereas you can have multiple unique keys. Also, unique key constraints allow for null values, whereas primary key constraints do not.
This solution focuses on using the ALTER TABLE...ADD CONSTRAINT
statement. When you create a unique key constraint, Oracle will automatically create an index for you. This is our recommended approach for creating unique key constraints and indexes. This example creates a unique constraint named CUST_UX1
on the combination of the LAST_NAME
and FIRST_NAME
columns of the CUST
table:
alter table cust add constraint cust_ux1 unique (last_name, first_name)
using index tablespace users;
The prior statement creates the unique constraint, and additionally Oracle automatically creates an associated index. The following query displays the constraint that was created successfully:
select
constraint_name
,constraint_type
from user_constraints
where table_name = 'CUST';
Here is a snippet of the output:
CONSTRAINT_NAME C
------------------------------ -
CUST_UX1 U
This query shows the index that was automatically created along with the constraint:
select
index_name
,tablespace_name
,index_type
,uniqueness
from user_indexes
where table_name = 'CUST';
Here is some sample output:
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENESS
-------------------- ---------- ---------- ---------
CUST_UX1 USERS NORMAL UNIQUE
Defining a unique constraint ensures that when you insert or update column values, then any combination of non-null values are unique. Besides the approach we displayed in the “Solution” section, there are several additional techniques for creating unique constraints:
CREATE TABLE
statement.ALTER TABLE
to add a constraint.These techniques are described in the next few subsections.
Listed next is an example of using the CREATE TABLE
statement to include a unique constraint.
create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
,constraint cust_ux1 unique(last_name, first_name)
using index tablespace users);
The advantage of this approach is that it's simple and encapsulates the constraint and index creation within one statement.
You have the option of first creating an index and then adding the constraint as a separate statement—for example:
SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;
SQL> alter table cust add constraint cust_uidx1 unique (last_name, first_name);
The advantage of creating the index separate from the constraint is that you can drop or disable the constraint without dropping the underlying index. When working with large indexes, you may want to consider this approach. If you need to disable the constraint for any reason and then re-enable it later, you can do so without dropping the index (which may take a long time for large indexes).
You can also create just a unique index without adding the unique constraint—for example:
SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;
When you create only a unique index explicitly (as in the prior statement), Oracle creates a unique index but doesn't add an entry for a constraint in DBA/ALL/USER_CONSTRAINTS
. Why does this matter? Consider this scenario:
SQL> insert into cust values (1, 'STARK', 'JIM'),
SQL> insert into cust values (1, 'STARK', 'JIM'),
Here's the corresponding error message that is thrown:
ERROR at line 1:
ORA-00001: unique constraint (MV_MAINT.CUST_UIDX1) violated
If you're asked to troubleshoot this issue, the first place you look is in DBA_CONSTRAINTS
for a constraint named CUST_UIDX1
. However, there is no information:
select
constraint_name
from dba_constraints
where constraint_name='CUST_UIDX1';
no rows selected
The “no rows selected” message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data-dictionary views. In this situation, you have to look at DBA_INDEXES
to view the details of the unique index that has been created—for example:
select index_name, uniqueness
from dba_indexes where index_name='CUST_UIDX1';
INDEX_NAME UNIQUENESS
------------------------------ ----------
CUST_UIDX1 UNIQUE
A large number of the queries in your application use foreign key columns as predicates in the WHERE
clause. Therefore, for performance reasons, you want to ensure that you have all foreign key columns indexed.
Unlike primary key constraints, Oracle does not automatically create indexes on foreign key columns. For example, say you have a requirement that every record in the ADDRESS
table be assigned a corresponding CUST_ID
column that exists in the CUST
table. To enforce this relationship, you create a foreign key constraint on the ADDRESS
table as follows:
alter table address add constraint addr_fk1
foreign key (cust_id) references cust(cust_id);
Note A foreign key column must reference a column in the parent table that has a primary key or unique key constraint defined on it. Otherwise you'll receive the error “ORA-02270: no matching unique or primary key for this column-list.”
You realize the foreign key column is used extensively when joining the CUST
and ADDRESS
tables and that an index on the foreign key column will dramatically increase performance. You have to manually create an index in this situation. For example, a regular B-tree index is created on the foreign key column of CUST_ID
in the ADDRESS
table:
SQL> create index addr_fk1 on address(cust_id);
You don't have to name the index the same as the foreign key name (as we did in the prior lines of code). It's a personal preference as to whether you do that. We feel it's easier to maintain environments when the constraint and corresponding index have the same name.
Foreign keys exist to ensure that when inserting into a child table, a corresponding parent table record exists. This is the mechanism to guarantee that data conforms to parent/child business relationship rules. From a performance perspective, it's usually a good idea to create an index on foreign key columns. This is because parent/child tables are frequently joined on the foreign key column(s) in the child table to the primary key column(s) in the parent table—for example:
select
a.last_name, a.first_name, b.state
from cust a
,address b
where a.cust_id = b.cust_id;
In most scenarios, the Oracle query optimizer will choose to use the index on the foreign key column to identify the child records that are required to satisfy the results of the query. If no index exists, Oracle has to perform a full table scan on the child table.
If you've inherited a database, then it's prudent to check if columns with foreign key constraints defined on them have a corresponding index. The following query displays indexes associated with foreign key constraints:
select
a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
join
user_cons_columns b on a.constraint_name = b.constraint_name
left outer join
user_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;
If there is no index on the foreign key column, the ***No Index*** message is displayed. For example, suppose the index in the “Solution” section was accidentally dropped and then the prior query was run. Here is some sample output:
CONS_NAME TAB_NAME CONS_COLUMN IND_COLUMN
------------------ ----------------------- ------------------------- --------------------
ADDR_FK1 ADDRESS CUST_ID ***No Index***
You have a combination of columns (from the same table) that are often used in the WHERE
clause of several SQL queries. For example, you use LAST_NAME
in combination with FIRST_NAME
to identify a customer:
You wonder if it would be more efficient to create a single concatenated index on the combination of LAST_NAME
and FIRST_NAME
columns or if performance would be better if two indexes were created separately on LAST_NAME
and FIRST_NAME
.
When frequently accessing two or more columns in conjunction in the WHERE
clause, a concatenated index is often more selective than two single indexes. For this example, here's the table creation script:
create table cust(
cust_id number primary key
,last_name varchar2(30)
,first_name varchar2(30));
Here's an example of a concatenated index created on LAST_NAME
and FIRST_NAME
:
SQL> create index cust_idx1 on cust(last_name, first_name);
To determine whether the concatenated index is used, several rows are inserted (only a subset of the rows is shown here):
SQL> insert into cust values(1,'SMITH','JOHN'),
SQL> insert into cust values(2,'JONES','DAVE'),
..........
SQL> insert into cust values(3,'FORD','SUE'),
Next, statistics are generated for the table and index:
SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT',-
tabname=>'CUST',cascade=>true);
Now Autotrace is turned on so that the execution plan is displayed when a query is run:
SQL> set autotrace on;
Here's the query to execute:
select last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';
Listed next is an explain plan that shows the optimizer is using the index:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 143 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 13 | 143 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
The prior output indicates that an INDEX RANGE SCAN
was used to access the CUST_IDX1
index. Notice that all of the information required to satisfy the results of this query was contained within the index. The table data was not required. Oracle accessed only the index.
One other item to consider: suppose you have this query that additionally selects the CUST_ID
column:
select cust_id, last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';
If you frequently access CUST_ID
in combination with LAST_NAME
and FIRST_NAME
, consider adding CUST_ID
to the concatenated index. This will provide all of the information that the query needs in the index. Oracle will be able to retrieve the required data from the index blocks and thus not have to access the table blocks.
Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as concatenated indexes. These indexes are especially effective when you often use multiple columns in the WHERE
clause when accessing a table. Here are some factors to consider when using concatenated indexes:
WHERE
clause, consider creating a concatenated index.WHERE
clause, place that column at the leading edge of the index (first column defined).WHERE
clause (see the next few paragraphs here for details).In older versions of Oracle (circa v8), the optimizer would use a concatenated index only if the leading edge column(s) appeared in the WHERE
clause. In modern versions, the optimizer uses a concatenated index even if the leading edge column(s) aren't present in the WHERE
clause. This ability to use an index without reference to leading edge columns is known as the skip-scan feature. For example, say you have this query that uses the FIRST_NAME
column (which is a lagging column in the concatenated index created in the “Solution” section of this recipe):
SQL> select last_name from cust where first_name='DAVE';
Here is the corresponding explain plan showing that the skip-scan feature is in play:
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38 | 418 | 1 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | CUST_IDX1 | 38 | 418 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
A concatenated index that is used for skip-scanning is more efficient than a full table scan. However, if you're consistently using only a lagging edge column of a concatenated index, then consider creating a single-column index on the lagging column.
You want to create an index that efficiently handles cases in which many rows have the same values in one or more indexed columns. For example, suppose you have a table defined as follows:
create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
,middle_name varchar2(30));
Furthermore, you inspect the data inserted into the prior table with this query:
SQL> select last_name, first_name, middle_name from cust;
You notice that there is a great deal of duplication in the LAST_NAME and FIRST_NAME columns:
LEE JOHN Q
LEE JOHN B
LEE JOHN A
LEE JOE D
SMITH BOB A
SMITH BOB C
SMITH BOB D
SMITH JOHN J
SMITH JOHN A
SMITH MIKE K
SMITH MIKE R
SMITH MIKE S
You want to create an index that compresses the values so as to compact entries into the blocks. When the index is accessed, the compression will result in fewer block reads and thus improve performance. Specifically you want to create a key-compressed index on the LAST_NAME
and FIRST_NAME
columns of this table.
Use the COMPRESS N
clause to create a compressed index:
SQL> create index cust_cidx1 on cust(last_name, first_name) compress 2;
The prior line of code instructs Oracle to create a compressed index on two columns (LAST_NAME
and FIRST_NAME
). For this example, if we determined that there was a high degree of duplication only in the first column, we could instruct the COMPRESS N
clause to compress only the first column (LAST_NAME
) by specifying an integer of 1:
SQL> create index cust_cidx1 on cust(last_name, first_name) compress 1;
Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated. Compressed indexes have the following advantages:
The degree of compression will vary by the amount of duplication in the index columns specified for compression. You can verify the degree of compression and the number of leaf blocks used by running the following two queries before and after creating an index with compression enabled:
SQL> select sum(bytes) from user_extents where segment_name='&&ind_name';
SQL> select index_name, leaf_blocks from user_indexes where index_name='&&ind_name';
You can verify the index compression is in use and the corresponding prefix length as follows:
select index_name, compression, prefix_length
from user_indexes
where index_name = 'CUST_CIDX1';
Here's some sample output indicating that compression is enabled for the index with a prefix length of 2:
INDEX_NAME COMPRESS PREFIX_LENGTH
------------------------------ -------- -------------
CUST_CIDX1 ENABLED 2
You can modify the prefix length by rebuilding the index. The following code changes the prefix length to 1:
SQL> alter index cust_cidx1 rebuild compress 1;
You can enable or disable compression for an existing index by rebuilding it. This example rebuilds the index with no compression:
SQL> alter index cust_cidx1 rebuild nocompress;
Note You cannot create a key-compressed index on a bitmap index.
A query is running slow. You examine the WHERE
clause and notice that a SQL UPPER
function has been applied to a column. The UPPER
function blocks the use of the existing index on that column. You want to create a function-based index to support the query. Here's an example of such a query:
You inspect USER_INDEXES
and discover that an index exists on the FIRST_NAME
column:
select index_name, column_name
from user_ind_columns
where table_name = 'CUST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
CUST_IDX1 FIRST_NAME
You generate an explain plan via SET AUTOTRACE TRACE EXPLAIN
and notice that with the UPPER
function applied to the column, the index is not used:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUST | 1 | 17 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
You need to create an index that Oracle will use in this situation.
There are two ways to resolve this issue:
This solution focuses on using a function-based index. You create a function-based index by referencing the SQL function and column in the index creation statement. For this example, a function-based index is created on UPPER(name)
:
SQL> create index cust_fidx1 on cust(UPPER(first_name));
To verify if the index is used, the Autotrace facility is turned on:
SQL> set autotrace trace explain;
Now the query is executed:
SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';
Here is the resulting execution plan showing that the function-based index is used:
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUST | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_FIDX1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Note You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
Function-based indexes are created with functions or expressions in their definitions. Function-based indexes allow index lookups on columns referenced by SQL functions in the WHERE
clause of a query. The index can be as simple as the example in the “Solution” section of this recipe, or it can be based on complex logic stored in a PL/SQL function.
Note Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC
when creating a user-defined function that you want to use in a function-based index.
If you want to see the definition of a function-based index, select from the DBA/ALL/USER_IND_EXPRESSIONS
view to display the SQL associated with the index. If you're using SQL*Plus, be sure to issue a SET LONG
command first—for example:
SQL> SET LONG 500
SQL> select index_name, column_expression from user_ind_expressions;
The SET LONG
command in this example tells SQL*Plus to display up to 500 characters from the COLUMN_EXPRESSION
column, which is of type LONG
.
You're currently using a function-based index but need better performance. You want to replace the function-based index with a virtual column and place an index on the virtual column.
Note The virtual column feature requires Oracle Database 11g or higher.
Using a virtual column in combination with an index provides you with an alternative method for achieving performance gains when using SQL functions on columns in the WHERE
clause. For example, suppose you have this query:
SELECT first_name
FROM cust
WHERE UPPER(first_name) = 'DAVE';
Normally, the optimizer will ignore any indexes on the column FIRST_NAME
because of the SQL function applied to the column. There are two ways to improve performance in this situation:
This solution focuses on the latter bullet. First a virtual column is added to the table that encapsulates the SQL function:
SQL> alter table cust add(up_name generated always as (UPPER(first_name)) virtual);
Next an index is created on the virtual column:
SQL> create index cust_vidx1 on cust(up_name);
This creates a very efficient mechanism to retrieve data when referencing a column with a SQL function.
You might be asking this question: “Which performs better, a function-based index or an indexed virtual column?” In our testing, we were able to create several scenarios where the virtual column performed better than the function-based index. Results may vary depending on your data.
The purpose of this recipe is not to convince you to immediately start replacing all function-based indexes in your system with virtual columns; rather we want you to be aware of an alternative method for solving a common performance issue.
A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.
Several caveats are associated with virtual columns:
To view the definition of a virtual column, use the DBMS_METADATA
package to view the DDL associated with the table. If you're selecting from SQL*Plus, you need to set the LONG
variable to a value large enough to show all data returned:
SQL> set long 10000;
SQL> select dbms_metadata.get_ddl('TABLE','CUST') from dual;
Here's a partial snippet of the output showing the virtual column details:
"UP_NAME" VARCHAR2(30) GENERATED ALWAYS AS (UPPER("FIRST_NAME"))
VIRTUAL VISIBLE) SEGMENT CREATION IMMEDIATE
You can also view the definition of the virtual column by querying the DBA/ALL/USER_IND_EXPRESSIONS
view. If you're using SQL*Plus, be sure to issue a SET LONG command first—for example:
SQL> SET LONG 500
SQL> select index_name, column_expression from user_ind_expressions;
The SET LONG
command in this example tells SQL*Plus to display up to 500 characters from the COLUMN_EXPRESSION
column, which is of type LONG
.
You use a sequence to populate the primary key of a table and realize that this can cause contention on the leading edge of the index because the index values are nearly similar. This leads to multiple inserts into the same block, which causes contention. You want to spread out the inserts into the index so that the inserts more evenly distribute values across the index structure. You want to use a reverse-key index to accomplish this.
Use the REVERSE
clause to create a reverse-key index:
SQL> create index inv_idx1 on inv(inv_id) reverse;
You can verify that an index is reverse-key by running the following query:
SQL> select index_name, index_type from user_indexes;
Here's some sample output showing that the INV_IDX1
index is reverse-key:
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
INV_IDX1 NORMAL/REV
USERS_IDX1 NORMAL
Note You can't specify REVERSE
for a bitmap index or an index-organized table.
Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created. For example, if the index values are 100, 101, and 102, the reverse-key index values are 001, 101, and 201:
Index value Reverse key value
------------- --------------------
100 001
101 101
102 201
Reverse-key indexes can perform better in scenarios where you need a way to evenly distribute index data that would otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid having I/O concentrated in one physical disk location within the index during large inserts of sequential values. The downside to this type of index is that it can't be used for index range scans, which therefore limits its usefulness.
You can rebuild an existing index to be reverse-key by using the REBUILD REVERSE
clause—for example:
SQL> alter index f_regs_idx1 rebuild reverse;
Similarly, if you want to make an index that is reverse-key into a normally ordered index, then use the REBUILD NOREVERSE
clause:
SQL> alter index f_regs_idx1 rebuild noreverse;
You know from experience that sometimes when an index is added to a third-party application, this can cause performance issues and also can be a violation of the support agreement with the vendor. You want to implement an index in such a way that the application won't ever use the index.
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—for example:
SQL> create index inv_idx1 on inv(inv_id) invisible;
Next, ensure that the OPTIMIZER_USE_INVISIBLE_INDEXES
initialization parameter is set to TRUE
(the default is FALSE
). This instructs the optimizer to consider invisible indexes:
SQL> alter system set optimizer_use_invisible_indexes=true;
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 TRACE EXPLAIN
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 means only that the optimizer can't see the index. Just like any other index, an invisible index consumes space and resources when executing DML statements.
In Oracle Database 11g 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:
The first bulleted item was discussed in the “Solution” section of this recipe. The second scenario is discussed in this section. For example, 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 consume considerable database resources.
Making an index invisible has the advantage that it tells the optimizer only 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. You simply have to mark it as visible again—for example:
SQL> alter index inv_idx1 visible;
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 VISIBLE
OLD SCHOOL: INSTRUCTING THE OPTIMIZER NOT TO USE AN INDEX
You have a data warehouse that contains a star schema. The star schema consists of a large fact table and several dimension (lookup) tables. The primary key columns of the dimension tables map to foreign key columns in the fact table. You would like to create bitmap indexes on all of the foreign key columns in the fact table.
You use the BITMAP
keyword to create a bitmap index. The next line of code creates a bitmap index on the CUST_ID
column of the F_SALES
table:
SQL> create bitmap index f_sales_cust_fk1 on f_sales(cust_id);
The type of index is verified with the following query:
SQL> select index_name, index_type from user_indexes where index_name='F_SALES_CUST_FK1';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
F_SALES_CUST_FK1 BITMAP
A bitmap index stores the ROWID
of a row and a corresponding bitmap. You can think of the bitmap as a combination of ones and zeros. A one indicates the presence of a value, and a zero indicates that the value doesn't exist. Bitmap indexes are ideal for low-cardinality columns (few distinct values) and where the application is not frequently updating the table. Bitmap indexes are commonly used in data warehouse environments where you have star schema design.
A typical star schema structure consists of a large fact table and many small dimension (lookup) tables. In these scenarios, it's common to create bitmap indexes on fact table–foreign key columns. The fact tables are typically loaded on a daily basis and (usually) aren't subsequently updated or deleted.
You shouldn't use bitmap indexes on OLTP databases with high INSERT
/UPDATE
/DELETE
activities, due to locking issues. Locking issues arise because the structure of the bitmap index results in potentially many rows being locked during DML operations, which results in locking problems for high-transaction OLTP systems.
Note Bitmap indexes and bitmap join indexes are available only with the Oracle Enterprise Edition of the database.
You're working in a data warehouse environment. You have a fairly large dimension table that is often joined to an extremely large fact table. You wonder if there's a way to create a bitmap index in such a way that it can eliminate the need for the optimizer to access the dimension table blocks to satisfy the results of a query.
Here's the basic syntax for creating a bitmap join index:
create bitmap index <index_name>
on <fact_table> (<dimension_table.dimension_column>)
from <fact_table>, <dimension_table>
where <fact_table>.<foreign_key_column> = <dimension_table>.<primary_key_column>;
Bitmap join indexes are appropriate in situations where you're joining two tables using the foreign key column(s) in one table that relate to primary key column(s) in another table. For example, suppose you typically retrieve the CUST_NAME
from the D_CUSTOMERS
table while joining to a large F_SHIPMENTS
fact table. This example creates a bitmap join index between the F_SHIPMENTS
and D_CUSTOMERS
tables:
create bitmap index f_shipments_bm_idx1
on f_shipments(d_customers.cust_name)
from f_shipments, d_customers
where f_shipments.d_cust_id = d_customers.d_cust_id;
Now, consider a query such as this:
select
d.cust_name
from f_shipments f, d_customers d
where f.d_cust_id = d.d_cust_id
and d.cust_name = 'Sun';
The optimizer can choose to use the bitmap join index and thus avoid the expense of having to join the tables.
Bitmap join indexes store the results of a join between two tables in an index. Bitmap indexes are beneficial because they avoid joining tables to retrieve results. The syntax for a bitmap join index differs from a regular bitmap index in that it contains FROM
and WHERE
clauses.
Bitmap join indexes are usually suitable only for data warehouse environments where you have tables that get loaded and then are not updated. When updating tables that have bitmap join indexes declared, this potentially results in several rows being locked. Therefore this type of an index is not suitable for an OLTP database.
You want to create a table that is the intersection of a many-to-many relationship between two tables. The intersection table will consist of two columns. Each column is a foreign key that maps back to a corresponding primary key in a parent table.
Index-organized tables (IOTs) are efficient objects when the table data is typically accessed through querying on the primary key. Use the ORGANIZATION INDEX
clause to create an IOT:
create table cust_assoc
(cust_id number
,user_group_id number
,create_dtt timestamp(5)
,update_dtt timestamp(5)
,constraint cust_assoc_pk primary key(cust_id, user_group_id)
)
organization index
including create_dtt
pctthreshold 30
tablespace nsestar_index
overflow
tablespace dim_index;
Notice that DBA/ALL/USER_TABLES
includes an entry for the table name used when creating an IOT. The following two queries show how Oracle records the information regarding the IOT in the data dictionary:
select table_name, iot_name
from user_tables
where iot_name = 'CUST_ASSOC';
Here is some sample output:
TABLE_NAME IOT_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_184185 CUST_ASSOC
Listed next is another slightly different query with its output:
select table_name, iot_name
from user_tables
where table_name = 'CUST_ASSOC';
Here is some sample output:
TABLE_NAME IOT_NAME
------------------------------ ------------------------------
CUST_ASSOC
Additionally, DBA/ALL/USER_INDEXES
contains a record with the name of the primary key constraint specified. The INDEX_TYPE
column contains a value of IOT - TOP
for IOTs:
select index_name, index_type
from user_indexes
where table_name = 'CUST_ASSOC';
Here is some sample output:
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
CUST_ASSOC_PK IOT - TOP
An IOT stores the entire contents of the table's row in a B-tree index structure. IOTs provide fast access for queries that have exact matches and/or range searches on the primary key.
All columns specified up to and including the column specified in the INCLUDING
clause are stored in the same block as the CUST_ASSOC_PK
primary key column. In other words, the INCLUDING
clause specifies the last column to keep in the table segment. Columns listed after the column specified in the INCLUDING
clause are stored in the overflow data segment. In the previous example, the UPDATE_DTT
column is stored in the overflow segment.
PCTTHRESHOLD
specifies the percentage of space reserved in the index block for the IOT row. This value can be from 1 to 50, and defaults to 50 if no value is specified. There must be enough space in the index block to store the primary key. The OVERFLOW
clause details which tablespace should be used to store overflow data segments.
You maintain a large database that contains thousands of indexes. As part of your proactive maintenance, you want to determine if any indexes are not being used. You realize that unused indexes have a detrimental impact on performance, because every time a row is inserted, updated, and deleted, the corresponding index has to be maintained. This consumes CPU resources and disk space. If an index isn't being used, it should be dropped.
Use the ALTER INDEX...MONITORING USAGE
statement to enable basic index monitoring. The following example enables index monitoring on an index named F_REGS_IDX1
:
SQL> alter index f_regs_idx1 monitoring usage;
The first time the index is accessed, Oracle records this; you can view whether an index has been accessed via the V$OBJECT_USAGE
view. To report which indexes are being monitored and have ever been used, run this query:
SQL> select index_name, table_name, monitoring, used from v$object_usage;
If the index has ever been used in a SELECT
statement, then the USED
column will contain the YES
value. Here is some sample output from the prior query:
INDEX_NAME TABLE_NAME MON USED
------------------------------ ------------------------------ --- ----
F_REGS_IDX1 F_REGS YES YES
Most likely, you won't monitor only one index. Rather, you'll want to monitor all indexes for a user. In this situation, use SQL to generate SQL to create a script you can run to turn on monitoring for all indexes. Here's such a script:
set pagesize 0 head off linesize 132
spool enable_mon.sql
select
'alter index ' || index_name || ' monitoring usage;'
from user_indexes;
spool off;
To disable monitoring on an index, use the NOMONITORING USAGE
clause—for example:
SQL> alter index f_regs_idx1 nomonitoring usage;
The main advantage to monitoring index usage is to identify indexes not being used. This allows you to identify indexes that can be dropped. This will free up disk space and improve the performance of DML statements.
The V$OBJECT_USAGE
view shows information only for the currently connected user. You can verify this behavior by inspecting the TEXT
column of DBA_VIEWS
for the V$OBJECT_USAGE
definition:
SQL> select text from dba_views where view_name = 'V$OBJECT_USAGE';
Notice the following line in the output:
where io.owner# = userenv('SCHEMAID')
That line instructs the view to display information only for the currently connected user. If you're logged in as a DBA privileged user and want to view the status of all indexes that have monitoring enabled (regardless of the user), execute this query:
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
The prior query removes the line from the query that restricts output to display information only for the currently logged-in user. This provides you with a convenient way to view all monitored indexes.
You're creating an index based on a table that contains millions of rows. You want to create the index as fast as possible.
This solution describes two techniques for increasing the speed of index creation:
You can use the prior two features independently of each other, or they can be used in conjunction.
You can optionally create an index with the NOLOGGING
clause. Doing so has these implications:
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;
You can run this query to determine whether an index has been created with NOLOGGING
:
SQL> select index_name, logging from user_indexes;
In large database environments where you're attempting to create an index on a table that is populated with many rows, you may be able to reduce the time it takes to create the index by using the PARALLEL
clause. For example, this sets the degree of parallelism to 2 when creating the index:
create index inv_idx1 on inv(inv_id)
parallel 2
tablespace inv_mgmt_data;
You can verify the degree of parallelism on an index via this query:
SQL> select index_name, degreel from user_indexes;
Note If you don't specify a degree of parallelism, Oracle selects a degree based on the number of CPUs on the box times the value of PARALLEL_THREADS_PER_CPU
.
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 when creating 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 subsequently have a failure that causes you to restore from a backup (taken prior to the index creation), then 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.
In addition to NOLOGGING
, you can use the PARALLEL
clause to increase the speed of an index creation. For large indexes, this can significantly decrease the time required to create an index.
Keep in mind that you can use NOLOGGING
in combination with PARALLEL
. This next example rebuilds an index in parallel while generating a minimal amount of redo:
SQL> alter index inv_idx1 rebuild parallel nologging;
You have an index consuming space in a segment, but without actually using that space. For example, you're running the following query to display the Segment Advisor's advice:
SELECT
'Task Name : ' || f.task_name || CHR(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || CHR(10) ||
'Segment Type : ' || o.type || CHR(10) ||
'Partition Name : ' || o.attr3 || CHR(10) ||
'Message : ' || f.message || CHR(10) ||
'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
The following output is displayed:
ADVICE
--------------------------------------------------------------------------------
Task Name : F_REGS Advice
Start Run Time : 19-feb-11 09:32
Segment Name : F_REGS_IDX1
Segment Type : INDEX
Partition Name :
Message : Perform shrink, estimated savings is 84392870 bytes.
More Info : Allocated Space:166723584: Used Space:82330714: Reclaimable S
pace :84392870:
------------------------------------------------------
You want to shrink the index to free up the unused space.
There are a couple of effective methods for freeing up unused space associated with an index:
Before you perform either of these operations, first check USER_SEGMENTS
to verify that the amount of space used corresponds with the Segment Advisor's advice. In this example, the segment name is F_REGS_IDX1
:
SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES
----------
166723584
This example uses the ALTER INDEX...REBUILD
statement to re-organize and compact the space used by an index:
SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the ALTER INDEX...SHRINK SPACE
statement to free up unused space in an index—for example:
SQL> alter index f_regs_idx1 shrink space;
Index altered.
Now query USER_SEGMENTS
again to verify that the space has been de-allocated. Here is the output for this example:
BYTES
----------
524288
The space consumed by the index has considerably decreased.
Usually rebuilding an index is the fastest and most effective way to reclaim unused space consumed by an index. Therefore this is the approach we recommend for reclaiming unused index space. Freeing up space is desirable because it ensures that you use only the amount of space required by an object. It also has the performance benefit that Oracle has fewer blocks to manage and sort through when performing read operations.
Besides freeing up space, you may want to consider rebuilding an index for these additional reasons:
Keep in mind that Oracle attempts to acquire a lock on the table and rebuild the index online. If there are any active transactions that haven't committed, then Oracle won't be able to obtain a lock, and the following error will be thrown:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
In this scenario, you can either wait until the there is little activity in the database or try setting the DDL_LOCK_TIMEOUT
parameter:
SQL> alter session set ddl_lock_timeout=15;
The DDL_LOCK_TIMEOUT
initialization parameter is available in Oracle Database 11g or higher. It instructs Oracle to repeatedly attempt to obtain a lock for the specified amount of time.
If no tablespace is specified, Oracle rebuilds the index in the tablespace in which the index currently exists. Specify a tablespace if you want the index rebuilt in a different tablespace:
SQL> alter index inv_idx1 rebuild tablespace inv_index;
Tip If you're working with a large index, you may want to consider using features such as NOLOGGING
and/or PARALLEL
(see Recipe 2-16 for details).
If you use the ALTER INDEX...SHRINK SPACE
operation to free up unused index space, keep in mind that this feature requires that the target object must be created within a tablespace with automatic segment space management enabled. If you attempt to shrink a table or index that has been created in a tablespace using manual segment space management, you'll receive this error:
ORA-10635: Invalid segment or tablespace type
As we've noted elsewhere in this chapter, we recommend that you use the ASSM feature whenever possible. This allows you to take advantage of all the Oracle segment management features.
The ALTER INDEX...SHRINK SPACE
statement has a few nuances to be aware of. For example, you can instruct Oracle to attempt only to merge the contents of index blocks (and not free up space) via the COMPACT
clause:
SQL> alter index f_regs_idx1 shrink space compact;
The prior operation is equivalent to the ALTER INDEX...COALESCE
statement. Here's an example of using COALESCE
:
SQL> alter index f_regs_idx1 coalesce;
If you want to maximize the space compacted, either rebuild the index or use the SHRINK SPACE
clause as shown in the “Solution” section of this recipe. It's somewhat counterintuitive that the COMPACT
space doesn't actually initiate a greater degree of realized free space. The COMPACT
clause instructs Oracle to only merge index blocks where possible and not to maximize the amount of space being freed up.