⚡ Module 15: Query Optimization & Execution Plans

-- Create plan table (if not exists)
CREATE TABLE plan_table (
  statement_id VARCHAR2(30),
  plan_id NUMBER,
  timestamp DATE,
  remarks VARCHAR2(4000),
  operation VARCHAR2(30),
  options VARCHAR2(255),
  object_node VARCHAR2(128),
  object_owner VARCHAR2(30),
  object_name VARCHAR2(30),
  object_alias VARCHAR2(65),
  object_instance NUMBER,
  object_type VARCHAR2(30),
  optimizer VARCHAR2(255),
  search_columns NUMBER,
  id NUMBER,
  parent_id NUMBER,
  depth NUMBER,
  position NUMBER,
  cost NUMBER,
  cardinality NUMBER,
  bytes NUMBER,
  other_tag VARCHAR2(255),
  partition_start VARCHAR2(255),
  partition_stop VARCHAR2(255),
  partition_id NUMBER,
  other LONG,
  distribution VARCHAR2(30),
  cpu_cost NUMBER,
  io_cost NUMBER,
  temp_space NUMBER,
  access_predicates VARCHAR2(4000),
  filter_predicates VARCHAR2(4000),
  projection VARCHAR2(4000),
  time NUMBER,
  qblock_name VARCHAR2(30)
);

-- Explain a query
EXPLAIN PLAN
SET statement_id = 'query_001'
FOR
SELECT * FROM employees
WHERE salary > 50000;

-- View the plan
SELECT *
FROM table(dbms_xplan.display('plan_table', 'query_001'));

-- Force full table scan
SELECT /*+ FULL(e) */ *
FROM employees e
WHERE salary > 50000;

-- Force index usage
SELECT /*+ INDEX(e idx_salary) */ *
FROM employees e
WHERE salary > 50000;

-- Force join method
SELECT /*+ HASH_JOIN(e d) */ e.*, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Parallel execution
SELECT /*+ PARALLEL(4) */ count(*)
FROM large_table;

-- No merge (avoid subquery merge)
SELECT /*+ NO_MERGE(sq) */ *
FROM (
  SELECT emp_id, salary
  FROM employees
  WHERE salary > 50000
) sq;

-- Force driving table
SELECT /*+ LEADING(d) */ e.*, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- Gather table statistics
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMPLOYEES',
    estimate_percent => 10,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

-- Gather schema statistics
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'SCOTT',
    options => 'GATHER',
    estimate_percent => 10,
    degree => 4
  );
END;
/

-- View table statistics
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name = 'EMPLOYEES';

-- View column statistics
SELECT column_name, num_distinct, low_value, high_value
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

-- Efficient join with small result
SELECT e.emp_id, e.name, d.dept_name
FROM departments d
INNER JOIN employees e ON d.dept_id = e.dept_id
WHERE d.dept_id = 10;

-- Use EXISTS instead of IN for correlated subqueries
SELECT emp_id, name
FROM employees e
WHERE EXISTS (
  SELECT 1
  FROM assignments
  WHERE emp_id = e.emp_id
);

-- Avoid function calls in WHERE (prevents index usage)
-- BAD: WHERE UPPER(name) = 'JOHN'
-- GOOD:
SELECT emp_id, name
FROM employees
WHERE name = 'John';

-- Use UNION instead of OR for index efficiency
SELECT emp_id, name
FROM employees
WHERE dept_id = 10
UNION
SELECT emp_id, name
FROM employees
WHERE salary > 100000;