Oracle Reverse Key Index FAQs with examples

1. What is a Reverse Key Index in Oracle?

A Reverse Key Index is a B-Tree index that stores the bytes of the indexed values in reverse order.

It helps reduce index block contention when many users insert sequential values, such as sequence-generated IDs, at the same time.

Example:

CREATE INDEX idx_emp_rev
ON employees(employee_id)
REVERSE;

2. Why do we use a Reverse Key Index?

Reverse Key Indexes are used to:

• Reduce index block contention
• Improve concurrent INSERT performance
• Distribute index entries across multiple index blocks
• Improve scalability in high-concurrency OLTP systems

3. How does a Reverse Key Index work?

Suppose employee IDs are inserted in sequence:

1001
1002
1003
1004

Normally, Oracle inserts them into the same end of the index, which can create contention. With a Reverse Key Index, Oracle internally stores them in reverse-byte order, helping spread inserts across different index blocks.

4. How do you create a Reverse Key Index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name)
REVERSE;

Example:

CREATE INDEX idx_empid_rev
ON employees(employee_id)
REVERSE;

5. When should you use a Reverse Key Index?

Use a Reverse Key Index when:

• The indexed column uses a sequence
• Many users insert data simultaneously
• The application experiences index hot spots
• The system is an OLTP application with heavy INSERT activity

Example:

INSERT INTO employees
VALUES (emp_seq.NEXTVAL, 'John', 50000);

If thousands of users insert rows concurrently, a Reverse Key Index can reduce contention.

6. When should you avoid a Reverse Key Index?

Avoid Reverse Key Indexes when queries frequently perform:

• Range searches
• BETWEEN operations
• Greater than (>)
• Less than (<)
• ORDER BY using the indexed column

These operations are generally not efficient because the key values are stored in reverse order.

7. Difference between a Normal Index and a Reverse Key Index

Normal Index	Reverse Key Index
Stores keys in normal order	Stores keys in reverse-byte order
Supports range scans	Does not efficiently support range scans
Suitable for general queries	Suitable for high-concurrency inserts
Default index type	Special B-Tree index option

8. Can a Reverse Key Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_emp_rev
ON employees(employee_id)
REVERSE;

This enforces uniqueness while also reducing insert contention.

9. Can a Reverse Key Index be Composite?

Yes.

Example:

CREATE INDEX idx_emp_rev
ON employees(employee_id, department_id)
REVERSE;

The composite key is stored using reverse-key indexing.

10. Can Oracle perform Range Scans on a Reverse Key Index?

Generally, no.

Example:

SELECT *
FROM employees
WHERE employee_id BETWEEN 1000 AND 2000;

Oracle usually performs a Full Table Scan or uses another suitable index because Reverse Key Indexes are not designed for efficient range scans.

11. How do you view Reverse Key Indexes?

SELECT index_name,
       index_type
FROM user_indexes;

The INDEX_TYPE column indicates the index type. You can also review the index DDL using DBMS_METADATA.GET_DDL to confirm that it was created with the REVERSE option.

12. How do you drop a Reverse Key Index?

DROP INDEX idx_emp_rev;

13. What are the advantages of a Reverse Key Index?

• Reduces index block contention
• Improves concurrent INSERT performance
• Better scalability for sequence-generated keys
• Useful in high-volume OLTP applications

14. What are the disadvantages of a Reverse Key Index?

• Does not efficiently support range scans
• Not suitable for BETWEEN, <, or > searches
• Less suitable for queries requiring ordered index access

15. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  first_name  VARCHAR2(50)
);

Create a Reverse Key Index:

CREATE INDEX idx_emp_rev
ON employees(employee_id)
REVERSE;

Insert data:

INSERT INTO employees
VALUES (1001, 'John');

INSERT INTO employees
VALUES (1002, 'David');

INSERT INTO employees
VALUES (1003, 'Scott');

The Reverse Key Index helps distribute these sequential key inserts across the index, reducing contention.

16. Real-World Example

Suppose an online banking system generates transaction IDs using a sequence.

INSERT INTO transactions
VALUES (txn_seq.NEXTVAL, 5000, SYSDATE);

Create the index:

CREATE INDEX idx_txn_rev
ON transactions(transaction_id)
REVERSE;

Thousands of users can insert new transactions simultaneously with less index block contention than a normal B-Tree index.

17. Can Oracle use a Reverse Key Index for Equality Searches?

Yes.

Example:

SELECT *
FROM employees
WHERE employee_id = 105;

Oracle can efficiently use a Reverse Key Index for equality (=) searches.

18. How do you check if Oracle is using a Reverse Key Index?

Use an execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE employee_id = 105;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the execution plan shows an index access path, Oracle is using the Reverse Key Index.

Key Point:
Reverse Key Indexes are best for high-concurrency inserts with sequential keys, but they are not suitable for range queries.

Oracle Function-Based Index FAQs with examples

1. What is a Function-Based Index in Oracle?

A Function-Based Index is an index created on the result of a function or expression instead of directly on a column.

It improves the performance of queries that use functions in the WHERE clause.

Example:

CREATE INDEX idx_upper_name
ON employees(UPPER(last_name));

2. Why do we use a Function-Based Index?

Function-Based Indexes are used to:

• Improve queries that use functions
• Avoid Full Table Scans
• Speed up case-insensitive searches
• Improve query performance

3. When should you use a Function-Based Index?

Use a Function-Based Index when queries frequently use functions like:

• UPPER()
• LOWER()
• TRUNC()
• NVL()
• SUBSTR()
• ROUND()

Example:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'SMITH';

Without a Function-Based Index, Oracle may perform a Full Table Scan.

4. How do you create a Function-Based Index?

Syntax:

CREATE INDEX index_name
ON table_name(function(column_name));

Example:

CREATE INDEX idx_upper_lastname
ON employees(UPPER(last_name));

5. How does a Function-Based Index work?

Create the index:

CREATE INDEX idx_upper_name
ON employees(UPPER(last_name));

Query:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'SMITH';

Oracle can use the Function-Based Index instead of scanning every row.

6. Can we use built-in functions?

Yes.

Examples:

• UPPER()
• LOWER()
• TRUNC()
• NVL()
• SUBSTR()
• ROUND()
• ABS()

Example:

CREATE INDEX idx_lower_email
ON employees(LOWER(email));

7. Can we use expressions?

Yes.

Example:

CREATE INDEX idx_total_salary
ON employees(salary + commission_pct);

Oracle indexes the result of the expression.

8. Can we create a Function-Based Index on multiple columns?

Yes.

Example:

CREATE INDEX idx_fullname
ON employees(UPPER(first_name || ' ' || last_name));

Query:

SELECT *
FROM employees
WHERE UPPER(first_name || ' ' || last_name) = 'JOHN SMITH';

9. What is a common use of a Function-Based Index?

Case-insensitive searches.

Example without index:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'KING';

Create index:

CREATE INDEX idx_upper_lastname
ON employees(UPPER(last_name));

Now Oracle can use the index.

10. What happens if we use a normal index with a function?

Suppose a normal index exists:

CREATE INDEX idx_lastname
ON employees(last_name);

Query:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'KING';

Oracle usually cannot use the normal index efficiently because the query applies a function to the indexed column.

11. How do you view Function-Based Indexes?

SELECT index_name,
       index_type
FROM user_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL';

To view the indexed expression:

SELECT index_name,
       column_expression
FROM user_ind_expressions;

12. How do you drop a Function-Based Index?

DROP INDEX idx_upper_lastname;

13. What are the advantages of Function-Based Indexes?

• Faster searches using functions
• Improves case-insensitive queries
• Reduces Full Table Scans
• Improves query performance
• Supports indexing expressions

14. What are the disadvantages of Function-Based Indexes?

• Use additional storage
• Slow INSERT, UPDATE, and DELETE operations because the index must be maintained
• Queries must use the same function or compatible expression for Oracle to use the index effectively

15. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  last_name   VARCHAR2(50)
);

Create the index:

CREATE INDEX idx_upper_name
ON employees(UPPER(last_name));

Run the query:

SELECT *
FROM employees
WHERE UPPER(last_name) = 'SMITH';

Oracle can use the Function-Based Index to retrieve matching rows efficiently.

16. Real-World Example

Suppose users can search employee emails without worrying about letter case.

Query:

SELECT *
FROM employees
WHERE LOWER(email) = 'john@gmail.com';

Create the index:

CREATE INDEX idx_lower_email
ON employees(LOWER(email));

This improves the performance of case-insensitive email searches.

17. Can a Function-Based Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_unique_upper_email
ON employees(UPPER(email));

This ensures that email addresses are unique regardless of case.

Example: John@ABC.com and john@abc.com are treated as duplicates.

18. How do you check if Oracle is using a Function-Based Index?

Use an execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE UPPER(last_name) = 'SMITH';

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the execution plan shows an index access path such as INDEX RANGE SCAN, Oracle is using the Function-Based Index.

Key Point:
Function-Based Indexes are ideal when queries apply functions to columns, especially for case-insensitive searches and expression-based filtering.

Oracle Unique Index FAQs with examples

1. What is a Unique Index in Oracle?

A Unique Index ensures that all non-NULL values in the indexed column, or combination of columns, are unique. It prevents duplicate indexed values from being inserted.

Example:

CREATE UNIQUE INDEX idx_emp_email
ON employees(email);

2. Why do we use a Unique Index?

Unique indexes are used to:

• Prevent duplicate values
• Enforce uniqueness
• Improve query performance
• Support PRIMARY KEY and UNIQUE constraints

3. How do you create a Unique Index?

Syntax:

CREATE UNIQUE INDEX index_name
ON table_name(column_name);

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

4. What happens if duplicate values are inserted?

Oracle returns an error and does not allow the duplicate value.

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

INSERT INTO employees
VALUES (101, 'John', 'john@gmail.com');

INSERT INTO employees
VALUES (102, 'David', 'john@gmail.com');

Output:

ORA-00001: unique constraint violated

The second INSERT fails because john@gmail.com already exists.

5. Can a Unique Index contain NULL values?

Yes. A single-column Oracle B-Tree unique index can allow multiple NULL values. Oracle does not store an index entry when all indexed columns are NULL.

Example:

CREATE UNIQUE INDEX idx_phone
ON employees(phone_number);

These inserts are allowed:

INSERT INTO employees(employee_id, phone_number)
VALUES (1, NULL);

INSERT INTO employees(employee_id, phone_number)
VALUES (2, NULL);

Important:
A Unique Index and a PRIMARY KEY are different. A PRIMARY KEY does not allow NULL values, while a standalone unique index can allow NULLs.

6. Can a Unique Index be created on multiple columns?

Yes. This is called a Composite Unique Index.

CREATE UNIQUE INDEX idx_email_dept
ON employees(email, department_id);

The combination of email and department_id must be unique for non-NULL indexed values.

7. What is the difference between a Unique Index and a Normal Index?

```

Unique Index	Normal Index
Prevents duplicate indexed values	Allows duplicate values
Enforces uniqueness	Does not enforce uniqueness
Can support unique constraints	Used mainly for faster data access
Can improve query performance	Can improve query performance

8. What is the difference between a Primary Key and a Unique Index?

```

Primary Key	Unique Index
Does not allow NULL values	Can allow NULL values
Only one primary key per table	Multiple unique indexes are allowed
A database constraint	A database index
Enforces entity integrity	Enforces uniqueness of indexed values

9. Does a Unique Constraint create a Unique Index?

Oracle normally creates a unique index to enforce a UNIQUE constraint if a suitable index does not already exist.

Example:

ALTER TABLE employees
ADD CONSTRAINT uk_email
UNIQUE(email);

10. How do you view Unique Indexes?

SELECT index_name,
       uniqueness,
       table_name
FROM user_indexes
WHERE uniqueness = 'UNIQUE';

11. How do you drop a Unique Index?

DROP INDEX idx_email;

Note:
If the index is being used to enforce a PRIMARY KEY or UNIQUE constraint, normally manage or drop the constraint rather than attempting to drop its supporting index directly.

12. Can one table have multiple Unique Indexes?

Yes.

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

CREATE UNIQUE INDEX idx_aadhar
ON employees(aadhar_number);

CREATE UNIQUE INDEX idx_pan
ON employees(pan_number);

Each unique index independently enforces uniqueness for its indexed values.

13. What are the advantages of a Unique Index?

• Prevents duplicate data
• Improves query performance
• Helps enforce business rules
• Helps maintain data integrity
• Speeds up searches on unique columns

14. What are the disadvantages of a Unique Index?

• Uses additional storage space
• Adds overhead to INSERT operations
• Adds overhead to UPDATE operations on indexed columns
• Requires index maintenance during DML operations

15. Unique Index Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  email       VARCHAR2(100)
);

Create a Unique Index:

CREATE UNIQUE INDEX idx_email
ON employees(email);

Insert the first row:

INSERT INTO employees
VALUES (1, 'john@gmail.com');

Try to insert the same email again:

INSERT INTO employees
VALUES (2, 'john@gmail.com');

Output:

ORA-00001: unique constraint violated

The second row is rejected because the email value already exists in the unique index.

16. Real-World Example

Suppose every employee must have a unique company email.

Create the index:

CREATE UNIQUE INDEX idx_company_email
ON employees(company_email);

Now Oracle prevents duplicate non-NULL company email addresses, ensuring that the same email cannot be assigned to multiple employees.

Key Point:
A Unique Index combines fast indexed access with uniqueness enforcement. It is useful for columns such as email addresses, employee identifiers, account numbers, and other values that should not be duplicated.

Oracle Composite Index FAQs with examples

1. What is a Composite Index in Oracle?

A Composite Index (also called a Concatenated Index) is an index created on two or more columns of a table.

It helps Oracle retrieve data faster when queries use multiple indexed columns together.

Example:

CREATE INDEX idx_emp_name_dept
ON employees(last_name, department_id);

2. Why do we use a Composite Index?

Composite indexes are used to:

• Speed up searches using multiple columns
• Improve JOIN performance
• Improve sorting (ORDER BY)
• Reduce Full Table Scans
• Improve query performance

3. How do you create a Composite Index?

Syntax:

CREATE INDEX index_name
ON table_name(column1, column2, ...);

Example:

CREATE INDEX idx_emp_job_dept
ON employees(job_id, department_id);

4. When will Oracle use a Composite Index?

Oracle uses the index when the query searches using the leading column(s) of the index.

Example index:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Query that uses the index:

SELECT *
FROM employees
WHERE last_name = 'Smith';

Or:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND department_id = 10;

5. What is the Leading Column Rule?

Oracle can efficiently use a composite index only when the query starts with the first (leading) column of the index.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Uses the index:

SELECT *
FROM employees
WHERE last_name = 'King';

Uses the index:

SELECT *
FROM employees
WHERE last_name = 'King'
AND department_id = 20;

May not use the index efficiently:

SELECT *
FROM employees
WHERE department_id = 20;

Because the leading column (last_name) is not included in the search condition.

6. Can a Composite Index contain more than two columns?

Yes.

Example:

CREATE INDEX idx_emp_full
ON employees(last_name,
first_name,
department_id);

This index contains three columns.

7. Difference between Single-Column Index and Composite Index

Single-Column Index	Composite Index
One column	Two or more columns
Faster for one-column searches	Faster for multi-column searches
Simpler	More efficient for related search conditions

8. Does the order of columns matter?

Yes.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

Better query:

SELECT *
FROM employees
WHERE last_name = 'Scott'
AND department_id = 30;

Less efficient:

SELECT *
FROM employees
WHERE department_id = 30
AND last_name = 'Scott';

Note: The order of conditions in the WHERE clause does not matter. Oracle's optimizer can reorder predicates. What matters is that the query includes the leading indexed column (last_name).

9. Can Oracle use only the first column of a Composite Index?

Yes.

Example:

CREATE INDEX idx_emp
ON employees(last_name, department_id);

This query can use the index:

SELECT *
FROM employees
WHERE last_name = 'Jones';

10. Can Oracle use only the second column?

Usually no, unless Oracle uses special optimization techniques such as Index Skip Scan and the optimizer determines it is beneficial.

Example:

SELECT *
FROM employees
WHERE department_id = 20;

Normally, Oracle performs a Full Table Scan or uses another suitable index if available.

11. How do you view Composite Indexes?

SELECT index_name,
       column_name,
       column_position
FROM user_ind_columns
ORDER BY index_name,
         column_position;

12. How do you drop a Composite Index?

DROP INDEX idx_emp_name_dept;

13. Can a Composite Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_unique_emp
ON employees(email, department_id);

Duplicate combinations of email and department_id are not allowed.

14. What are the advantages of Composite Indexes?

• Faster searches using multiple columns
• Better JOIN performance
• Faster sorting
• Reduced Full Table Scans
• Improved overall query performance

15. What are the disadvantages of Composite Indexes?

• Consume additional disk space
• Slow INSERT operations
• Slow UPDATE operations
• Slow DELETE operations
• Less useful if queries do not use the leading column

16. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id   NUMBER,
  last_name     VARCHAR2(30),
  department_id NUMBER,
  salary        NUMBER
);

Create a composite index:

CREATE INDEX idx_emp_dept
ON employees(last_name, department_id);

Query using both columns:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND department_id = 10;

Oracle can use the composite index to retrieve matching rows efficiently.

17. Real-World Example

Suppose an HR application frequently runs:

SELECT *
FROM employees
WHERE department_id = 50
AND job_id = 'IT_PROG';

Create a composite index:

CREATE INDEX idx_dept_job
ON employees(department_id, job_id);

This improves the performance of queries filtering by both department_id and job_id.

Key Point:
Composite indexes work best when your queries search using the leading column or a combination of the indexed columns.

Oracle Simple Index FAQs with Examples

1. What is a Simple Index in Oracle?

A Simple Index is an index created on a single column of a table. It helps Oracle find data faster without scanning the entire table.

Example:

CREATE INDEX idx_emp_name
ON employees(first_name);

This creates an index on the first_name column.

2. Why do we use a Simple Index?

Simple indexes are used to:

• Speed up data retrieval
• Improve SELECT query performance
• Reduce full table scans
• Improve search operations on frequently used columns

Example:

SELECT *
FROM employees
WHERE first_name = 'John';

If first_name has an index, Oracle may find the data faster.

3. How do you create a Simple Index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name);

Example:

CREATE INDEX idx_salary
ON employees(salary);

This creates a simple index on the salary column.

4. How do you use a Simple Index?

You do not manually call an index. Oracle automatically decides whether to use the index based on the query and cost estimation.

Example:

SELECT *
FROM employees
WHERE salary = 50000;

If salary is indexed and Oracle finds it beneficial, it uses the index automatically.

5. How do you view indexes in Oracle?

SELECT index_name,
       table_name
FROM user_indexes;

Example output:

INDEX_NAME        TABLE_NAME
IDX_SALARY        EMPLOYEES

6. How do you see indexed columns?

SELECT index_name,
       column_name
FROM user_ind_columns;

Example output:

INDEX_NAME    COLUMN_NAME
IDX_SALARY    SALARY

7. How do you drop a Simple Index?

Syntax:

DROP INDEX index_name;

Example:

DROP INDEX idx_salary;

Dropping an index does not delete table data.

8. Can a Simple Index contain more than one column?

No. A Simple Index is created on only one column.

Example:

CREATE INDEX idx_salary
ON employees(salary);

For multiple columns, Oracle uses a Composite Index.

9. What is the difference between Simple Index and Composite Index?

Simple Index	Composite Index
Created on one column	Created on multiple columns
Example: salary	Example: salary, department_id
Easy to create and maintain	Used for complex searches

Simple Index example:

CREATE INDEX idx_salary
ON employees(salary);

10. Does a Primary Key create an index?

Yes. When you create a primary key, Oracle automatically creates a unique index unless a suitable index already exists.

Example:

CREATE TABLE employees
(
  employee_id NUMBER PRIMARY KEY,
  first_name  VARCHAR2(50)
);

Oracle creates an index on employee_id.

11. When should you create a Simple Index?

Create a simple index on columns that are:

• Frequently searched
• Used in WHERE conditions
• Used for sorting
• Used in joins

Example:

SELECT *
FROM employees
WHERE department_id = 10;

If this query runs frequently, creating an index can improve performance.

12. When should you avoid creating a Simple Index?

Avoid unnecessary indexes on:

• Very small tables
• Columns with very few different values
• Tables with heavy INSERT, UPDATE, and DELETE operations

Indexes require additional storage and maintenance.

13. Can one table have multiple Simple Indexes?

Yes.

Example:

CREATE INDEX idx_name
ON employees(first_name);

CREATE INDEX idx_salary
ON employees(salary);

CREATE INDEX idx_department
ON employees(department_id);

Each index is created on a single column.

14. What are the advantages of Simple Indexes?

• Faster data retrieval
• Faster searching
• Improved query performance
• Faster sorting operations
• Reduced table scanning

15. What are the disadvantages of Simple Indexes?

• Require extra storage space
• Slow down INSERT operations
• Slow down UPDATE operations
• Slow down DELETE operations
• Need maintenance

16. How do you rebuild a Simple Index?

Syntax:

ALTER INDEX index_name
REBUILD;

Example:

ALTER INDEX idx_salary
REBUILD;

17. How do you make a Simple Index unusable?

Oracle does not directly disable a normal index. You can mark it unusable.

Example:

ALTER INDEX idx_salary
UNUSABLE;

Rebuild it later:

ALTER INDEX idx_salary
REBUILD;

18. How do you check if Oracle is using a Simple Index?

Use an execution plan.

Example:

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE salary = 50000;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the output shows INDEX RANGE SCAN or INDEX UNIQUE SCAN, Oracle is using an index.

19. What happens if there is no Simple Index?

Oracle performs a Full Table Scan.

Example:

SELECT *
FROM employees
WHERE first_name = 'John';

Without an index on first_name, Oracle checks every row.

20. Simple Index Interview Example

Create a table:

CREATE TABLE employees
(
  employee_id NUMBER,
  first_name  VARCHAR2(50),
  salary      NUMBER
);

Create a Simple Index:

CREATE INDEX idx_emp_salary
ON employees(salary);

Search data:

SELECT *
FROM employees
WHERE salary = 50000;

Oracle can use idx_emp_salary to locate matching rows faster instead of scanning the entire table.

Key Point:
Simple indexes are best for single-column searches, frequent lookups, and improving Oracle query performance.

Oracle B-Tree Index FAQs with examples

1. What is a B-Tree Index in Oracle?

A B-Tree (Balanced Tree) Index is the default index type in Oracle. It stores index entries in a balanced tree structure, allowing Oracle to quickly locate rows.

It is best suited for high-cardinality columns (columns with many unique values).

Example:

CREATE INDEX idx_emp_id
ON employees(employee_id);

2. Why do we use a B-Tree Index?

B-Tree indexes are used to:

• Speed up data retrieval
• Improve SELECT query performance
• Reduce Full Table Scans
• Improve JOIN performance
• Speed up ORDER BY and GROUP BY operations

3. What is High Cardinality?

High cardinality means a column has many distinct values.

Examples:

• Employee ID
• Email
• Phone Number
• Passport Number

These columns are ideal for B-Tree indexes.

4. How do you create a B-Tree Index?

Since B-Tree is Oracle's default index type, simply use the standard CREATE INDEX syntax.

Syntax:

CREATE INDEX index_name
ON table_name(column_name);

Example:

CREATE INDEX idx_email
ON employees(email);

5. When should you use a B-Tree Index?

Use a B-Tree index when:

• Columns have many unique values
• Queries frequently use WHERE clauses
• Tables have frequent INSERT, UPDATE, and DELETE operations
• Applications are OLTP (Online Transaction Processing)

Example:

SELECT *
FROM employees
WHERE employee_id = 101;

6. When should you avoid a B-Tree Index?

Avoid a B-Tree index on columns with very few distinct values.

Examples:

• Gender
• Status
• Yes/No
• Active/Inactive

Bitmap indexes are usually better for these columns in data warehouse environments.

7. Difference between a B-Tree Index and a Bitmap Index

B-Tree Index	Bitmap Index
Best for high-cardinality columns	Best for low-cardinality columns
Suitable for OLTP systems	Suitable for data warehouses
Supports frequent DML operations	Not suitable for frequent DML operations
Default Oracle index type	Special index type

8. Can a B-Tree Index be created on multiple columns?

Yes. This is called a Composite B-Tree Index.

Example:

CREATE INDEX idx_name_dept
ON employees(last_name, department_id);

9. Can a B-Tree Index be Unique?

Yes.

CREATE UNIQUE INDEX idx_email
ON employees(email);

Duplicate values are not allowed.

10. How does Oracle search a B-Tree Index?

Oracle starts at the root node, moves through one or more branch nodes, and reaches the appropriate leaf node where the indexed value and corresponding row location are stored.

         Root
        /    \
   Branch   Branch
   /   \      /   \
Leaf  Leaf  Leaf  Leaf

This balanced structure allows Oracle to find rows efficiently.

11. How do you view B-Tree Indexes?

SELECT index_name,
       index_type,
       table_name
FROM user_indexes
WHERE index_type = 'NORMAL';

NORMAL indicates a standard B-Tree index.

12. How do you drop a B-Tree Index?

DROP INDEX idx_email;

13. How do you rebuild a B-Tree Index?

ALTER INDEX idx_email
REBUILD;

14. What are the advantages of B-Tree Indexes?

• Very fast data retrieval
• Efficient for unique lookups
• Suitable for range searches
• Good performance for JOIN operations
• Supports frequent INSERT, UPDATE, and DELETE operations
• Ideal for OLTP applications

15. What are the disadvantages of B-Tree Indexes?

• Use additional disk space
• Increase the cost of INSERT, UPDATE, and DELETE operations because the index must also be maintained
• Less effective for low-cardinality columns

16. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  first_name  VARCHAR2(50),
  email       VARCHAR2(100)
);

Create a B-Tree index:

CREATE INDEX idx_employee_id
ON employees(employee_id);

Run a query:

SELECT *
FROM employees
WHERE employee_id = 105;

Oracle can use the B-Tree index to quickly locate the matching row instead of scanning the entire table.

17. Real-World Example

Suppose an HR application frequently searches employees by email.

SELECT *
FROM employees
WHERE email = 'john@example.com';

Create a B-Tree index:

CREATE INDEX idx_emp_email
ON employees(email);

This significantly improves the performance of employee lookups by email.

18. Can a B-Tree Index be used for Range Searches?

Yes. B-Tree indexes are very efficient for range queries.

Example:

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

If salary has a B-Tree index, Oracle can efficiently locate rows within the specified range.

19. What are common Index Scan types for a B-Tree Index?

Oracle may use different index access methods, such as:

• INDEX UNIQUE SCAN – Retrieves a single row using a unique index.
• INDEX RANGE SCAN – Retrieves multiple rows within a range of indexed values.
• INDEX FULL SCAN – Scans all index entries in sorted order.
• INDEX FAST FULL SCAN – Reads the entire index quickly, similar to a full table scan but using the index.

20. How do you check if Oracle is using a B-Tree Index?

Use an execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE employee_id = 101;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the execution plan shows INDEX UNIQUE SCAN, INDEX RANGE SCAN, or another index access path, Oracle is using the B-Tree index.

Key Point:
B-Tree indexes are the best choice for high-cardinality columns, OLTP systems, and fast row lookups.

This makes B-Tree indexing one of the most important concepts in Oracle database interviews.

Oracle Index FAQs with examples

1. What is an Index in Oracle?

An index is a schema object that improves the speed of data retrieval by allowing Oracle to find rows quickly without scanning the entire table.

Example:

CREATE INDEX idx_emp_name
ON employees(first_name);

2. Why are indexes used?

Indexes are used to:

• Improve SELECT query performance
• Speed up WHERE clause searches
• Improve JOIN operations
• Speed up ORDER BY and GROUP BY operations
• Enforce uniqueness (using unique indexes)

3. What are the types of indexes in Oracle?

Oracle supports several types of indexes:

• B-Tree Index (Default)
• Unique Index
• Composite (Concatenated) Index
• Bitmap Index
• Function-Based Index
• Reverse Key Index
• Descending Index
• Partitioned Index
• Domain Index

4. What is a B-Tree Index?

A B-Tree index is the default index type and is best suited for columns with high cardinality (many distinct values).

Example:

CREATE INDEX idx_salary
ON employees(salary);

5. What is a Unique Index?

A unique index ensures that no duplicate values exist in the indexed column(s).

Example:

CREATE UNIQUE INDEX idx_email
ON employees(email);

Or using a constraint:

ALTER TABLE employees
ADD CONSTRAINT uk_email UNIQUE(email);

6. What is a Composite Index?

A composite index is created on two or more columns.

Example:

CREATE INDEX idx_name_dept
ON employees(last_name, department_id);

Useful when queries frequently search using both columns.

Example:

SELECT *
FROM employees
WHERE last_name='Smith'
AND department_id=10;

7. What is a Bitmap Index?

A bitmap index is suitable for columns with low cardinality, such as gender or status.

Example:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

Common values:

• Male/Female
• Active/Inactive
• Yes/No

Bitmap indexes are commonly used in data warehouses but are generally not recommended for tables with frequent DML operations.

8. What is a Function-Based Index?

A function-based index indexes the result of an expression or function.

Example:

CREATE INDEX idx_upper_name
ON employees(UPPER(last_name));

Now this query can use the index:

SELECT *
FROM employees
WHERE UPPER(last_name)='SMITH';

9. What is a Reverse Key Index?

A reverse key index reverses the bytes of indexed values to reduce index block contention.

Example:

CREATE INDEX idx_rev_empid
ON employees(employee_id)
REVERSE;

Commonly used with sequence-generated primary keys in high-concurrency systems.

10. What is a Descending Index?

A descending index stores values in descending order.

Example:

CREATE INDEX idx_salary_desc
ON employees(salary DESC);

Useful for queries like:

SELECT *
FROM employees
ORDER BY salary DESC;

11. How do you create an index?

Syntax:

CREATE INDEX index_name
ON table_name(column_name);

Example:

CREATE INDEX idx_department
ON employees(department_id);

12. How do you view indexes?

View indexes:

SELECT index_name,
       table_name,
       status
FROM user_indexes;

View indexed columns:

SELECT index_name,
       column_name
FROM user_ind_columns;

13. How do you drop an index?

DROP INDEX idx_department;

14. How do you rebuild an index?

Rebuilding removes fragmentation and improves performance.

ALTER INDEX idx_department
REBUILD;

15. How do you make an index unusable?

ALTER INDEX idx_department
UNUSABLE;

Rebuild it later:

ALTER INDEX idx_department
REBUILD;

16. What is Index Selectivity?

Selectivity indicates how unique the indexed values are.

Example:

Column	Good Candidate?
Employee ID	Yes
Email	Yes
Gender	No (Bitmap Index)
Status	No (Bitmap Index)

Higher selectivity usually means better performance with B-Tree indexes.

17. When does Oracle use an index?

Oracle may use an index when:

• WHERE clause filters indexed columns
• JOIN uses indexed columns
• ORDER BY uses indexed columns
• GROUP BY uses indexed columns
• High selectivity exists

Example:

SELECT *
FROM employees
WHERE employee_id=100;

If employee_id is indexed, Oracle can perform an Index Scan instead of a Full Table Scan.

18. When will Oracle ignore an index?

Oracle may choose a full table scan when:

• Most rows are returned
• Table is very small
• Statistics are outdated
• Functions are used without a function-based index
• The optimizer estimates a full scan is cheaper

Example:

SELECT *
FROM employees
WHERE salary > 1000;

If almost every row satisfies the condition, Oracle may ignore the index.

19. What is the difference between a Primary Key and an Index?

Primary Key	Index
Enforces uniqueness	Improves performance
Cannot contain NULL values	Can contain NULL values (depending on type and usage)
Automatically creates a unique index	Can be unique or non-unique
Used for data integrity	Used for faster data access

20. Can multiple indexes exist on a table?

Yes.

Example:

CREATE INDEX idx_salary
ON employees(salary);

CREATE INDEX idx_dept
ON employees(department_id);

CREATE INDEX idx_name
ON employees(last_name);

A table can have many indexes, but too many indexes can slow INSERT, UPDATE, and DELETE operations because Oracle must maintain each index.

21. What are the advantages of indexes?

• Faster SELECT queries
• Faster JOIN operations
• Faster sorting
• Faster grouping
• Improved query performance
• Faster primary key lookups

22. What are the disadvantages of indexes?

• Consume additional disk space
• Slow INSERT operations
• Slow UPDATE operations
• Slow DELETE operations
• Require maintenance and rebuilding in some cases
• Too many indexes can reduce DML performance

23. How do you check whether Oracle is using an index?

Use the execution plan.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE employee_id = 101;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the plan shows INDEX RANGE SCAN, INDEX UNIQUE SCAN, or another index access path, Oracle is using an index.

24. What is Index Range Scan?

Oracle uses an Index Range Scan when multiple adjacent index entries satisfy the search condition.

Example:

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

25. Interview Example: Improve Query Performance

Suppose the query is slow:

SELECT *
FROM employees
WHERE email = 'john@example.com';

Create an index:

CREATE INDEX idx_email
ON employees(email);

Now Oracle can retrieve the matching row much faster using the index.