Oracle Database Indexing - Part 1

Oracle Database Indexing

We will be discussing Oracle indexes/types of indexes and all the option on how to create index in oracle with example in this post.

“An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access.”

An index:

·       Is a schema object

·       Can be used by the Oracle Server to speed up the retrieval of rows by using a pointer

·       Can reduce disk input/output (I/O) by using a rapid path access method to locate data quickly

·       Is dependent on the table that it indexes

·       Is used and maintained automatically by the Oracle Server

·       Indexes are used to search the rows in the table quickly.  If the index is not present the select query has to read the whole table and returns the rows. With Index, the rows can be retrieved quickly

·       We should create Indexes when selecting a small percentage of rows from a table (less than 2-4%). If the % of rows returned is high then index scan will be slow. It also depends on the data distribution i.e clustering factor

·       Indexes are logically and physically independent of the data in the associate table.

·       Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table.

·       Indexes are the primary means of reducing disk I/O when properly used.

·       The query decides at the beginning whether to use index or no

·       The best thing with indexes is that 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 must also update the indexes associated with the table.

·       If you are  owner of table, you can create index  or if you want to create index for table in another schema then you should either have  CREATE ANY INDEX system privilege  or index privilege on that table

Logical Type of Indexes

It defines the application characteristics of the Index

Unique or Non Unique	Index can be Unique or non Unique.  Oracle create unique index for Primary key and unique key constraints
Composite	The index can be comprised of single of multiple columns. Composite indexes can speed retrieval of data for SELECT statement in which the WHERE clause references all or the leading portion of the columns in the composite index.
Function Based indexes	The indexed column’s data is based on a calculation
Application Domain Indexes	This index used in special applications (Spatial, Text).

An Oracle Server index is a schema object that can speed up the retrieval of rows by using a

pointer and improves the performance of some queries. Indexes can be created explicitly or

automatically. If you do not have an index on the column, a full table scan occurs.

An index provides direct and fast access to rows in a table. Its purpose is to reduce the disk

I/O by using an indexed path to locate data quickly. An index is used and maintained

automatically by the Oracle Server. After an index is created, no direct activity is required by

the user.

Indexes are logically and physically independent of the data in the objects with which they are

associated. This means that they can be created or dropped at any time, and have no effect

on the base tables or other indexes.

Note: When we drop a table, the corresponding indexes are also dropped.

Types of indexes in oracle Database

Ø  B-Tree Indexes

·       Reverse Key Indexes

·       Composite B-Tree Indexes

·       Covering Indexes

·       Compressed Key Indexes

Ø  Bitmap Indexes

Ø  Function Based, Indexed Virtual

Ø  Index Organized Tables

Ø  Invisible Indexes

Ø  Virtual Indexes

Ø  Cluster Indexes

·       B-Tree Cluster Index

·       Hash Cluster Index

Ø  Partitioned Indexes

·       Global Partitioned

·       Local Partitioned

Ø  Oracle Domain indexes

How Are Indexes Created?

Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

Manually: We can create unique or non-unique index on columns to speed up access to the rows.

We can create two types of indexes.

• Unique index: The Oracle Server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE constraint. The name of the index is the name that is given to the constraint.

• Nonunique index: This is an index that a user can create. For example, you can create the FOREIGN KEY column index for a join in a query to improve the speed of retrieval.

Note: We can manually create a unique index, but it is recommended that you create a unique constraint, which implicitly creates a unique index.

Index Creation Guidelines

Ø  Create an Index when

·       A column contains a wide range of values

·       A column contains a large number of null values

·       One or more columns are frequently used together in a WHERE clause or a join condition

·       The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

Ø  Do not create an index when

·       The columns are not often used as a condition in the query

·       The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table

·       The table is updated frequently

·       The indexed columns are referenced as part of an expression index when:

Note: There are other types of indexes as well, such as Oracle TEXT for indexing CLOB or other large text data, and Oracle Spatial.

B-Tree Indexes

• Most common type of index

• Data is ordered within the index

• Consists of branches and leaves

Reverse Key Indexes (RKI)

• Creates a “mirror image” of the key

• UTOUG would become GUOTU

• Used to spread block splits and avoid hot blocks in RAC environments

• No index range scans

• Lots of conflicting information

• Test extensively, and use with caution

Composite B-Tree Indexes

• Sometimes known as covering or concatenated

• Consist of more than one column

• Leading column is important

Covering Indexes

• The Poor-Man’s IOT

• Use to improve performance of select by reducing I/O

Compressed Key Indexes

• Leading columns have low cardinality

• Duplicate occurrences of the same value are eliminated

• Save space

• Improve performance

• A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it

Bitmap Indexes

• Typically found in data warehouse environments

• Index on low cardinality data (few distinct values)

• Bitmap Indexes work on columns with low data cardinality (as opposed to B-Tree indexes)

• Take up little space

• Bitmap join

• Oracle stores a bitmap for each distinct value in the index with 1 bit for each row in the table

• Bitmaps are expensive to maintain and are therefore not suitable for applications which make     a lot of writes to the data as for example an OLTP system would

• They are not meant for transactional or high update databases

• The Bitmap index is useful on large columns with low-DML activity like marital status (M/S) or gender (M/F)

Function-Based Indexes

• A function-based index is based on expressions

• Index on a database function (predefined, user written)

• Allows index lookups when a function is used

• Both store the derived value in the index

• The index expression is built from table columns, constants, SQL functions, and user-defined functions.

• The function must be deterministic (always return the same value for the same inputs)

Index Organized Tables (IOT)

• An index-organized table differs from an ordinary table because the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result in updating the index

• Imagine you take your concatenated index, and extend it to all the columns in the table.  Then you can remove the table itself.  That's what an index organized table is

• All the data is stored in the Oracle database in a B*tree index structure defined on the table's primary key

• This is ideal when related pieces of data must be stored together or data must be physically stored in a specific order

• Index-organized tables are often used for information retrieval, spatial and OLAP applications

• The index-organized table is like an ordinary table with an index on one or more of its columns, but instead of maintaining two separate storages for the table and the B-tree index, the database system maintains only a single B-tree index which contains both the encoded key value and the associated column values for the corresponding row

Invisible Indexes

• They are created as a normal index, but invisible to the cost based optimizer

• This can allow us to test out performance of large queries, without impacting the live running application

• Create or modify an index to be invisible

• Invisible to the optimizer

• Still maintained by the database

• Better, more reliable option than MONITORING USAGE

• Must set optimizer_use_invisible_indexes=TRUE

Virtual (no segment) Indexes

• They allow us to test new indexes and their effect on query plans, without actually building them

• Only visible to the optimizer

• Used for evaluating an indexes usefulness

B-Tree Cluster Index

• B*tree index defined for clusters

• Clusters are two or more tables with one or more common columns and are usually accessed together (via a join)

• Take two tables with one column in common, and that column has a cluster index on it

Hash Cluster Index

• Hash clusters can exist on a single table

• In a hash cluster rows that have the same hash key value (generated by a hash function) are stored together in the Oracle database

• Hash clusters are equivalent to indexed clusters, except the index key is replaced with a hash function

• No separate index as the hash is the index

Partitioned Indexes

• A whole world of new index types opens up to us, from ones that index across all the partitions (global) to ones that are focused on each partition individually (local). 

• Partitioned Indexes are also useful in Oracle data warehouse applications where there is a large amount of data that is partitioned by a particular dimension such as time

• Partition indexes can either be created as local partitioned indexes or global partitioned indexes

Global Partitioned

 • The partitioning is user defined and is not the same as the underlying table

• Crosses partitions

• Exists on whole table

• Can exist on non-partitioned tables

• Can be either range or hash based

• Partition maintenance can render the index unusable

• Global indexes on partitioned tables must lead with the partition key

Local Partitioned

• Local partitioned indexes means that the index is partitioned on the same columns and with the same number of partitions as the table

• Unique to each partition

• Isolate maintenance operations to a single partition

• Mark unusable/invisible independently

• Separate partitions into different tablespaces

• Prefixed, non-prefixed

• Unique indexes must include partition key

• Can only exist on partitioned tables

PARTITIONED INDEXES

Operation	Local partition	Global or non-partition
Split	Partition index unusable	Entire index unusable
Move	Partition index unusable	Entire index unusable
Merge	Partitions involved unusable	Entire index unusable
Exchange	Partition index unusable	Entire index unusable
Truncate	No effect on index	Entire index unusable
Drop	No effect on index	Entire index unusable

Oracle Domain indexes

• These indexes are created by user-defined indexing routines and enable the user to define his or her own indexes on custom data types (domains) such as pictures, maps or fingerprints for example

• These type of index require in-depth knowledge about the data and how it will be accessed.

Index Information

USER_INDEXES provides information about our indexes.

USER_IND_COLUMNS describes columns of indexes owned by us and columns of indexes on our tables.

DESCRIBE user_indexes

SELECT index_name, table_name, uniqueness FROM user_indexes

WHERE table_name = 'EMPLOYEES';

SELECT table_name, index_name, column_name FROM all_ind_columns WHERE table_name like upper(‘&Table_Name’) ORDER BY table_name, index_name,column_position;

 Querying USER_IND_COLUMNS

DESCRIBE user_ind_columns

SELECT index_name, column_name,table_name

FROM user_ind_columns

WHERE index_name = 'LNAME_IDX';

Determine index size

            Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’INDEX_NAME’ group by segment_name;

OR

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’INDEX_NAME’ group by owner,segment_name;

List of Size of all INDEXES of a USER

select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_type=’INDEX’ group by segment_name order by “SIZE in GB” desc;

OR

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_type=’INDEX’ group by owner,segment_name order by “SIZE in GB” desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_type=’INDEX’ group by owner;

Removing/Drop an Index

Remove an index from the data dictionary by using the

DROP INDEX command:

Remove the emp_last_name_idx index from the data

dictionary:

DROP INDEX emp_last_name_idx;

To drop an index, you must be the owner of the index or

have the DROP ANY INDEX privilege.

Note: We cannot modify indexes. To change an index, we must drop it and then re-create it.

Remove an index definition from the data dictionary by issuing the DROP INDEX statement. To

Drop an index, we must be the owner of the index or have the DROP ANY INDEX privilege.

In the syntax, index is the name of the index.

We can drop an index using the ONLINE keyword.

DROP INDEX emp_indx ONLINE;

ONLINE: Specify ONLINE to indicate that DML operations on the table are allowed while

dropping the index.

Note: If we drop a table, indexes and constraints are automatically dropped but views

remain.

WHAT TO INDEX

• Primary keys

• Unique keys

• Foreign keys

• Columns frequently used in where, distinct, and order by clauses

• Columns often queried together

Note: Index all that should be, and no more, If in doubt, b-tree is probably safest.

Recreate the Indexes/rebuild index in oracle

We can Use the ALTER INDEX … REBUILD statement to reorganize or compact an existing index or to change its storage characteristics

The REBUILD statement uses the existing index as the basis for the new one.

ALTER INDEX … REBUILD is usually faster than dropping and re-creating an index. 

It reads all the index blocks using multiblock I/O then discards the branch blocks.

A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

  Alter index  <index name>  rebuild ;

  Alter index  <index name>  rebuild  tablespace <name>;

Create or rebuild the index Online

Oracle used to lock the table on which index is being created throughout creation process  in older vesions. This makes table unavailable for data manipulation during the creation of index.

Now with 8i, Oracle introduced online rebuilding of index where Oracle doesn’t lock the table on which index is being built.

Online indexing is provided through the keyword ONLINE.

CREATE <UNIQUE|NON UNIQUE>  INDEX <index_name>

ON <table_name> (<column_name>,<column_name>…)

PCTFREE <integer>

TABLESPACE <tablespace_name>

Online;

Alter index <index name> rebuild online;

Basically with online rebuild,Oracle locks the table at the start and end of the creation of the index. It allows transaction in between. The mechanism has been quite improved with 11g and 12c.

Unusable indexes

An unusable index is ignored by the optimizer in deciding the explain plan It is  also not maintained by DML i.e update,insert, delete does the update the index. There could be several for index in unusable state.  You did the rebuild of the table but did not rebuild the index, then index will be unusable state.

One other reason to make an index unusable is to improve bulk load performance. Another reason might be optimizer picking up the wrong index every time and time is critical so we may decide to make it unusable An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used. Truncating a table makes an unusable index valid.

Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped.

The functionality of unusable indexes depends on the setting of the SKIP_UNUSABLE_INDEXES initialization parameter.

When SKIP_UNUSABLE_INDEXES is TRUE (the default), then:

DML statements against the table proceed, but unusable indexes are not maintained.

DML statements terminate with an error if there are any unusable indexes that are used to enforce the UNIQUE constraint.

For nonpartitioned indexes, the optimizer does not consider any unusable indexes when creating an access plan for SELECT statements. The only exception is when an index is explicitly specified with the INDEX() hint.

 When SKIP_UNUSABLE_INDEXES is FALSE, then:

If any unusable indexes or index partitions are present, any DML statements that would cause those indexes or index partitions to be updated are terminated with an error.

For SELECT statements, if an unusable index or unusable index partition is present but the optimizer does not choose to use it for the access plan, the statement proceeds. However, if the optimizer does choose to use the unusable index or unusable index partition, the statement terminates with an error.

Invisible Indexes

Beginning with Oracle Database 11g Release 1, we can create invisible indexes or make an existing index invisible. An invisible index is ignored by the optimizer unless we explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

Unlike unusable indexes, an invisible index is maintained during DML statements. Although we can make a partitioned index invisible, we cannot make an individual index partition invisible while leaving the other partitions visible. Using invisible indexes, we can do the following: 

• Test the removal of an index before dropping it.
• Use temporary index structures for certain operations or modules of an application without affecting the overall application.

 Write Statements that Avoid Using Indexes

• We can use the NO_INDEX hint to give the CBO maximum flexibility while disallowing the use of a certain index.
• We can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan.
• We can use the INDEX, INDEX_COMBINE, or AND_EQUAL hints to force the optimizer to use one index or a set of listed indexes instead of another.

How does Oracle decide about the usage of index

Ø  Oracle automatically decides about whether index should be used by Optimizer engine.

Ø  Oracle decides whether to use an index or not depending upon the query.

Ø  Oracle can understand whether using an index will improve the performance in the given query.

Ø  If Oracle thinks using an index will improve performance, it will use the index otherwise it will ignore the index.

Let us understand by this example

We have a table emp which contains emp_name, salary,dept_no ,emp_no,date_of_joining and we have an index on emp_name

     Query 1

select * from emp  where emp_name = ‘John’;

The above query will use the index as we are trying to get information about a emp based on the name.

 Query 2

select * from emp;

The above query will not use index as we are trying to find all the rows in the table and we don’t have where clause in the query

Query 3

select * from emp where dept_no =5;

The above query will not use index as the where clause does not select the column which has index

 Query 4

select * from emp where substr(emp_name,1,4) =’XYZW’;

The above query will not use index as the where clause uses the function on the column and we don’t have functional index on emp_name

What are the Drawbacks of the Indexes

Ø  Indexes increase performance of select query, they can also decrease performance of data manipulation.

Ø  Many indexes on a table can slow down INSERTS and DELETES drastically.

Ø  The more the indexes on the table, the more time inserts and delete will take.

Ø  Similarly every change to an indexed column will need a change to index.

Ø  So we need to choose the index very carefully and drop which are not in use.

Ø  Though the extra space occupied by indexes is also a consideration, it may not matter much since the cost of data storage has declined substantially.

KEY CONSIDERATIONS

Create primary and unique keys within a create table or build the indexes and constraints separately.

The create table method is easier, but:

• Indexes don’t persist

• May break Golden Gate, replication

NAMING CONVENTION

• Pick a convention and stick to it!

•tablename_p

•tablename_un

•tablename_in

•tablename_fn

•tablename_bn

• ...etc

STORAGE

• Consider separating table and index tablespaces

• Specify suitable storage parameters

• PCTFREE is meaningless in indexes

•logging/nologging

• Extent and block size can be defined

• Manage backups

• Manage physical storage

MAINTENANCE

• Index reorganization options

• Alter index rebuild

• Alter index coalesce

• Alter index shrink space (compact)

Generate or gather statistics for Indexes

Index statistics are gathered using the ANALYZE INDEX or dbms_stats statement. 

Available options are COMPUTE/ESTIMATE STATISTICS or VALIDATE STRUCTURE.

With 10g onwards, when the index is created, compute statistics is done automatically

When using the validate structure, Oracle populates the INDEX_STATS view with statistics related to analyzed index.

The statistics contain number of leaf rows & blocks (LF_ROWS, LF_BLKS), number branch rows & blocks (BR_ROWS, BR_BLKS), number of deleted leaf rows (DEL_LF_ROWS), used space (USED_SPACE), number of distinct keys (DISTINCT_KEYS) etc. These statistics can be used to determine if the index should be rebuild or not

• Use DBMS_STATS

• Defaults are usually best:

exec dbms_stats.set_global_prefs(‘METHOD_OPT’, ‘FOR ALL COLUMNS SIZE AUTO’);

exec dbms_stats.reset_global_pref_defaults;

•CASCADE=TRUE

EXTENDED STATISTICS

• Introduced in 11g

• Allows you to create column groups

• Determines a relationship among potentially skewed data

dbms_stats.create_extended_stats(‘APP’, ‘CUSTOMERS’, ‘(BIRTHDATE, BIRTHSTONE)’);

Data dictionary views on Indexes

DBA_INDEXES ALL_INDEXES USER_INDEXES	DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_COLUMNS ALL_IND_COLUMNS USER_IND_COLUMNS	These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_IND_EXPRESSIONS ALL_IND_EXPRESSIONS USER_IND_EXPRESSIONS	These views describe the expressions of function-based indexes on tables.
DBA_IND_STATISTICS ALL_IND_STATISTICS USER_IND_STATISTICS	These views contain optimizer statistics for indexes.

  

I hope you all have enjoyed reading this article. Comments are welcome.... 

Related Posts:

-Oracle Database Indexing - Part 2