Creating and tuning SQL queries in Oracle is a complex and multi-faceted task, and requires hands-on examples and simulations for different scenarios. Below, I provide 50 query tuning scenarios that cover a wide range of optimization techniques, along with table creation scripts, query scenarios, and suggestions for tuning the queries.
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
Copy code
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
Copy code
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
Copy code
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);
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='EMPLOYEES';
select EMP_ID from employees
50 Query Tuning Scenarios with Scripts and Suggestions
----------------------------------------------------------------------
1. Full Table Scan Without Index
**********************************************
Scenario: Querying a large table without an index.
sql
Copy code
SELECT emp_name FROM employees WHERE emp_name = 'John Doe';
SELECT emp_name FROM employees WHERE emp_name = 'Employee NHLMN1IL63Q90PFOSDUNC4DPO8AF6YX53NNESDOY2OAI4TQXT7';
explain plan for
SELECT emp_name FROM employees WHERE emp_name = 'Employee NHLMN1IL63Q90PFOSDUNC4DPO8AF6YX53NNESDOY2OAI4TQXT7';
SQL>
SQL> explain plan for
SELECT emp_name FROM employees WHERE emp_name = 'Employee NHLMN1IL63Q90PFOSDUNC4DPO8AF6YX53NNESDOY2OAI4TQXT7'; 2
Explained.
SQL>
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 302 | 15704 | 7453 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 302 | 15704 | 7453 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP_NAME"='Employee NHLMN1IL63Q90PFOSDUNC4DPO8AF6YX53NNES
DOY2OAI4TQXT7')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
Solution: Create an index on emp_name.
sql
Copy code
CREATE INDEX idx_emp_name ON employees(emp_name);
**2. Avoid SELECT ALL at once ***
********************************************
Scenario: Querying all columns when only specific ones are needed.
sql
Copy code
SELECT * FROM employees WHERE department_id = 10;
Solution: Select only required columns.
sql
Copy code
SELECT emp_id, emp_name FROM employees WHERE department_id = 10;
3. Avoid NOT IN
***************************************
Scenario: Using NOT IN causes performance issues.
sql
Copy code
SELECT emp_id FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);
Solution: Rewrite using NOT EXISTS.
sql
Copy code
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
Copy code
SELECT emp_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);
Solution: Use EXISTS.
sql
Copy code
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
Copy code
SELECT emp_id FROM employees WHERE UPPER(emp_name) = 'JOHN DOE';
Solution: Avoid the function or create a function-based index.
sql
Copy code
CREATE INDEX idx_emp_name_upper ON employees(UPPER(emp_name));
6. Use Bind Variables
********************************
Scenario: Avoid using literals to reduce parsing.
sql
Copy code
SELECT emp_name FROM employees WHERE department_id = 10;
Solution: Use bind variables.
sql
Copy code
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
Copy code
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
Copy code
SELECT e.emp_name, d.department_name FROM employees e, departments d;
Solution: Add join conditions.
sql
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
SELECT emp_name,
(SELECT department_name FROM departments d WHERE d.department_id = e.department_id)
FROM employees e;
Solution: Use a join.
sql
Copy code
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
Copy code
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
Solution: Create an index on department_id.
sql
Copy code
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
Copy code
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
Copy code
SELECT emp_id FROM employees WHERE emp_id = '100';
Solution: Use explicit data types.
sql
Copy code
SELECT emp_id FROM employees WHERE emp_id = 100;
15. Gather Statistics Regularly
Scenario: Outdated statistics can lead to suboptimal execution plans.
sql
Copy code
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
END;
/
16. Use Optimizer Hints
Scenario: Force Oracle to use specific execution plans when necessary.
sql
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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
Copy code
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.
No comments:
Post a Comment