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.

Index Types

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.

How the Oracle Index Works

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.

Figure 13.1.

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.

Figure 13.2.

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.

Deciding What to Index

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:

• Index tables when queries select only a small number of rows. Queries that select a large number of rows defeat the purpose of the index. Use indexes when queries access less than 5% of the rows in the table.

• Don't index tables that are frequently updated. Updates, insertions, and deletions incur extra overhead when indexed. Base your decision to index on the number of updates, insertions, and deletions relative to the number of queries to the table.

• Index tables that don't have duplicate values on the columns usually selected in WHERE clauses. Tables in which the selection is based on TRUE or FALSE values are not good candidates for indexing.

• Index tables that are queried with relatively simple WHERE clauses. Complex WHERE clauses might not take advantage of indexes.

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:

• Choose columns that are most frequently specified in WHERE clauses. Frequently accessed columns can benefit most from indexes.

• Don't index columns that do not have many unique values. Columns in which a good percentage of rows are duplicates cannot take advantage of indexing.

• Columns that have unique values are excellent candidates for indexing. Oracle automatically indexes columns that are unique or primary keys defined with constraints. These columns are most effectively optimized by indexes.

• Columns that are commonly used to join tables are good candidates for indexing.

• Frequently modified columns probably should not be index columns because of the overhead involved in updating the 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:

• When two columns are not unique individually but are unique together, composite indexes might work very well. For example, although columns A and B have few unique values, rows with a particular combination of columns A and B are mostly unique. Look for WHERE clauses with AND operators.

• If all values of a SELECT statement are in a composite index, Oracle does not query the table; the result is returned from the index.

• If several different queries select the same rows with different WHERE clauses based on different columns, consider creating a composite index with all the columns used in the WHERE statements.

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.

Taking Advantage of Indexes

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.

How Indexes Improve Performance

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.

Creating Indexes

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.

Creating Indexes with Schema Manager

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.

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:

• Name--The name of the index. Each index must have a unique identifier.

• Schema--The schema where the index resides. The index and the table need not be within the same schema.

• Type--This is used for partitioning. If this were a partitioned table, you would be able to choose whether your index was local or global.

• Index On--You can choose whether you are indexing a table or a cluster.

• Schema--The schema where the table resides.

• Table--The table to be indexed.

• Index Columns--After the table has been selected, a list of the table's columns is displayed. If you click the Order column in the order you want the index columns to be created, you will see a number appear. This indicates the index order.

• Options--Optional parameters define whether the index is unique, sorted, and whether the index creation is written to the log file. For large indexes, it might be beneficial to abstain from logging the index creation to save log space and improve performance.

Figure 13.4.

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.

Figure 13.5.

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.

Creating Indexes with the CREATE INDEX Command

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.

• BITMAP--This parameter is used to specify a bitmap index. The bitmap index is described in detail in the section titled "Bitmap Indexes."

• NOSORT--This specifies to the index-creation process that the rows are already in sorted order, thus skipping the sort process. This can save substantial time in creating the index.

• REVERSE--This parameter specifies that the blocks in the index are stored in reverse order.

• GLOBAL--This parameter specifies a global partitioned index, as described in the section titled "Global Indexes."

• PARTITION--This parameter is used for partitioned indexes, as described in the section titled "Partitioned Indexes."

• VALUES LESS THAT--This parameter is also used for partitioned indexes, as described in the section titled "Partitioned Indexes."

• PARALLEL--This parameter allows you to specify the degree of parallelism for the index-creation process. For large indexes, the time savings can be substantial.

• STORAGE clause--This parameter allows you to modify the storage parameters for the index. This is the same STORAGE clause you saw before.

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.

INPUT:

Listing 13.1. Using the CREATE INDEX command.

CREATE INDEX "ETW".dogs_ix1
ON  "ETW"."DOGS" ("ID")
PARALLEL (DEGREE 10)
TABLESPACE DOGS; 

ANLYSIS:

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.

---

Partitioned Indexes

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:

• Global indexes--These are created in a manner different from the underlying partitioning of the table that is indexed.

• Local indexes--These are partitioned in the same manner as the underlying table partitioning.

Global Indexes

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:

• GLOBAL--This parameter specifies a global partitioned index.

• PARTITIONpart_name--This parameter is used to identify the partition. If you do not specify the partition name, a default name will be provided. It is not usually necessary to provide the partition name.

• VALUES LESS THAT--This parameter is used to specify the range that is allocated for that particular partition in the same way as the partition was specified in the CREATE TABLE statement (discussed yesterday).

---

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.

INPUT:

Listing 13.2. Creating a partitioned index.

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);

ANLYSIS:

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.

Local Indexes

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:

• Because the index exists entirely on one partition, any maintenance operations affect only that one partition.

• The Oracle optimizer can use the local index to generate better query plans based on the fact that a local index is used.

• If a partition is lost and must be recovered, only the data and index for that particular partition needs to be recovered. With a global index, the entire index would need recovery.

As you can see, there are many advantages of using both global and local partitioned indexes.

Index-Only Tables

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:

• Because it is part index and part table, no other indexes can be added to the index-only table.

• The UNIQUE constraint is not allowed on an index-only table.

• A trigger can be added to the index-only table.

• An index-only table cannot be stored in a cluster.

• Replication is not supported at this time.

As you can see, there are some restrictions on index-only tables, but there are also a great deal of benefits.

When to Use Index-Only Tables

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.

How to Create 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:

• ORGANIZATION INDEXED--This qualifier specifies an index-only table organization.

• OVERFLOW TABLESPACEts_name--This qualifier specifies the overflow tablespace name.

• PCTTHRESHOLDthreshold--This qualifier specifies the percent of a block that a row must be larger than in order to be offloaded to the overflow tablespace.

An example of how to create an index-only table is shown in Listing 13.3.

INPUT:

Listing 13.3. Creating an index-only table with the CREATE TABLE command.

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"; 

ANLYSIS:

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.

---

Bitmap Indexes

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.

Figure 13.6.

A bitmap index.

When to Use Bitmap Indexes

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

• Marital status

• Account status (good or bad)

• Sales region (if there are only a few)

• Rank (if there are only a few)

• Special notes (whether there is a note)

With columns that have low cardinality, the bitmap index can greatly improve performance. Columns with high cardinality are not candidates for bitmap indexes.

How to Create Bitmapped 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.

Table Sequences

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.

Creating Sequences with Schema Manager

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.

Figure 13.7.

The Create Sequence screen.

Creating Sequences with the CREATE SEQUENCE Command

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:

• INCREMENT BY--This parameter specifies the amount by which to increment the sequence each time a value is obtained.

• START WITH--This parameter specifies the starting value.

• MAXVALUEn--This parameter specifies the maximum value that the sequence can obtain.

• NOMAXVALU (Default)--This parameter specifies no maximum value for a sequence. The sequence can grow to 10²⁷ for ascending sequences and -1 for descending sequences.

• MINVALUEn--This parameter specifies the minimum value of the sequence.

• NOMINVALU (Default)--This parameter specifies no minimum value for a sequence. The sequence can have a minimum of 1 for ascending sequences and -10²⁶ for descending sequences.

• CYCLE--This parameter specifies that a sequence will restart after reaching the maximum or minimum value.

• NOCYCLE (Default)--This parameter specifies that the sequence cannot recycle after reaching the maximum or minimum value.

• CACHEn--This parameter specifies the number of sequence entries to cache for quick access. (The default is 20 values.)

• NOCACHE--This parameter specifies that no sequence entries should be cached.

• ORDER--This parameter specifies that sequence entries are generated in the order in which they are requested. By default, this is not the case.

• NOORDER (Default)--This parameter specifies that sequence numbers are not necessarily generated in the order in which they are requested. This is usually fine for primary key values.

Tuning Sequences

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.

Using Sequences

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.

INPUT:

Listing 13.4. Inserting a row using a sequence.

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.

---

Using Cached Sequences for Primary Key Values

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.

---

Summary

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.

What's Next?

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.

Q&A

Q How are indexes important to an RDBMS?

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.

Workshop

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."

Quiz

1. How many indexes can be created on a single table?

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?

Exercises

1. Create a simple index on one column using Schema Manager.

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.