Exploring Indexes Concepts in Oracle

About Indexes

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 Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.

Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries. Otherwise, they just take up space and add overhead when the indexed columns are updated. You should use the EXPLAIN PLAN feature to determine how the indexes are being used in your queries. Sometimes, if an index is not being used by default, you can use a query hint so that the index is used.

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. The database 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.

The absence or presence of an index does not require a change in the wording of any SQL statement. 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.

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

If you create index on a column combination which has already been indexed, you will get following errors:

SQL> create index emp_pkk on emp(empno);

create index emp_pkk on emp(empno)

                            *

ERROR at line 1:

ORA-01408: such column list already indexed

Oracle Database automatically maintains and uses indexes after they are created. Oracle Database automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle Database must also update the indexes associated with the table.

The optimizer can use an existing index to build another index. This results in a much faster index build.

Classification of Indexes:

On bases of Logical design Indexes are classified as:

■ Unique and Nonunique Indexes

■ Visible and Invisible Indexes

■  Single Column or Composite Indexes

■ Function-Based Indexes

On bases of Physical Implementation Indexes are classified as:

■ B-Tree: Normal or Reverse Key

■ Bitmap Indexes

■ Bitmap Join Indexes
■ Partition or Non Partitioned

Single Column or Composite Indexes

Index could be created on a single column or on multiple columns. 

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.

Composite indexes 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. Therefore, the order of the columns used in the definition is important.

Generally, the most commonly accessed or most selective columns go first.

No more than 32 columns can form a regular composite index. For a bitmap index, the maximum number columns is 30. A key value cannot exceed roughly half (minus some overhead) the available data space in a data block.

Unique and Non-unique 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. 

Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index:

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
      TABLESPACE indx;

Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.

Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

You cannot create Unique index on column which has duplicate records:

Table: EMP
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
      7566 JONES      MANAGER         7839 02-APR-81       2975
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
      7788 SCOTT      ANALYST         7566 19-APR-87       3000
      7839 KING       PRESIDENT            17-NOV-81       5000
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
      7876 ADAMS      CLERK           7788 23-MAY-87       1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950
      7902 FORD       ANALYST         7566 03-DEC-81       3000
      7934 MILLER     CLERK           7782 23-JAN-82       1300

14 rows selected.


SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO);
CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO)
                               *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

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. This does not apply if there are no non-NULL values—in other words, if the rows are entirely NULL. Oracle Database does not index table rows in which all key columns are NULL, except in the case of bitmap indexes or when the cluster key column value is NULL.

Unique Indexes doesn't identify NULL values. So even if a column has all or few NULL values we are still able to create Unique index on it.

SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(comm);
Index created.
SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(deptno);
Index created.

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. You can create a function-based index as either a B-tree or a bitmap index.

The expression cannot contain any aggregate functions, and it must be DETERMINISTIC. For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a LOB column, REF, or nested table column, nor can you build a function-based index if the object type contains a LOB, REF, or nested table.

Uses of Function-Based Indexes
Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. The value of the expression is computed and
stored in the index. When it processes INSERT and UPDATE statements, however, Oracle Database must still evaluate the function to process the statement.

For example, if you create the following index:
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
Oracle Database 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. For example, 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';

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 regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle Database stores each key value repeatedly with each stored rowid. 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. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient.

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

• The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
• The indexed table is either read-only or not subject to significant modification by DML statements.

Bitmap Indexes on a Single Table

Example 2 Query of customers Table

SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender
  2  FROM   sh.customers 
  3  WHERE  ROWNUM < 8 ORDER BY cust_id;
 
   CUST_ID CUST_LAST_ CUST_MAR C
---------- ---------- -------- -
         1 Kessel              M
         2 Koch                F
         3 Emmerson            M
         4 Hardy               M
         5 Gowen               M
         6 Charles    single   F
         7 Ingram     single   F
 
7 rows selected.

The cust_marital_status and cust_gender columns have low cardinality, whereas cust_id and cust_last_name do not. Thus, bitmap indexes may be appropriate on cust_marital_status and cust_gender. A bitmap index is probably not useful for the other columns. Instead, a unique B-tree index on these columns would likely provide the most efficient representation and retrieval.

Table 1-2 illustrates the bitmap index for the cust_gender column output shown in Example 2. It consists of two separate bitmaps, one for each gender.

Table 1-2 Sample Bitmap

Value	Row 1	Row 2	Row 3	Row 4	Row 5	Row 6	Row 7
M	1	0	1	1	1	0	0
F	0	1	0	0	0	1	1

A mapping function converts each bit in the bitmap to a rowid of the customers table. Each bit value depends on the values of the corresponding row in the table. For example, the bitmap for the M value contains a 1 as its first bit because the gender is M in the first row of the customers table. The bitmapcust_gender='M' has a 0 for its the bits in rows 2, 6, and 7 because these rows do not contain M as their value.

DBA_INDEX, ALL_INDEXES, USER_INDEXES Data Dictionary Tables:

We use these data dictionary tables to know about created indexes:

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the index
INDEX_NAME	VARCHAR2(30)	NOT NULL	Name of the index
INDEX_TYPE	VARCHAR2(27)		Type of the index: NORMAL BITMAP FUNCTION-BASED NORMAL FUNCTION-BASED BITMAP DOMAIN
TABLE_OWNER	VARCHAR2(30)	NOT NULL	Owner of the indexed object
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the indexed object
TABLE_TYPE	CHAR(5)		Type of the indexed object (for example, TABLE, CLUSTER)
UNIQUENESS	VARCHAR2(9)		Indicates whether the index is UNIQUE or NONUNIQUE
COMPRESSION	VARCHAR2(8)		Indicates whether index compression is enabled (ENABLED) or not (DISABLED)