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.