Oracle Indexes (The Speed Boosters) ⚡
Mentor's Note: Imagine searching for a word in a 1,000-page book. Would you read every page? No, you go to the Index at the back! An index in Oracle does the exact same thing—it helps the database find data without reading the whole table. 💡
🌟 The Scenario: The Library Catalog 📚
- Full Table Scan: Checking every shelf in the library to find one book. (Slow! 🐢)
- Indexed Search: Checking the computer catalog to find the exact shelf and row number. (Fast! ⚡)
💻 1. Core Index Types
A. B-Tree Index (The Default)
Perfect for columns with many unique values (e.g., Name, Email, ID).
CREATE INDEX idx_emp_last_name ON employees(last_name);
B. Bitmap Index (For Low Variety) 📊
Best for columns with only a few values (e.g., Gender, Marital Status, City). Architect's Note: Only use this in Data Warehouses!
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
C. Function-Based Index
If you search using UPPER(name), a normal index won't help. You need a function-based one.
CREATE INDEX idx_emp_upper_name ON employees(UPPER(first_name));
💻 2. Unique Indexes 💎
Ensures all values are different AND speeds up search. (Oracle automatically creates these for Primary Keys).
CREATE UNIQUE INDEX idx_passport_no ON travelers(passport_no);
🏗️ Architect's Note: The "Index Tax" 🛡️
Indexes make SELECT faster, but they make INSERT/UPDATE slower.
- The Architect's Secret: Every time you add a row, Oracle must also add an entry to every index on that table.
- The Golden Rule: Only index columns that are frequently used in WHERE clauses or JOIN conditions.