Indexes

This discussion is mainly on index and index partition segments.

Oracle DBA is concerned with the growth and fragmentation and physical properties of the indexes and index segments.

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. You can use indexes without rewriting any queries.

Indexes are created as help-tools in many database designs. This is wrong and a bad thinking.

How many types of Indexes I can create?

Oracle provides several indexing schemes that provide complementary performance functionality.

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. Oracle automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

Data is often inserted or loaded into a table using the either the SQL*Loader or Import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, Oracle then must update every index as each row is inserted.

Creating an index on a table that already has data requires sort space. Some sort space
comes from memory allocated for the index's creator. The amount for each user is determined
by the initialization parameter SORT_AREA_SIZE. Oracle also swaps sort information to and
from temporary segments that are only allocated during the index creation in the user
temporary tablespace.

Under certain conditions, data can be loaded into a table with SQL*Loader's direct path load
and an index can be created as data is loaded.

What Oracle suggests to me before I decide to create an Index?

Oracle says,

Create an index if you frequently want to retrieve less than 15% of the rows in a large
table. The percentage varies greatly according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

Create an index to improve performance on joins of multiple tables, index columns used for
joins. While primary and unique keys automatically ve indexes, but you might want to create
an index on a foreign key.

Small tables do not require indexes if they are too small

How do I identify the candidate columns for indexing per Oracle?

Strong candidates for indexing are identified by if the

01. Values are relatively unique in the column
02. There is a wide range of values (good for regular indexes)
03. There is a small range of values (good for bitmap indexes)
04. The column contains many nulls, but queries often select all rows having a value.
In this case, use the following phrase:
WHERE COL_X > -9.99 * power(10,125)
Using the above phrase is preferable to:
WHERE COL_X IS NOT NULL
This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

A good understand of NULL shall help a lot of good logic a programmer can put in for accomplishing a process.

Let me tell you about the WEAKEST candidates for indexing

01. There are many nulls in the column and you do not search on the non-null values.
02. You cannot index LONG and LONG RAW columns
03. The size of a single index entry cannot exceed roughly one-half (minus some overhead) of
the available space in the data block.

Note:

Although Oracle allows an unlimited number of indexes 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.

Basing on the nature of Indexes Oracle allows you to create

Unique Index:

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.

Non-Unique Index:

A non-unique 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 non-unique value.

Composite Index:

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.

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.

A few tips to identify the kind of index you need to create on a table on a column/columns

I some time back called a database design as “QUERY DRIVEN DATABASE DESIGNS” when I was given the reports a user is to see regularly and asked to design the database. (I was told that they need performance for those reports and they were around 108).

Choose columns that are most frequently specified in WHERE clauses. Frequently accessed columns can benefit most from indexes. This is to be determined along with the design of the table to hold the data a user is to access by analyzing the data access requirements after obtaining the requirements from the customer/user.

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. We may not able to avoid in many cases and the customer is to live with the cost of such data modifications and their costs at Oracle level.
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.

There will be occasions where business logic may not permit the usage of the indexes columns in a particular way in the WHERE clause. Then, to force Oracle to use the indexes use HINTS in the SQL statements and force oracle to use indexes.

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.

Partitioned Indexes
(Source Metalink Doc Id 165309.1)

Partitioning of the indexes is to be decided by the DBA and the Developer Team at the time of designing the database.

They need to analyze the indexing needs for there applications and then decide. It is never an after thought of the design, which is often the case. This is because the either there is no analysis by in business requirements as to what type of data is required to be seen by what kind of user at the front end level. This analysis of the requirement throws abundantly enough light on designing the indexes to improve the performance of the queries and the design of the SQL and use of hints in the SQL if need be. Here is the summarized info which decides on the requirement, design and deploying of indexes

1. Type of access to data through the application.
2. Performance in accessing data.
3. Availability
4. Whether parallel operations are possible

The kinds of Indexes that are supported by Oracle are

01. Local prefixed indexes.
02. Local non-prefixed indexes
03. Global prefixed indexes
04. Non Partition Indexes.

Creating Partitioned indexes

Creation of local prefixed partitioned indexes:

In a local index all entries in a single local index partition point to one and only one table partition. A partitioned index is considered locally prefixed if the partition key is based on the left most columns in the index.

Creation of local non-prefixed partitioned indexes:

A partitioned index is considered locally non-prefixed if the partition key is based on something other than the left most columns in the index.

Creation a global prefixed partitioned indexes:

This method enables the partitioning to be made on a partition key different from that of the underlying table. This type of index must be prefixed. So any entry in the global index may point to any partition of the partitioned table.

Maintenance operations:

The following table lists the maintenance operations that can be performed on index partitions. It indicates on which type of index (global or local) they can be performed. Global indexes can be only partitioned by range

Maintaining Global Indexes
Prior to Oracle 9i, when DDL operations were used, index partitions that were related to data partitions (Affected by DDL) get invalidated. One option is to rebuild the indexes. With 9i, Along with Partition DDL, user can specify UPDATE GLOBAL INDEXES clause, the global indexes becomes available. So the need of rebuilding is avoided. VII. Updating Global Indexes

This is available when executing the following DDL:
ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE, and COALESCE.
Only Valid Indexes are updated.
The clause is not supported with Index Organized Tables.

Source Metalink Doc ID