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.