An index is an optional structure designed to help you gain faster access to data. Just like the index in this book, an Oracle index is logically and physically independent of the data in the associated table or cluster. You can use the index to speed access to the data or you can retrieve the data independently from the index by searching the tables for it. When optimally configured and used, indexes can significantly reduce I/O to the datafiles and greatly improve performance.
The presence of an index is transparent to the user or application and requires no application changes. However, if you are aware of an index, you should be able to design your applications to take better advantage of those indexes. The only indication of an index might be an improved access time to data.
The index itself should be created with some knowledge of the application and data-access patterns. If indexes are created on columns that are not used to access the data, the index is useless.
After an index has been created for a table, Oracle automatically maintains that index. Insertions, updates, and deletions of rows in the table automatically update the related indexes.
A table can have any number of indexes, but the more indexes there are, the more overhead is incurred during table updates, insertions, and deletions. This overhead is incurred because all associated indexes must be updated whenever table data is altered.
TIP: Indexes can be created with the Parallel Index Creation feature of the Parallel Query option. Using this feature greatly reduces index-creation time. Because all data must be read to create the index, a table scan is forced. This table scan is parallelized, greatly improving performance.
It is often necessary to create a sequence of numbers to be used as an identifier in your application. This number might be an account number, order number, or some other sort of ID number. Rather than generating these numbers manually, Oracle has a facility to generate these sequences of numbers automatically. To create a unique sequence of numbers on your own, you would have to lock the record that has the last value of the sequence, generate a new value, and then unlock the record. To avoid locking these records, Oracle provides a sequence generator that performs this service for you.
The Oracle sequence generator can generate sequential numbers of up to 38 digits, without having to manually lock records. When you define a sequence, you can specify the original values of the sequence, whether the sequence should be cached, and whether the sequence should be in ascending or descending order. Later today you will learn how to use the Oracle sequence generator to automatically generate sequences of numbers for you.
There are several different types of indexes. An index can be limited to one column value or can consist of several columns. An index can be either unique or nonunique.
New Term: A unique index is an index value that has the additional constraint that the set of indexed columns defines a unique row. Although this constraint might be specified, it is usually better to associate this constraint with the table itself rather than with the index. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key.
New Term: A nonunique index does not impose the constraint that the index value be unique. Such an index can be quite useful when quick access is desired on a nonunique value.
New Term: Another type of index is a composite index, which indexes several columns in a table. These column values can be in any order and the columns do not have to be adjacent in the table.
A composite index is useful when SELECT statements have WHERE clauses that reference several values in the table. Because the index is accessed based on the order of the columns used in the definition, it is wise to base this order on the frequency of use. The most-referenced column should be defined first, and so on.
The index should be created based on the values accessed in the application; the application should be developed to take advantage of these indexes. Having knowledge of and influence over these indexes can be very useful to the application developer.
New Term: When an index is created, an index segment is automatically allocated. This index segment contains information that speeds access to data by determining the location of indexed data with as few I/Os as possible. Oracle indexes data by using an index structure known as a B*-tree index. A B*-tree index is designed to balance the access time to any row. A B*-tree index is a tree of descending comparison values, as shown in Figure 13.1. As you traverse down the index, you compare the desired value with the values in the upper-level index blocks, called branch blocks. Based on the outcome of the comparison with the branch blocks, you compare the desired value with more branch blocks until you reach the lowest-level index blocks. The index blocks on the lowest level, called leaf blocks, contain every indexed data value and the associated ROWID of that data.
The B*-tree index structure.
With a unique index, there is one ROWID per data value in the leaf block, as shown in Figure 13.2. With a nonunique index, there might be several values associated with the data value. In the case of the nonunique index, the data values are sorted first by the index key and then by the ROWID.
The index block structure.
With a B*-tree index, all the leaf blocks are at the same level. Access of index data takes approximately the same time regardless of the value of the data. B*-tree indexes provide quick access to data whether it is an exact match or a range query. In addition, B*-tree indexes provide good performance regardless of the size of the table; performance does not degrade as the table grows.
An index is effective only when it is used. The use of the index is determined primarily by the column values that are indexed. Remember that the more indexes you have on a table, the more overhead is incurred during updates, insertions, and deletions. Therefore, it is important to index selectively. Use the following guidelines for deciding which tables to index:
If you decide to use an index, it is important to determine the columns on which you put the index. Depending on the table, you might choose to index one or more columns. Use the following guidelines for deciding which columns to index:
In certain situations, the use of composite indexes might be more effective than individual indexes. Here are some examples of where composite indexes might be quite useful:
Composite indexes can be quite useful when they are carefully designed. As with single-column indexes, they are most effective if applications are written with the indexes in mind.
After you create the index, you should periodically use the SQL Trace facility to determine whether your queries are taking advantage of the indexes. It might be worth the effort to try the query with and without indexes and then compare the results to see whether the index is worth the space it uses.
In summary, indexes can significantly improve performance in your system if they are used properly. You must first decide whether an index is appropriate for the data and access patterns in your particular system. After you decide to use an index, you must decide which columns to index. Indexing an inappropriate column or table can reduce performance. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times. Careful planning and periodic testing with the SQL Trace feature can lead to a very effective use of indexes, with optimal performance being the outcome.
Because one or more columns are indexed, it is necessary to include the indexed column or columns in the WHERE clause of the SQL statement. Because the indexed column or columns are referenced in the WHERE clause, the optimizer will immediately know to use an index scan rather than a table scan to access the requested data. If you do not include the columns that are indexed in the WHERE clause of the SQL statement, the index will probably be bypassed, thus causing a table scan.
TIP: It takes the right application coding to take advantage of indexes. If one or more columns are indexed but are not referenced in the WHERE clause of the SQL statements accessing that table, the index will not be used. Coordination between the DBA and the application developers is required to take advantage of indexes.
You can tell whether you are taking advantage of indexes by using the Oracle EXPLAIN PLAN facility to show the query execution plan that the optimizer has chosen for your SQL statements. If you are using the index as expected, you will see an index scan in the execution plan.
New Term: If there are no indexes on your table, the only way Oracle can find the data you want is to search every piece of data in the table and compare it against the requested data. This is typically referred to as a table scan or full-table scan. A table scan is not very efficient in most cases because you typically want to select or update only a few rows.
Because I/Os are expensive operations on a computer system, table scans are very expensive. Reducing the amount of data that is read from the disk is desirable. By reducing the amount of data that is read, you reduce system overhead. An index improves your performance by knowing exactly where the data is on disk and avoiding costly table scans, thus reducing I/O overhead.
Indexes can be created either via the graphical utilities provided with Oracle Enterprise Manager or via the CREATE INDEX command. Because Enterprise Manager and Schema Manager provide essentially the same functionality, I will not discuss Enterprise Manager here. Schema Manager provides all the functionality of Enterprise Manager, but with additional features.
Schema Manager is used to administer all Oracle schema objects. As you have seen, indexes and tables are schema objects. Schema Manager provides an easy way to create indexes graphically. To create an index with Schema Manager, drill down into the table on which you want to create the index. Right-click the Indexes option under the table's entry, as shown in Figure 13.3.
Creating an index with Schema Manager.
This invokes the Create Index screen, where you provide the information required to create the index on the selected table. The initial Create Index screen is shown in Figure 13.4. A number of options must be filled in before the index can be created:
The Create Index screen.
A completed Create Index screen is shown in Figure 13.5. Here I have selected to index the DOGS ID field. This will speed up access to the DOGS table whenever I am using the ID column in the WHERE clause. When I access a row in the database where the selection criteria are based on a value of the ID column, the index will be used.
Creating an index for the DOGS table on the ID field.
That is really all there is to creating an index using Schema Manager. The index will be automatically maintained by Oracle after the index has been created. If you use the CREATE INDEX command, you have more options available.
With this command you can specify all the options available with Schema Manager, as well as other options. Rather than go over all the options available with the CREATE INDEX command, I will highlight some of the optional parameters that are not available with the graphical tools. Please reference the Oracle SQL Reference Manual for the complete syntax of the CREATE INDEX command.
In many cases, creating the index by hand with the CREATE INDEX command can be beneficial. In addition to allowing you more control over the storage and parallelism of the index creation, through scripting you have a saved record of the index-creation process. If you are creating indexes on large tables, the CREATE INDEX command might be the best way for you to create the index. An example of the CREATE INDEX procedure is shown in Listing 13.1.
CREATE INDEX "ETW".dogs_ix1 ON "ETW"."DOGS" ("ID") PARALLEL (DEGREE 10) TABLESPACE DOGS;
This creates the same index as shown with Schema Manager except that it uses a parallelism of 10. This allows 10 parallel processes to scan the table to retrieve the data necessary to create the index.
NOTE: There is no limit to the number of indexes that you can create on a table. Remember that each time a column is updated or inserted, the index might need to be modified. This causes overhead on the system. For tables whose access pattern is mostly read, there is very little penalty for multiple indexes except in terms of the space they use. Even though indexes can take up considerable space, they are well worth it.
As you saw yesterday, a new feature of Oracle8 is range partitioning. Like tables, indexes can also be partitioned; but with indexes you have a few more options because the underlying table might or might not also be partitioned. There are essentially two different types of partitioned indexes available with Oracle8:
New Term: A global index can be thought of as a single B*-tree index over the entire contents of the table. Even though this is one large index, it can be partitioned. This single index has information about all rows in all partitions in the table.
To create a global partitioned index, use the CREATE INDEX parameter GLOBAL. This specifies that the index will be a global index. Further partitioning of the index is accomplished by using the following parameters:
NOTE: The last partition should contain the keyword MAXVALUE for its range.
For example, to range-partition the index that was created earlier in this lesson, use the syntax that appears in Listing 13.2.
CREATE INDEX "ETW".dogs_ix1 ON DOGS (ID) PARTITION BY RANGE (ID) PARTITION pt1 VALUES LESS THAN (`1000') TABLESPACE ts1, PARTITION pt2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2);
This create two partitions, the first holding values of ID that are less than 1,000, the second holding the remaining values of ID. If you do not specify the partition name, as is the case here, a default name will be provided.
New Term: In contrast to the global index, a local partitioned index is individually created on each partition. If you specify a local partitioned index, Oracle automatically maintains the index's partitioning along with that of the underlying table.
Local partitioned indexes are created through the use of the LOCAL parameter with the CREATE INDEX statement. It is unnecessary to provide partitioning information because the underlying table partitioning will be used. A local index can be created with the following syntax:
CREATE INDEX "ETW".dogs_ix1 ON DOGS (ID) LOCAL;
Because the index is local, all partition changes to the table will be automatically reflected on the index partitions as well.
Local partitioned indexes have some inherent advantages that are similar to the advantages you get from partitioned tables. These advantages include the following:
As you can see, there are many advantages of using both global and local partitioned indexes.
New Term: An index-only table is a schema object introduced in Oracle8. An index-only table is similar to an index, but whereas an index contains the primary key value and a ROWID pointing to where the data is kept, the index-only table stores the column data in the leaf block of the index.
Because the leaf blocks of the Oracle index are traditionally very small and tightly packed, there can be some drawbacks to having large rows stored there. Oracle has developed a way to compensate for this: If rows become too large (by a set threshold), the row data is stored in an overflow area as specified in the CREATE TABLE statement. This creates storage more like the traditional index and table relationship.
An index-only table contains the same structure as the Oracle B*-tree index. Only the leaf blocks have changed. Index-only tables have many of the attributes of both indexes and tables, but there are a few exceptions:
As you can see, there are some restrictions on index-only tables, but there are also a great deal of benefits.
Index-only tables are very useful whenever data is always accessed via the primary key index. If this is the case with your data, the index-only table will cut down on the space used by both the index and the table (by combining them) and improve performance. Performance is improved because, by the time the ROWID would have been retrieved, you have the data.
Tables that are not accessed via the primary key value are not good candidates for index-only tables. Also, tables whose primary key values are updated and tables that have frequent insertions are not good candidates for index-only tables.
Index-only tables are created with the CREATE TABLE command; the ORGANIZATION INDEXED qualifier is used to identify the table as index-only. The following qualifiers are used in creating index-only tables:
An example of how to create an index-only table is shown in Listing 13.3.
CREATE TABLE "ETW".DOGS ( ID NUMBER, NAME VARCHAR2(40), OWNER_ID NUMBER, BREED_ID NUMBER, RANK NUMBER NULL, NOTES VARCHAR2(80) PRIMARY KEY(ID) ) ORGANIZATION INDEXED PCTTHRESHOLD 40 OVERFLOW TABLESPACE "DOGS2" TABLESPACE "DOGS";
This specifies that the index-only tablespace be created on the DOGS tablespace, whereas the overflow tablespace used is DOGS2.
NOTE: It is necessary to specify the PRIMARY KEY value when creating an index-only table. This is the value on which the index is created.
New Term: Another type of index available in Oracle8 is the bitmap index. With the traditional index you saw earlier, Oracle uses a B*-tree method to traverse the index to find the leaf block. With a bitmap index, a bitmap of ROWIDs is kept; this bitmap indicates which rows correspond to the index item. If the bit is set, this indicates that the corresponding row contains the key value; if the bit is not set, the opposite is true.
As you can probably tell, bitmap indexes can probably be quite useful under the right circumstances, and useless overhead otherwise. An example of a bitmap index is shown in Figure 13.6.
A bitmap index.
As you can probably guess, the bitmap index works well on items with low cardinality. Low cardinality means there is a small amount of variance in the possible values stored in that column. For example, the column representing the sex of the dog is said to have low cardinality because only two values are possible. Other column types that might have low cardinality include
With columns that have low cardinality, the bitmap index can greatly improve performance. Columns with high cardinality are not candidates for bitmap indexes.
A bitmap index is created with the CREATE INDEX command with the BITMAP qualifier. For example, the following will create a bitmap index:
CREATE BITMAP INDEX
To create a bitmap index on the SEX field in the DOGS table, you can use the following syntax:
CREATE BITMAP INDEX "ETW".dogs_bx1 ON DOGS (SEX);
This simple statement will create the bitmap index on the column specified. At this time, bitmap indexes cannot be created with the graphical tools.
It is frequently necessary to generate a sequence of numbers to use in your database. For example, you might need these numbers to identify a particular record. To create a unique sequence of numbers on your own, you would have to lock the record that has the last value of the sequence, generate a new value, and then unlock the record. To avoid locking these records, Oracle provides a sequence generator that performs this service for you.
The Oracle sequence generator can generate sequential numbers with as many as 38 digits without having to manually lock records. When you define a sequence, you can specify the original values of the sequence, whether the sequence should be cached, and whether the sequence should be in ascending or descending order.
Sequences can be created either with graphical tools, such as Schema Manager, or with the CREATE SEQUENCE command.
To create a sequence with Schema Manager, select the Create option after right-clicking the Sequence icon. This invokes the Create Sequence screen. By filling in parameters such as Minimum, Maximum, Increment, and so on, you can specify how the sequence works. A filled-in Create Sequence screen is shown in Figure 13.7.
The Create Sequence screen.
Sequences can be created with the CREATE SEQUENCE command. This command has the following syntax:
CREATE SEQUENCE DOG_ID INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 4;
The following provides brief descriptions of each parameter:
To get the best performance out of sequences, you should cache as many sequences as you think you will have simultaneous requests for. By over-specifying the number of cached sequences, you use more memory than necessary. By under-specifying the number of cached entries, you cause undue waiting for the sequences.
Using the Oracle sequence generator is much more efficient than manually generating sequences. If you have a series of values that must be sequential, I recommend using the Oracle sequence generator.
To generate a new sequence value, simply reference the value of sequence_name.NEXTVAL. To re-reference that number from within the same SQL block, reference the value of sequence_name.CURVAL. When you reference sequence_name.NEXTVAL, a new sequence number is generated.
Listing 13.4 contains the sequence created in the preceding section to generate a new value in the DOGS table. The result of this INSERT statement is to insert a dog with a sequentially growing value for the ID column. Remember, the ID column of the DOGS table is a sequential value for the dog's ID number.
SQL> INSERT INTO dogs 2 ( id, name) 3 VALUES 4 ( DOG_ID.NEXTVAL, `Shasta'); 1 row created.
Sequences are incremented as they are accessed, independent of rollback or commit. If a transaction generates a sequence and then rolls back, the sequence is not replaced. Therefore, there might be holes in your sequential values. This is usually not a problem.
NOTE: Because sequences are generated independently of commits or rollbacks, you might have gaps in the sequences. Although this is usually not an issue, you should make a note of it.
As shown in the preceding example, it can be efficient to use cached sequences to generate unique primary-key values. Not only is the performance of the cached sequence good, you are guaranteed a unique number (unless you have enabled CYCLE).
WARNING: If you use cached sequences to generate primary-key values, be sure to set the NOCYCLE parameter for the sequence and make sure the minimum and maximum values are sufficiently high. Cycling sequences causes integrity constraints to be violated.
Today you learned about indexes and sequences. An index is an optional structure designed to help you gain faster access to data. Just like the index in this book, an Oracle index is logically and physically independent of the data in the associated table or cluster. You can use the index to speed access to the data or you can retrieve the data independently from the index by searching the tables for it. When optimally configured and used, indexes can significantly reduce I/O to the datafiles and greatly improve performance.
The presence of an index is transparent to the user or application and requires no application changes. However, if you are aware of an index, you should be able to better design your applications to take advantage of it. The index itself should be created with some knowledge of the application and data-access patterns. If indexes are created on columns that are not used to access the data, the index is useless.
You also learned about the Oracle sequence generator. Using the sequence generator, you can generate a unique sequence of numbers quickly and without duplication. To create a unique sequence of numbers on your own, you would have to lock the record that has the last value of the sequence, generate a new value, and then unlock the record. To avoid locking these records, Oracle provides a sequence generator that performs this service for you.
The Oracle sequence generator can generate sequential numbers with as many as 38 digits, without having to manually lock records. When you define a sequence, you can specify the original values of that sequence, whether the sequence should be cached, and whether the sequence should be in ascending or descending order.
Tomorrow you will learn about Oracle clusters: the index cluster and the hash cluster. You will learn what these schema objects are and how to use them. You will also learn about another Oracle schema object, the stored procedure. Stored procedures are used to perform application functions within the Oracle instance itself. Finally, you will learn about database links, which are also Oracle schema objects. After you complete Day 14, "Using Oracle Clusters, Stored Procedures, and Database Links," you will be familiar with all the Oracle schema objects.
A Indexes are important mainly from a performance standpoint. Without indexes, every access to the database would have to be done based on a scan of all records in the table. An index provides a mechanism by which to find data quickly without having to read all records in a table.
Q What are some important factors involved in creating an index?
A Several factors should be kept in mind when creating an index. First, an index is only useful when it is accessed. If you do not access the table using the columns that you have indexed in the WHERE clause, the index will not be used. Second, if the table is used primarily for historical data (many insertions, few selections), it is not a good candidate for indexing.
Q What makes a column a good candidate to be used in an index?
A Columns that have many unique values are good candidates for indexing. If a combination of columns is unique, a complex index might be better.
Q How are sequences useful?
A Sequences are useful because they provide a mechanism to generate a unique sequence of numbers quickly with reduced locking on the database.
The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Answers to the question in the workshop can be found in Appendix A, "Answers."
2. Can an index be created in parallel?
3. What is a B*-tree index?
4. Can indexes be partitioned?
5. What is an index-only table?
6. What is a bitmap index?
7. What is a sequence used for?
8. How big can a sequence be?
9. Does an index need to be based on unique values?
10. Can an index be used to enforce uniqueness?
2. Create a compound index using Schema Manager.
3. Delete that index using Schema Manager.
4. Create and delete a sequence using Schema Manager.
© Copyright, Macmillan Computer Publishing. All rights reserved.