1. What is a B-Tree Index in Oracle?
A B-Tree (Balanced Tree)
index is the default index type in Oracle.
It is designed for:
- High-cardinality columns
- Equality searches (=)
- Range searches (>, <, BETWEEN)
- Join conditions
Example:
CREATE INDEX emp_salary_idx ON employees(salary);
If you don’t specify type, Oracle creates a B-Tree index.
2. Why is it called “Balanced Tree”?
- All leaf nodes are at the same depth
- Automatically balances during inserts and deletes
- Search time remains efficient (O(log n))
3. Internal structure of a B-Tree index
Contains:
1. Root Block – Starting point of search
2. Branch Blocks – Guide navigation
3. Leaf Blocks – Store indexed column value + ROWID
Structure:
Root
↓
Branch
↓
Leaf → (Value + ROWID)
4. How does a B-Tree index search work?
Example:
SELECT * FROM employees WHERE salary = 5000;
Oracle:
1. Starts at root block
2. Navigates branch blocks
3. Finds matching value in leaf
4. Uses ROWID to fetch row
Fast because it avoids full table scan.
5. When to use a B-Tree index?
Best for:
- High-cardinality columns
- Primary keys and foreign keys
- Frequently filtered columns
- Range conditions
Examples: emp_id, email, transaction_id
6. When NOT to use a B-Tree index?
Avoid for:
- Low-cardinality columns (gender, status)
- Very small tables
- Heavily updated columns
- Rarely filtered columns
For low-cardinality in Data Warehousing → use Bitmap index.
7. High Cardinality
|
Column |
Distinct Values |
Good for B-Tree? |
|
emp_id |
1 million |
Yes |
|
gender |
2 |
No |
|
status |
3 |
No |
8. Types of B-Tree scans
- Index Unique Scan – searches a unique value, fastest (PK)
- Index Range Scan – WHERE salary BETWEEN 5000 AND 10000
- Index Full Scan – scans entire index, used when index smaller than table
- Fast Full Index Scan – reads index like a table, no table access needed
9. Clustering Factor
Measures how ordered table data is relative to the index.
- Low → fewer block reads, better performance
- High → more block reads, worse performance
10. DML impact
|
Operation |
Impact |
|
INSERT |
Adds new leaf entry |
|
UPDATE (indexed column) |
Removes + re-inserts entry |
|
DELETE |
Marks entry as deleted |
Heavy DML → leaf block splits and fragmentation.
11. Leaf Block Split
Occurs when leaf block is full
and new value is inserted. Frequent splits reduce performance.
Common in sequential inserts (like sequence PK). Solution: use reverse key
index.
12. Reverse Key B-Tree Index
Reverses byte order of indexed column to prevent hot block contention.
CREATE INDEX emp_id_rev_idx ON employees(emp_id) REVERSE;
13. Composite B-Tree Index
Index on multiple columns:
CREATE INDEX emp_dept_salary_idx ON employees(dept_id, salary);
Column order matters. Best if first column is used in WHERE clause.
14. Function-Based B-Tree Index
Index on expression:
CREATE INDEX emp_upper_name_idx ON employees(UPPER(name));
Improves performance for queries like:
WHERE UPPER(name) = 'JOHN'
15. Storage
Depends on number of rows, column size, distinct values. Stored in separate segment, typically smaller than table.
16. Advantages
- Fast lookups
- Efficient range queries
- Automatically balanced
- Good for OLTP
- Default, widely used
17. Disadvantages
- Slows DML
- Not efficient for low-cardinality
- Fragmentation under heavy deletes
- Requires storage
18. Performance tuning tips
- Index frequently filtered columns
- Index foreign keys
- Avoid over-indexing
- Monitor clustering factor
- Rebuild if fragmented
- Keep statistics updated
- Choose correct column order in composite index
19. Real-world example
Bad query:
SELECT * FROM orders WHERE customer_id = 100;
Without index → full table scan.
Better:
CREATE INDEX orders_cust_idx ON orders(customer_id);
Query uses index range scan.
20. Interview Tip
“A B-Tree index is Oracle’s default balanced tree index that stores column values with ROWIDs. Provides fast logarithmic search performance, ideal for high-cardinality columns and range queries. Improves SELECT performance but adds overhead to insert, update, delete operations.”
No comments:
Post a Comment