Oracle Indexes

Indexes:

  • index is a database object that improves the performance of data retrieval operations by providing a faster way to access rows in a table.
  • Indexes are especially useful in speeding up queries that involve SELECT statements with WHERE clauses, joins, or sorting operations.
  • However, while indexes can improve read performance, they can slow down INSERT, UPDATE, and DELETE operations because the index must be updated whenever the data changes.

Index Storage:

Oracle Database stores index data in an index segment. The tablespace of an index segment is either the default tablespace of the owner or a tablespace specifically named in the CREATE INDEX statement. For ease of administration, you can store an index in a separate tablespace.

index block:

It  is a special type of data block that manages space differently from table blocks. Oracle Database uses index blocks to manage the logical storage space in an index.

Types of Index Blocks

An index contains a root block, branch blocks, and leaf blocks.

  1. Root block - This block identifies the entry point into the index.
  2. Branch blocks - The databases navigates through branch blocks when searching for an index key.
  3. Leaf blocks - These blocks contain the indexed key values rowids that point to the associated rows.

ROWID:

It is a Pseudo column of the table. ROWID returns the address of each row in the table. Oracle assigns a ROWID to each row.

AAAR/q        AAH    AAAADG    AAA

It Contains,

  • Object number (first 6 letters)
  • Data file number (next 3 letters)
  • Block number (next 6 letters)
  • Row Number (last 3 letters)

Index Types:

  1. B-Tree Indexes
  2. Bitmap Indexes
  3. Function-Based Indexes
  4. Application Domain Indexes
  5. Index-Organized Tables

B-Tree Index (Balanced Tree):

The most common type of index in Oracle. It organizes the data in a balanced tree structure, allowing efficient access to rows.

A B-tree index has two types of blocks: 

  • the branch block for searching, and the leaf block for storing key values. 
  • The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. 
  • The height of the index is the number of blocks required to go from the root block to a leaf block. 
  • The branch level is the height minus 1. The index has a height of 3 and a branch level of 2.

How to create b-tree index

CREATE INDEX index_name ON table_name(column_name);

Bitmap Indexes:

  • The database stores a bitmap for each index key. B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
  • It is created on columns that cardinality is very low. Just like gender column in a table which have only two distinct values "M" & "F".
  • Oracle Database uses a B-tree index structure to store bitmaps for each indexed key.

A bitmap index entry for this index has the following components:

  • The job title as the index key
  • A low rowid and high rowid for a range of rowids
  • A bitmap for specific rowids in the range

Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001

How to create bitmap index:

CREATE BITMAP INDEX index_name  ON table_name (column_name);

Function-Based Indexes:

  • A function-based index computes the value of a function or expression involving one or more columns and stores it in an index. 
  • A function-based index can be either a B-tree or a bitmap index. The indexed function can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL functions.
  • Function-based indexes are efficient for evaluating statements that contain functions in their WHERE clauses. The database only uses the function-based index when the function is included in a query.

How to Use:

CREATE INDEX emp_total_sal_idx   ON employees (12 * salary * commission_pct, salary, commission_pct);

SELECT employee_id, last_name, first_name, 12*salary*commission_pct AS "ANNUAL SAL" FROM employees WHERE (12 * salary * commission_pct) < 30000 ORDER BY "ANNUAL SAL" DESC;

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

SELECT * FROM   employees WHERE  UPPER(first_name) = 'MUTHU';

CREATE INDEX cust_valid_idx ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );

Application Domain Indexes:

Application Domain Indexes in Oracle are specialized indexes designed to improve performance for specific application requirements. These indexes can be created to support complex data types such as documents, spatial data, images, and video clips (Unstructured Data).

How to Use:

CREATE TABLE xml_documents (
doc_id NUMBER PRIMARY KEY,
doc_content XMLTYPE
);

CREATE INDEX xml_content_idx ON xml_documents(doc_content) INDEXTYPE IS XDB.XMLINDEX;

Index-Organized Table:

  • Index-Organized Table (IOT) in Oracle is a type of table that stores data in a B-tree index structure based on the table's primary key. 
  • index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index.
  • Data is ordered by the primary key, which allows for faster retrieval of rows when queried by the primary key.

Creating an Index-Organized Table:

CREATE TABLE employees_iot (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
) ORGANIZATION INDEX;

Creating a Heap-Organized Table:

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
);

Other types of indexes:

Unique / Nonunique Index

Unique indexes guarantee that no two rows of a table have duplicate values in the key column or columns. all values in the indexed column are unique and used in primary key or unique key columns.

Nonunique indexes permit duplicates values in the indexed column or columns.

Example:

CREATE UNIQUE INDEX emp_email_idx ON employees(email);

Reverse Key Index:

  • A reverse key index is a type of B-tree index that physically reverses the bytes of each index key. 
  • This reduces contention for index block updates in certain high-concurrency environments (RAC). 
  • It is suitable for high-volume insert operations where data is mostly sequential, like sequence-generated primary keys.

Example:

CREATE INDEX emp_rev_idx ON employees(id) REVERSE;

Composite Index:

Indexes multiple columns of a table. It can improve the performance of queries that filter on multiple columns.

Example:

CREATE INDEX emp_name_dob_idx ON employees(last_name, birth_date);

Create index with tablespace:

CREATE UNIQUE INDEX emp_email_idx ON employees(email) tablespace tbs1;

Move another tablespace:

alter index emp_email_idx rebuild tablespace tbs2;

select index_name, tablespace_name from user_indexes;

Rebuild and rebuild online the index:

Rebuild: rebuilding index can reclaim space that was previously occupied by deleted or updated rows. rebuild can improve the efficiency of index scans, making queries faster and index becomes unavailable during the rebuild.
rebuild online: rebuilding online index rebuild allows the index to be rebuilt without causing downtime. Then index remain available for DML (inserts, updates, deletes) and queries during the rebuild.

 How to index rebuild/rebuild online:

alter index index_name rebuild;

alter index index_name rebuild online;

SELECT sid, serial#, sofar, totalwork, elapsed_seconds FROM v$session_longops WHERE opname LIKE 'Index%';

Compressed index

  • rebuilding a compressed index involves recreating an existing index with compression, which helps to reduce storage space and improve performance in certain scenarios, especially when the index contains many repeating values. 
  • This feature is especially useful for large indexes in data warehousing or environments where storage savings are important.

ALTER INDEX sales_idx REBUILD COMPRESS 2;

SELECT index_name, compression, compress_prefix_length FROM user_indexes WHERE index_name = 'EMP_IDX';

Rename the index:

alter index old_index_name rename to new_index_name;

Drop the index:

drop index index_name;

Analyze the index:

analyze index index_name validate structure;

select index_name, to_char(last_analyzed, ‘DD-MON_YY HH:MI:SS’) from user_indexes where index_name=’INDEX_NAME’;

Index properties

Usability

  • An unusable index, which is ignored by the optimizer, is not maintained by DML operations. An unusable index can improve the performance of bulk loads. 
  • Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. 
  • Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.

ALTER INDEX emp_dept_idx UNUSABLE;

ALTER INDEX emp_dept_idx REBUILD;

Visibility

  • An invisible index is maintained by DML operations but is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. 
  • Invisible indexes are especially useful for testing the removal of an index before dropping it, without affecting the overall application.

ALTER INDEX emp_dept_idx INVISIBLE;

ALTER INDEX emp_dept_idx VISIBLE; 

SELECT index_name, status, visibility FROM user_indexes WHERE index_name = 'EMP_DEPT_IDX';

Monitoring of index:

Monitoring the performance and usage of indexes in Oracle is crucial for maintaining database efficiency and identifying whether indexes are being used effectively.

How to enable/disable the Monitoring:

ALTER INDEX emp_dept_idx MONITORING USAGE;

ALTER INDEX emp_dept_idx NOMONITORING USAGE;

Verify the Monitoring:

SELECT index_name, table_name, monitoring, used FROM v$object_usage WHERE index_name = 'EMP_DEPT_IDX';

Comments

Post a Comment