Oracle Indexes

Organizations ranging from government agencies to small business, from large financial institutions to universities, use ORACLE to make sense of their data.Oracle being a well functioning system, what goes wrong isn’t basic operation,but performance.With any large peice of software operating in the real world, the number of users  increases and oragnizational needs grow.More data needs to be managed and more complex queries need to be processed. The load on teh system grows and performnce suffers. And questiones are risen like:

“ I have been waiting 30 mins for a respnse to my query—whast goin on ?”

“Our backups take too long-----how can we speed them up?”

“ Our database is fully normalised, but response time is bad---why?”

Although you may eventually find that adding computing power is necessary, there is a lot you can do to increase the performance of the system you already have.

Indexes Indexes are optional structures associated with tables. Indexes can be created to  increase the performance of data retrieval. Just as the index in a manual helps you quickly locate specific information, an Oracle index provides an access path to table data.

Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row.

Indexes are created on one or more columns of a table.Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes. Indexes are the primary means of reducing disk I/O when properly used. However,the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table.

Schema objects are the logical structures that directly refer to the database’s data. It includes structures like tables,views, and indexes.

Index segment Each index has an index segment that stores all of its data. You can partition indexes.

Extents in Indexes All extents allocated to an index segment remain allocated as long as the index  exists. When you drop the index or associated table or cluster, Oracle reclaims the extents for other uses within the tablespace.

Oracle RDBMS accesss table data in one of the two ways

·         Full table scan

·         Using Indexes

Using Indexes is always preferrable to using a full table scan as it allows more immediate access to just the desired rows.

Full table scan is preferable when you know that more than about 25% of the records in the queried tables will be selected;

 

Introduction to Index Segments

Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data. Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment.

Nulls are not indexed, except when the cluster key column value is null or the index is a bitmap index.You can create indexes for temporary tables using the CREATE INDEX statement.Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns.

For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);

CREATE INDEX employees_idx2 ON employees (job_id, last_name);

You cannot create an index that references only one column in a table if another such index already exists. Oracle provides several indexing schemes, which provide complementary performance functionality:

·         B-tree indexes

·         B-tree cluster indexes

·         Hash cluster indexes

·         Reverse key indexes

·         Bitmap indexes

·         Bitmap Join Indexes

An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. Indexes are logically and physically independent of the data in the associated table.You can create or drop an index at any time without affecting the base tables or other indexes.

Unique and Nonunique Indexes

Indexes can be unique or nonunique.Unique indexes guarantee that no two rows

of a table have duplicate values in the key column (or columns). Nonunique indexes

do not impose this restriction on the column values.

Composite Indexes

A composite index (also called a concatenated index)is an index that you create on multiple columns in a table.Columns in a composite index can appear in any order and need not be adjacent in the table. It can speed retrieval of data for SELECT statements in which the

WHERE clause references all or the leading portion of the columns in the composite

index. No more than 32 columns can form a regular composite index. For a bitmap index,the

maximum number columns is 30.

Indexes and Nulls

NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index are identical,in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing NULL values from being treated as identical. Oracle does not index table rows in which all key columns are NULL.

Function-Based Indexes

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of the function or expression and stores it in the index.

Uses of Function-Based Indexes

Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses.

For example, if you create the following index:

CREATE INDEX idx ON table_1 (a+b *(c -1),a,b);

then Oracle can use it when processing queries such as this:

SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. Eg, the following index:

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

can facilitate processing queries such as this:

SELECT * FROM employees WHERE UPPER(first_name) = ’RICHARD’;

Dependencies of Function-Based Indexes

Function-based indexes depend on the function used in an expression that defines the index. If the function is a PL/SQL or package function, the index is disabled by any changes to the function specification. The optimizer does not use an index if the indexed column is part of a function(in the where clause). In general avoid doing calculations on indexed columns , apply function and concatening on an indexed column.

Select * from account where substr(ac_acct_no,1,1)= ‘1’

Instead of the above use:

Select * from account where ac_acct_no like ‘1%’

Note: The SQL functions MIN and MAX are exceptions to this rule and will utilize all available indexes.

How Indexes Are Stored

When you create an index, Oracle automatically allocates an index segment to hold the index’s data in a tablespace. You can control allocation of space for an index’s segment and use of this reserved space in the following ways:

·         Set the storage parameters for the index segment to control the allocation of the

      index segment’s extents.

·         Set the PCTFREE parameter for the index segment to control the free space in the datablocks that constitute the index segment’s extents.

The tablespace of an index’s segment is either the owner’s default tablespace or a tablespace specifically named in the CREATE INDEX statement.

You can improve performance of queries that use an index by storing an index and its table

in different tablespaces located on different disk drives, because Oracle can retrieve both index and table data in parallel.

Format of Index Blocks

When you create an index,Oracle fetches and sorts the columns to be indexed and stores the rowid along with the index value for each row.

Eg. consider the statement:

CREATE INDEX employees_last_name ON employees(last_name);

Oracle sorts the employees table on the last_name column. It then loads the index with the last_name and corresponding rowid values in this sorted order. When it uses the index, Oracle does a quick search through the sorted last_name values and then uses the associated rowid values to locate the rows having the sought last_name value.

The Internal Structure of Indexes

Oracle uses B-trees to store indexes to speed up data access.

Consider an ordered list of the values divided into block-wide ranges (leaf blocks). The end points of the ranges along with pointers to the blocks can be stored in a search tree and a value in log(n) time for n entries could be found. This is the basic principle behind Oracle indexes.

The upper blocks branch contain index data that points to lower-level index blocks. The lowest level index blocks (leaf blocks) contain every indexed data value and a corresponding rowid used to locate the actual row. The leaf blocks are doubly linked. For a unique index, one rowid exists for each data value.

Index Properties

The two kinds of blocks:

·         Branch blocks for searching

·         Leaf blocks that store the values.

Branch Blocks Branch blocks store the following:

·         The minimum key prefix needed to make a branching decision between two keys

·         The pointer to the child block containing the key

If the blocks have n keys then they have n+1 pointers.

All leaf blocks are at the same depth from the root branch block.

Leaf blocks store the following:

·         The complete key value for every row

·         ROWIDs of the table rows

The B-tree structure has the following advantages:

·         All leaf blocks of the tree are at the same depth, so retrieval of any record from

anywhere in the index takes approximately the same amount of time.

·         B-tree indexes automatically stay balanced.

·         All blocks of the B-tree are three-quarters full on the average.

·         B-trees provide excellent retrieval performance for a wide range of queries,

     including exact match and range searches.

·         Inserts, updates, and deletes are efficient, maintaining key order for fast

      retrieval..

·         B-tree performance is good for both small and large tables and does not

      degrade as the size of a table grows.

How Indexes Are Searched

Index unique scan is one of the most efficient ways of accessing data. This access

method is used for returning the data from B-tree indexes.

Steps in Index Unique Scans

1. Start with the root block.

2. Search the block keys for the smallest key greater than or equal to the value.

3. If key is greater than the value, then follow the link before this key to the child block.

4. If key is equal to the value, then follow this link to the child block.

5. If no key is greater than or equal to the value in Step 2, then follow the link after the     highest key in the block.

6. Repeat steps 2 through 4 if the child block is a branch block.

7. Search the leaf block for key equal to the value.

8. If key is found, then return the ROWID.

9. If key is not found, then the row does not exist.

Fig shows an example of an index unique scan and is described in the text that follows the figure.

Figure 10–8 If searching for Patrick:

·         In the root block, Rh is the smallest key >= Patrick.

·         Follow the link before Rh to branch block (N, P, Ph).

·         In this block, Ph is the smallest key >= Patrick.

·         Follow the link before Ph to leaf block (Pablo, Patrick, Paula, Peter).

·         In this block, search for key Patrick = Patrick.

·         Found Patrick = Patrick, return (KEY, ROWID).

If searching for Meg:

·         In the root block, Rh is the smallest key >= Meg.

·         Follow the link before Rh to branch block (N, P, Ph).

·         In this block, Mo is the smallest key >= Meg.

·         Follow the link before Mo to leaf block (Luis,… , May, Mike).

·         In this block, search for key = Meg.

·         Did not find key = Meg, return 0 rows.

Index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns.

Key Compression

Key compression lets you compress portions of the primary key column values in an index or index-organized table, which reduces the storage overhead of repeated values. It leads to a huge saving in space which can lead to less I/O and better performance but it can increase the CPU time required to reconstruct the key column values during an index scan.

Reverse Key Indexes

Creating a reverse key index,compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.For example, keeping the index of mail messages in an e-mail application: some users keep old messages, and the index must maintain pointers to these as well as to the most recent.

The REVERSE keyword provides a simple mechanism for creating a reverse key index. You can specify the keyword REVERSE along with the optional index specifications in a CREATE INDEX statement:

CREATE INDEX i ON t (a,b,c) REVERSE;

Bitmap Indexes

The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a bitmap index,a bitmap for each key value is used instead of a list of rowids.Each bit in the bitmap corresponds to a possible rowid.

Benefits for Data Warehousing Applications

Bitmap indexing benefits data warehousing applications which have large amounts of data and and hoc queries by reducing the space required and the response time for large queries.

Cardinality

The advantages of using bitmap indexes are greatest for low cardinality columns i.e. columns in which the number of distinct values is small compared to the number of rows in the table. B-tree indexes are most effective for high-cardinality data: i.e data with many possible values, like CUSTOMER_NAME or PHONE_NUMBER.

Bitmap Join Indexes

A join index is an index on one table that involves columns of one or more different tables through a join. They are much more efficient in storage as they compress rowids of the fact tables.

Index-Organized Tables

An index-organized table has a storage organization that is a variant of a primary B-tree. It is really an index that has it's data in it. This in contrary to a normal table+index which stores its index keys in the index and looks up the data through the rowid. Data for an IOT is stored in a B-tree index structure. It is normally used in lookup tables, small tables that are not updated frequently but are being used to quickly lookup some data.

You can partition an IOT by RANGE or HASH on column values.

Index-Organized Table Applications

The superior query performance for primary key based access, high availability aspects, and reduced storage requirements make IOT’s ideal for the following kinds of applications:

·         Online Transaction Processing (OLTP)

·         Internet (for example, search engines and portals)

·         E-Commerce (for example, electronic stores and catalogs)

·         Data Warehousing.

·         Time-series applications

Local Partitioned Indexes

Local partitioned indexes are easier to manage than other types of partitioned indexes. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.A local index can be unique.

Global Partitioned Indexes

Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. Global prefixed indexes can be unique or nonunique.

·         Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.

·         Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key. 

·         Rowids are the fastest means of accessing particular rows.

·         Rowids can be used to see how a table is organized.

·         Rowids are unique identifiers for rows in a given table

As a DBA , you sometime have to enable and disable primary key . When you disable a primary key , primary key index will also be dropped and when you enable it, the entire index will again be created. Avoid using NOT when testing index as oracle would choose NOT to use index and would perform a full table scan.

Eg: the below statement would never use an index on student_num :

select * from student where student_num not like ‘9%’.

Primary reasons for partitioning :
MANAGEABILITY, AVAILABILITY and PERFORMANCE

There are three types of partitioned indexes:
a) Global Prefixed
b) Local Prefixed
c) Local Non-prefixed

Local Prefixed/Non-Prefixed index : Local indexes are indexes on partitioned tables where the index contains ROWID pointers that refer to rows in only one partition.

Prefixed index: means that the partition key is based on the left most columns in an index.
Non-Prefixed index: means that the partition key is based on something else than the left most columns in the index.
Global index : Global Indexes are partitioned either on the same key than the underlying table but different ranges either on a different key. Oracle only supports prefixed global   partitioned indexes.
An index is global prefixed if it is partitioned on the left prefix of the index columns.

An index can be used if :

·         it is referenced in a predicate. A predicate is each portion of selection criteria used to include or exclude rows from a result. Eg. the below where clause contains two predicates:

WHERE DNAME = ‘DEVELOPMENT’

AND SEX != ‘FEMALE’

·         the indexed column is not modified by a function or arithmetic operation

An Index will not be used if:

·         there is no where clasue.

·         the predicate modifies the indexed column in any way.

·         the search is explicitly for records with NULL or NOT NULL values in the indexed column( i.e. the predicate contains either IS NULL or IS NOT NULL).