🚀 Module 14: Indexes & Performance Tuning

-- Create simple B-tree index
CREATE INDEX idx_emp_salary
ON employees(salary)
PCTFREE 10
PCTUSED 60;

-- Create composite index (important for multi-column searches)
CREATE INDEX idx_emp_dept_salary
ON employees(dept_id, salary)
PCTFREE 10;

-- Create index with included columns (avoid table lookups)
CREATE INDEX idx_emp_sal_inc
ON employees(salary)
INCLUDE (name, email);

-- Drop index
DROP INDEX idx_emp_salary;

-- View indexes
SELECT index_name, table_name, uniqueness
FROM user_indexes;

-- Create bitmap index for low cardinality columns
CREATE BITMAP INDEX idx_emp_gender
ON employees(gender);

-- Create composite bitmap index
CREATE BITMAP INDEX idx_emp_status_dept
ON employees(emp_status, dept_id);

-- Useful for:
-- - Few distinct values (M/F, Active/Inactive)
-- - OLAP workloads, data warehousing
-- - Complex WHERE clauses with AND/OR

-- Create function-based index (UPPER function)
CREATE INDEX idx_emp_name_upper
ON employees(UPPER(name));

-- Now this query uses the index:
SELECT * FROM employees
WHERE UPPER(name) = 'JOHN';

-- Function-based with expression
CREATE INDEX idx_emp_annual_salary
ON employees(salary * 12);

-- Case expression index
CREATE INDEX idx_emp_category
ON employees(
  CASE
    WHEN salary > 100000 THEN 'SENIOR'
    WHEN salary > 50000 THEN 'MID'
    ELSE 'JUNIOR'
  END
);

-- JSON search index (Oracle 12.2+)
CREATE INDEX idx_doc_search
ON documents(json_col) INDEXTYPE IS CTXSYS.CONTEXT;

-- Monitor index usage
ALTER INDEX idx_emp_salary MONITORING USAGE;

-- View index statistics
SELECT index_name, blevel, leaf_blocks, distinct_keys
FROM user_ind_statistics
WHERE index_name = 'IDX_EMP_SALARY';

-- Check index fragmentation
SELECT index_name, del_lf_rows, lf_rows,
       ROUND(100 * del_lf_rows / lf_rows, 2) fragmentation_pct
FROM user_ind_statistics
WHERE lf_rows > 0;

-- Find unused indexes
SELECT i.index_name, i.table_name
FROM user_indexes i
LEFT JOIN v$object_usage v ON i.index_name = v.name
WHERE v.name IS NULL;

-- Rebuild fragmented index
ALTER INDEX idx_emp_salary REBUILD ONLINE;

-- Leading column is most selective
CREATE INDEX idx_emp_composite
ON employees(
  dept_id,           -- Most selective first
  salary,            -- Second
  hire_date          -- Least selective
);

-- Separate indexes vs composite
-- When to use composite:
-- - Frequently used together in WHERE clause
-- - Covering index can avoid table access

-- Partial index (filter on subset of rows)
CREATE INDEX idx_emp_active
ON employees(emp_id)
WHERE status = 'ACTIVE';

-- Invisible index (test without rebuilding)
CREATE INDEX idx_emp_test
ON employees(salary)
INVISIBLE;

-- Make index visible
ALTER INDEX idx_emp_test VISIBLE;