Simple Index FAQS

1. What is a Simple Index in Oracle?

A Simple Index is an index created on a single column of a table.

It is the most basic and commonly used index type.

Example:

CREATE INDEX emp_salary_idx

ON employees(salary);

This index is:

·        Single-column

·        B-Tree by default

·        Used to improve query performance

2. Is Simple Index the same as B-Tree Index?

Yes, by default.

When you create:

CREATE INDEX idx_name ON table_name(column_name);

Oracle creates a B-Tree index unless specified otherwise.

So:

Simple Index = Single-column B-Tree Index (in most cases)

3. How does a Simple Index work internally?

Structure:

Root Block

  ↓

Branch Blocks

  ↓

Leaf Blocks → (Column Value + ROWID)

Example:

If salary values are:

3000

4000

5000

6000

Index stores:

3000 → ROWID

4000 → ROWID

5000 → ROWID

6000 → ROWID

Sorted automatically.

4. How does Oracle use Simple Index?

Example query:

SELECT *

FROM employees

WHERE salary = 5000;

Oracle:

1.    Searches index root

2.    Navigates to leaf block

3.    Finds salary = 5000

4.    Uses ROWID to fetch row

Avoids full table scan.

5. When should you create a Simple Index?

Best for:

·        Columns used frequently in WHERE clause

·        JOIN columns

·        High-cardinality columns

·        Foreign keys

·        Frequently searched columns

Examples:

·        employee_id

·        email

·        order_id

·        account_number

6. What is High Cardinality?

High cardinality means:

·        Many distinct values

Example:

Column	Distinct Values	Good for Simple Index?
emp_id	1M	Yes
gender	2	No
status	3	No

Simple indexes work best with high-cardinality columns.

7. When should you NOT create a Simple Index?

Avoid if:

·        Column has very few distinct values

·        Table is very small

·        Column rarely used in queries

·        Heavy DML and index not needed

Because:

·        Index adds overhead

·        May not improve performance

8. What types of scans can occur?

1. Index Unique Scan

If column is unique.

WHERE employee_id = 100

Fastest type.

2. Index Range Scan

WHERE salary BETWEEN 4000 AND 6000

Oracle scans portion of index.

3. Index Full Scan

Reads entire index.

4. Fast Full Index Scan

Reads index like a table.

Used when query only needs indexed column.

9. How does Simple Index affect DML?

INSERT:

·        Adds entry in index

UPDATE:

·        If indexed column changes → delete + insert

DELETE:

·        Removes index entry

More indexes = more DML overhead.

10. Does Simple Index improve SELECT performance?

Yes, when:

·        Query filters on indexed column

·        Table is large

·        Selectivity is good

No improvement if:

·        Query returns most rows

·        Full table scan cheaper

11. What is Selectivity?

Selectivity measures how selective a condition is.

Example:

WHERE emp_id = 100

Returns 1 row → High selectivity → Good for index.

WHERE status = 'ACTIVE'

Returns 90% rows → Poor selectivity → Index not useful.

12. What is Clustering Factor?

Clustering Factor measures how ordered table data is relative to index.

Low value:

·        Table rows physically close

·        Fewer block reads

·        Better performance

High value:

·        Rows scattered

·        More I/O

13. Does Simple Index consume space?

Yes.

Stored as separate segment.

Storage depends on:

·        Number of rows

·        Column size

·        Distinct values

Trade-off:

More storage for faster queries.

14. Can Simple Index enforce uniqueness?

Yes, if created as:

CREATE UNIQUE INDEX emp_email_uk

ON employees(email);

Prevents duplicates.

15. Simple Index vs Composite Index

Feature	Simple Index	Composite Index
Columns	One	Multiple
Storage	Smaller	Larger
Query flexibility	Limited	More flexible
Multi-column filters	Less efficient	Better

Simple index best for single-column filtering.

16. How to check if index is used?

Use:

EXPLAIN PLAN FOR

SELECT * FROM employees WHERE salary = 5000;

Look for:

INDEX RANGE SCAN

If you see:

TABLE ACCESS FULL

Index not used.

17. Common mistakes

·        Creating index on low-selectivity column

·        Creating too many indexes

·        Indexing small tables unnecessarily

·        Not gathering statistics

·        Ignoring execution plans

18. Best practices

·        Index columns frequently used in WHERE/JOIN

·        Avoid indexing columns rarely queried

·        Monitor execution plans

·        Avoid redundant indexes

·        Update statistics regularly

·        Index foreign keys in OLTP systems

19. Real-world Example

Query:

SELECT *

FROM orders

WHERE customer_id = 500;

Without index:

·        Full table scan

·        Slow if millions of rows

Solution:

CREATE INDEX orders_cust_idx

ON orders(customer_id);

Now:

·        Index range scan

·        Faster performance

20. Interview Tip

If asked:

“What is a Simple Index in Oracle?”

Answer:

“A Simple Index is a single-column B-Tree index used to improve query performance by storing sorted column values along with ROWIDs. It is best suited for high-cardinality columns frequently used in WHERE or JOIN conditions. While it improves SELECT performance, it adds overhead to INSERT, UPDATE, and DELETE operations.”