Creating Indexes

Indexes are created either in the account of the user who owns the table on which they are built, or in a different account. It is generally the practice to create all indexes in the table owner's account.

There are several things to consider when you are deciding whether to create an index. This section discusses many of these considerations.

Criteria for Creating an Index

You will likely want to create an index if you have queries that frequently retrieve less than roughly 15% of the rows in a large table. The percentage naturally varies somewhat according to the relative speed of a full table scan on that table and that column and on exactly how clustered the row data is in relationship to the index key. The faster the table scan runs, the lower the percentage of rows has to be to really be able to justify the index. The more clustered the row data is with respect to the index column, the higher the percentage should be.

You would create an index to improve the query performance on joins of multiple tables. The index columns in this case would be used for joins. Primary keys (covered in Chapter 14, "Maintaining Data Integrity") already have indexes on them, but foreign keys might be a good candidate for indexing so that the joins perform optimally. Also, foreign key columns are excellent candidates for indexing when there are a significant number of concurrent inserts, updates, or deletes to the parent and child tables. Without this index, the entire child table would have to be locked whenever the parent tables data is changed.

Small tables are not likely to benefit from indexes because full table scans will likely be done regardless of indexing. If queries with relationship to the small table become increasingly long, you may find that the table has grown, and an index might be beneficial.

Columns that might be strong candidates for indexing have one or more of the following characteristics:

• Relatively unique values

• A very wide range of values

• A very small range of values

• Contain NULL values yet have queries that frequently select only from rows that have a value

The following would not be good candidates for indexing:

• Columns that have many NULL values and that have queries that do not limit their searches to NON-NULL values are less suitable for indexing.

• Columns that have LONG and LONG RAW data types cannot be indexed.

The size of a single index entry should not exceed roughly one-half to two-thirds (minus some overhead) of the available space in the data block.

The order that the columns appear in the CREATE INDEX statement can affect query performance. Although Skip Scanning (covered in relation to the Performance Tuning exam) can play a significant role, in general it is better to specify the most frequently used columns first. If you create a single index that includes columns 1, 2, and 3 in the table and then have many queries that access just column 1, the query is speeded up, but a query that accesses just column 2 or just column 3, or columns 2 and 3 together will not gain any benefit from the index.

Any table can have any number of indexes. Oracle does not set a definitive limit. However, it is important to remember that the more indexes you have on a table, the more overhead there is whenever the table or its data is modified, inserted, or deleted.

To create an index in your own schema, Oracle requires that a user meet at least one of the following conditions:

• The table or the cluster to be indexed must be in that user's schema.

• That user must have INDEX privilege on the table to be indexed.

• The user must have the CREATE ANY INDEX system privilege.

More on privileges is covered in Chapter 17, "Managing Privileges."

To create an index in another user's schema, all the following conditions must be true:

• The user creating the index must have the CREATE ANY INDEX system privilege.

• The user building the index has to have a quota on the tablespace that is going to contain the index or UNLIMITED TABLESPACE system privilege.

Now that we have discussed the types of indexes, we need to discuss how to create these indexes.

Creating a B-Tree Index

If we were to create a B-tree index on our friends table created in Chapter 12, "Managing Tables," we could use the following syntax:

 CREATE INDEX friends_last_names_idx ON friends(lname) TABLESPACE useridx; 

This syntax creates an index on the last names of the entries in the table.

The general index creation syntax follows:

[View full width]
 CREATE [UNIQUE | NONUNIQUE] schema.index ON schema.table(columns) [ASC | DESC] TABLESPACE  [tablespace] {[PCTFREE integer] [INITRANS integer] [MAXTRANS integer] [STORAGE clause]  [LOGGING | NOLOGGING] [NOSORT] 

The general syntax includes the following keywords:

• UNIQUE is used to specify a unique index (nonunique is the default).

• Schema is the owner of the table (when connected to the table name) and the index (when connected to the index). Recall that they can be different schemas.

• Index is the name of the index you are creating.

• Table is the name of the table on which you are creating the table.

• Column is the name of the column on which you are creating the index.

• ASC|DESC indicates whether the index should be created in either ascending or descending order.

• Tablespace indicates the tablespace where the new index will be created. If not included, it will be created in the default tablespace of the user creating the index.

• PCTFREE is the amount of free space to be reserved in each block (this is the percentage of total space minus the amount of space for the block header) at the time of index creation. This is left free for the accommodation of new index entries. PCTFREE is not relevant if the tablespace in which you are creating the index is locally managed. PCTUSED does not make any sense in the case of indexes because index entries must be stored in the correct order. The user can't control when an index block is used for inserts.

• INITRANS is the number of transaction entries to preallocate in each block. The default (and the minimum) is 2.

• MAXTRANS is the maximum number of transaction entries that can be allotted concurrently to each block. The default is 255.

• STORAGE clause is determinant of how extents are allocated to the index. If you are creating the index in a locally managed tablespace, this clause will have no apparent effect.

• LOGGING specifies that the creation of the index and all subsequent index entries and other operations on the index are logged in the redo log files. Logging is the default.

• NOLOGGING specifies that the creation of the index and certain kinds of data loading situations will not be logged in the redo log files.

• NOSORT specifies that the rows are to be stored in the table in ascending order. This means that Oracle does not have to sort the rows when it creates the index and that it assumes that the data is already in sorted order. If this is not the case, the index creation will fail.

If any of the storage parameters are omitted (MINIMUM EXTENT, LOGGING|NOLOGGING, and so on), the defaults of the tablespace take effect.

When determining whether you want to build an index, or what kind of index you want to build, you need to balance your DML needs with your query needs. Indexes tend to speed up queries and slow down DML. You should, therefore, minimize indexes on highly volatile tables.

Always place indexes in a separate tablespace from the table on which they are built and not in a tablespace that has temporary segments being created in it. Although this will not provide extreme gains in performance, it will allow you to recover just the data tablespaces in case of a failure, speeding recovery, and rebuilding the indexes after the database is back online.

Now, how do we create this index in OEM?

Using OEM to Create a B-Tree Index

From a launched OEM Console, expand the database with which you will be working. Expand the Schema folder and select Indexes from the resulting navigator tree. Choose Object from the menu; then choose Create.

Expand the database that resides in the independently launched window and select Index from the list of icons; then click Create. Enter General (index name, index schema, tablespace, table schema, table, columns, and type of index that you are creating), Partitions (if appropriate), Storage characteristics, and Options (parallel or not, the degree of parallelism, and whether to override LOGGING) information in the property sheet and click Create. You can also choose to let OEM automatically define the storage and block utilization parameters based on your estimate of the initial volume for the table, the anticipated table growth rate, the anticipated insert activity on the table, and the order in which rows are to be inserted (sorted or not).

Guidelines

If you are setting extent sizes for index tablespaces, you should attempt to create those tablespaces with uniform extent sizes.

Because you really don't "lose" anything if indexes are lost, only the time to rebuild and the performance hit that your applications might see if the index gets lost, you should probably consider NOLOGGING for large indexes. The performance trade-offs are likely worth the risk. This is also a good reason to segregate indexes into their own tablespaces.

Initial transactions (INITRANS) should, in general, be higher on indexes than on the tables on which they are built. This is the reasoning behind default INITRANS being 2 rather than 1 (as it is for tables); index entries are, typically, smaller than table row entries. This means that there are usually more entries per block.

Again, because indexes work differently from the way tables work, the PCTFREE parameter also works somewhat differently between the two. PCTFREE in index building is used only during the creation of the index to reserve space for more index entries that may need to be inserted into the same blocks. When a key column in the index is updated, there is a logical delete of the original, and an insert of the updated.

You would use a low PCTFREE for indexes created on columns that include system generated numbers, such as sequence numbers. The low PCTFREE is excellent because new indexes are appended to the existing entries, and there is little or no need to insert an entry between two existing entries.

A higher PCTFREE would be more appropriate for an index where a column of an inserted row can take on any value or can fall within a range of values that already exist.

Creating a Bitmap Index

To create a bitmap index, you use the CREATE_BITMAP_AREA_SIZE parameter to specify the amount of memory to be allocated for bitmap creation. This initialization parameter determines the amount of space in memory that will be used for storing bitmap segments. The default value is 8MB; however, a larger value may enable faster index creation. Columns with low cardinality can allow this value to be lowered. As a general rule, the higher the cardinality, the more memory needed for optimal index creation performance.

The CREATE INDEX command is as follows:

 CREATE BITMAP INDEX index_name ON table_name(column); 

The only noticeable difference between the CREATE INDEX command and the CREATE BITMAP INDEX command is the addition of the BITMAP keyword and the fact that bitmap indexes cannot be unique, so will never take the UNIQUE key word.

Using OEM to Create Bitmap Indexes

The only difference between creating a B-tree index and a bitmap index is the selection of Bitmap as the type.

It is possible to change some (but not all) of the storage parameters for indexes after they are created.