-- Forcing join order without testing

SELECT /*+ USE_NL(c o) */ c.name, o.total

FROM customers c, orders o

WHERE c.id = o.customer_id;

-- Test optimizer choice first, then consider hints

EXPLAIN SELECT c.name, o.total

FROM customers c

JOIN orders o ON c.id = o.customer_id;

-- Only add hints if EXPLAIN shows suboptimal plan

Always analyze EXPLAIN output first. Optimizer usually chooses well. Hints should be last resort with evidence.

Using hints without understanding optimizer choice

-- Assuming SQL order equals execution order

SELECT * FROM large_table l

JOIN small_table s ON l.id = s.large_id;

-- Assuming large_table processed first

-- Optimizer chooses based on statistics, not SQL order

EXPLAIN SELECT * FROM large_table l

JOIN small_table s ON l.id = s.large_id;

-- Check EXPLAIN to see actual join order

Optimizer reorders joins based on cost estimates. Use EXPLAIN to see actual execution plan.

SQL join order is logical, not physical execution order

-- Not updating statistics leading to poor join order

-- Statistics last updated 6 months ago

SELECT * FROM customers c

JOIN orders o ON c.id = o.customer_id;

-- Keep statistics current for optimal join order

ANALYZE TABLE customers;

ANALYZE TABLE orders;

SELECT * FROM customers c

JOIN orders o ON c.id = o.customer_id;

Optimizer relies on statistics for cost estimates. Update statistics regularly, especially after large data changes.

Outdated statistics cause optimizer to choose poor join order