Ten SQL Antipatterns Every APEX Developer Should Avoid

Common SQL Mistakes That Kill Performance

These ten SQL antipatterns appear frequently in APEX applications and cause unnecessary performance problems, incorrect results, or maintenance headaches.

1. SELECT * in Production Queries

Always specify the columns you need. SELECT * fetches unnecessary data, breaks when table structures change, and prevents the optimizer from using covering indexes.

2. Functions on Indexed Columns in WHERE Clauses

-- BAD: UPPER() prevents index use
WHERE UPPER(last_name) = 'SMITH'

-- BETTER: Create a function-based index
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
-- Or use a case-insensitive column setting in 12c+

3. NOT IN With NULLable Columns

-- DANGEROUS: Returns no rows if any manager_id is NULL
WHERE department_id NOT IN (SELECT department_id FROM restricted_depts)

-- SAFE: Use NOT EXISTS instead
WHERE NOT EXISTS (SELECT 1 FROM restricted_depts r
                   WHERE r.department_id = e.department_id)

4. Implicit Data Type Conversion

-- BAD: employee_id is NUMBER but compared to string
WHERE employee_id = '101'

-- CORRECT: Use matching types
WHERE employee_id = 101

5. Using DISTINCT to Hide Join Problems

If you need DISTINCT because your query returns duplicates, the join logic is probably wrong. Fix the join rather than masking the problem with DISTINCT.

6. Correlated Subqueries Where Joins Would Work

-- SLOW: Subquery executes once per row
SELECT e.first_name, (SELECT d.department_name FROM departments d
                       WHERE d.department_id = e.department_id) AS dept
  FROM employees e

-- FASTER: Use a join
SELECT e.first_name, d.department_name
  FROM employees e JOIN departments d ON e.department_id = d.department_id

7. LIKE With Leading Wildcards

-- Forces full table scan: no index can help
WHERE description LIKE '%widget%'

-- Use Oracle Text CONTAINS for full-text search instead

8. Cartesian Joins (Missing Join Conditions)

Always verify that every table in the FROM clause has a join condition. A missing condition produces a cross product that can return millions of unintended rows.

9. Using HAVING Instead of WHERE

-- BAD: Filters after aggregation
SELECT department_id, COUNT(*) FROM employees
 GROUP BY department_id HAVING department_id = 20

-- CORRECT: Filter before aggregation
SELECT department_id, COUNT(*) FROM employees
 WHERE department_id = 20 GROUP BY department_id

10. Ignoring Execution Plans

If a query runs slowly, check the execution plan before adding indexes or rewriting SQL. Use EXPLAIN PLAN FOR followed by SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) to see exactly what the optimizer is doing. The plan often reveals the problem immediately: a missing index, an unexpected full table scan, or a suboptimal join order.