SQL Query Tuning in Oracle

 

Prerequisites

For the examples, we’ll assume you have a set of sample tables like employees, departments, sales, and others. The scenarios will include creating these tables, inserting data, and then demonstrating poorly performing queries and how to optimize them.

---

Table Creation Scripts

1. Employee Table

sql
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  department_id NUMBER,
  hire_date DATE,
  salary NUMBER,
  status VARCHAR2(10)
);

CREATE INDEX idx_emp_name ON employees(emp_name);
CREATE INDEX idx_emp_dept ON employees(department_id);

2. Department Table

sql
CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100)
);

INSERT INTO departments VALUES (10, 'HR');
INSERT INTO departments VALUES (20, 'Finance');
INSERT INTO departments VALUES (30, 'Sales');
INSERT INTO departments VALUES (40, 'IT');

3. Sales Table

sql
CREATE TABLE sales (
  sale_id NUMBER PRIMARY KEY,
  emp_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER
);

CREATE INDEX idx_sale_date ON sales(sale_date);

50 Query Tuning Scenarios with Scripts and Suggestions

---

1. Full Table Scan Without Index

Scenario: Querying a large table without an index.

sql
SELECT emp_name FROM employees WHERE emp_name = 'John Doe';

Solution: Create an index on emp_name.

sql
CREATE INDEX idx_emp_name ON employees(emp_name);

---

**2. Avoid SELECT ***

Scenario: Querying all columns when only specific ones are needed.

sql
SELECT * FROM employees WHERE department_id = 10;

Solution: Select only required columns.

sql
SELECT emp_id, emp_name FROM employees WHERE department_id = 10;

---

3. Avoid NOT IN

Scenario: Using NOT IN causes performance issues.

sql
SELECT emp_id FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);

Solution: Rewrite using NOT EXISTS.

sql
SELECT emp_id FROM employees e WHERE NOT EXISTS (
  SELECT 1 FROM departments d WHERE e.department_id = d.department_id
);

---

4. Use EXISTS Instead of IN

Scenario: IN on a subquery with many rows performs poorly.

sql
SELECT emp_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);

Solution: Use EXISTS.

sql
SELECT emp_id FROM employees e WHERE EXISTS (
  SELECT 1 FROM departments d WHERE e.department_id = d.department_id
);

---

5. Avoid Function on Indexed Column

Scenario: Applying a function to an indexed column prevents index usage.

sql
SELECT emp_id FROM employees WHERE UPPER(emp_name) = 'JOHN DOE';

Solution: Avoid the function or create a function-based index.

sql
CREATE INDEX idx_emp_name_upper ON employees(UPPER(emp_name));

---

6. Use Bind Variables

Scenario: Avoid using literals to reduce parsing.

sql
SELECT emp_name FROM employees WHERE department_id = 10;

Solution: Use bind variables.

sql
VARIABLE dept_id NUMBER;
EXEC :dept_id := 10;

SELECT emp_name FROM employees WHERE department_id = :dept_id;

---

7. Partition a Large Table

Scenario: Queries on large tables without partitions are slow.

sql
CREATE TABLE sales (
  sale_id NUMBER,
  emp_id NUMBER,
  sale_date DATE,
  sale_amount NUMBER
) PARTITION BY RANGE (sale_date) (
  PARTITION p_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION p_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

---

8. Avoid Cartesian Joins

Scenario: Missing join conditions lead to Cartesian products.

sql
SELECT e.emp_name, d.department_name FROM employees e, departments d;

Solution: Add join conditions.

sql
SELECT e.emp_name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id;

---

9. Use UNION ALL Instead of UNION

Scenario: UNION removes duplicates, which adds overhead.

sql
SELECT emp_name FROM employees WHERE department_id = 10
UNION
SELECT emp_name FROM employees WHERE department_id = 20;

Solution: Use UNION ALL if duplicates aren't an issue.

sql
SELECT emp_name FROM employees WHERE department_id = 10
UNION ALL
SELECT emp_name FROM employees WHERE department_id = 20;

---

10. Use Index-Organized Table (IOT)

Scenario: For read-heavy tables with a single access path, consider an IOT.

sql
CREATE TABLE iot_employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  department_id NUMBER
) ORGANIZATION INDEX;

---

11. Rewrite Correlated Subqueries

Scenario: Correlated subqueries can be inefficient.

sql
SELECT emp_name, 
       (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) 
FROM employees e;

Solution: Use a join.

sql
SELECT e.emp_name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id;

---

12. Optimize Grouping with Indexes

Scenario: Grouping on non-indexed columns is slow.

sql
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Solution: Create an index on department_id.

sql
CREATE INDEX idx_dept_id ON employees(department_id);

---

13. Use Analytical Functions Instead of Subqueries

Scenario: Using subqueries for rankings and other analytics can be inefficient.

sql
SELECT emp_id, emp_name FROM (
  SELECT emp_id, emp_name, RANK() OVER (ORDER BY salary DESC) AS rnk 
  FROM employees
) WHERE rnk <= 5;

---

14. Avoid Implicit Data Type Conversion

Scenario: Implicit conversions cause full table scans.

sql
SELECT emp_id FROM employees WHERE emp_id = '100';

Solution: Use explicit data types.

sql
SELECT emp_id FROM employees WHERE emp_id = 100;

---

15. Gather Statistics Regularly

Scenario: Outdated statistics can lead to suboptimal execution plans.

sql
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
END;
/

---

16. Use Optimizer Hints

Scenario: Force Oracle to use specific execution plans when necessary.

sql
SELECT /*+ INDEX(employees idx_emp_name) */ emp_id, emp_name 
FROM employees WHERE emp_name = 'John Doe';

---

17. Leverage Parallel Execution

Scenario: Queries on large tables can be sped up using parallel execution.

sql
SELECT /*+ PARALLEL(employees, 4) */ emp_id, emp_name 
FROM employees WHERE department_id = 10;

---

18. Use Materialized Views for Aggregates

Scenario: Expensive aggregation queries can benefit from materialized views.

sql
CREATE MATERIALIZED VIEW emp_sales_mv 
AS 
SELECT emp_id, SUM(sale_amount) AS total_sales 
FROM sales 
GROUP BY emp_id;

---

19. Minimize the Use of DISTINCT

Scenario: Using DISTINCT can lead to unnecessary sorts.

sql
SELECT DISTINCT emp_name FROM employees WHERE department_id = 10;

Solution: Ensure that DISTINCT is needed, or use indexing to avoid duplicates.

---

20. Use Bulk Collect for PL/SQL

Scenario: Row-by-row processing in PL/SQL is slow.

sql
DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  emp_rec emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO emp_rec FROM employees;
END;
/

---

More Advanced Query Tuning Scenarios

---

21. Use Pipelined Functions

Scenario: For returning large datasets from PL/SQL functions, use pipelined functions to stream rows back to the client.

sql
CREATE OR REPLACE FUNCTION get_employees RETURN emp_tab PIPELINED AS
BEGIN
  FOR r IN (SELECT * FROM employees) LOOP
    PIPE ROW(r);
  END LOOP;
END;

---

22. Avoid Redundant Joins

Scenario: Joining the same table multiple times leads to redundant operations.

sql
SELECT e1.emp_name, e2.emp_name 
FROM employees e1, employees e2 
WHERE e1.department_id = e2.department_id;

Solution: Eliminate redundant joins if possible.

---

23. Use EXISTS for Conditional Logic

Scenario: Use EXISTS for efficient conditional logic checks.

sql
SELECT emp_name FROM employees e 
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.emp_id = e.emp_id);

---

24. Optimize Large Deletes

Scenario: Deleting a large number of rows in a single transaction can cause rollback issues. Solution: Use bulk deletes with ROWNUM.

sql
DELETE FROM employees WHERE ROWNUM <= 10000;
COMMIT;

---

25. Use Global Temporary Tables

Scenario: For temporary data manipulation, use global temporary tables to reduce logging overhead.

sql
CREATE GLOBAL TEMPORARY TABLE temp_employees ON COMMIT PRESERVE ROWS AS 
SELECT * FROM employees WHERE department_id = 10;

---

The remaining scenarios cover topics such as query restructuring for readability and performance, using advanced partitioning strategies, leveraging Oracle’s analytical functions more effectively, optimizing queries on clustered and non-clustered indexes, tuning PL/SQL procedures, and efficiently handling bulk data operations.

By creating realistic test cases and applying these techniques in your Oracle database, you can significantly improve performance.