What is the best way to do SQL select statements in Oracle PL/SQL?
Answer:
For best performance of select statements returning many rows, use the PL/SQL fetch bulk collect with limit.
DECLARE
CURSOR hr_employees_cur IS
SELECT first_name, last_name, hire_date, salary
FROM hr.employees;
TYPE hr_employees_rec IS record(
first_name hr.employees.first_name%TYPE,
last_name hr.employees.last_name%TYPE,
hire_date hr.employees.hire_date%TYPE,
salary hr.employees.salary%TYPE
);
batchSize CONSTANT PLS_INTEGER := 100;
TYPE hr_employees_vat IS VARRAY(100) OF hr_employees_rec;
hr_employees hr_employees_vat;
BEGIN
OPEN hr_employees_cur;
LOOP
FETCH hr_employees_cur BULK COLLECT INTO hr_employees LIMIT batchSize;
FOR i IN 1 .. hr_employees.COUNT()
LOOP
IF ( hr_employees(i).salary > 10000 AND
hr_employees(i).hire_date > TO_DATE('15-04-1999', 'DD-MM-YYYY') )
THEN
sys.dbms_output.put_line(
'New employee over salary limit: ' ||
hr_employees(i).first_name ||
' ' ||
hr_employees(i).last_name);
ELSIF ( hr_employees(i).salary < 2400 AND
hr_employees(i).hire_date > TO_DATE('15-04-1999', 'DD-MM-YYYY') )
THEN
sys.dbms_output.put_line(
'New employee under salary limit: ' ||
hr_employees(i).first_name ||
' ' ||
hr_employees(i).last_name);
END IF;
END LOOP;
EXIT WHEN hr_employees.COUNT() < batchSize;
END LOOP;
CLOSE hr_employees_cur;
END;
Notes:
- Fetch bulk collect performs the best because it reduces context switching between the PL/SQL and SQL engine. When PL/SQL executes a SQL statement, like in a select SQL fetch, the PL/SQL engine has to pass the processing to the separate SQL engine. The passing of processing between the PL/SQL and SQL engine is called context switching and it is CPU expensive. Executing a 10,000 row select fetch in a loop results in 10,000 context switches. Bulk collect fetch batches multi-row select fetches into 1 context switch.
- Limit is needed so the bulk collect fetch does not consume excessive per-session memory for select queries that returns a large number of rows.
- A batch size of 100 is a good balance between memory and performance. Increasing the batch size above 100 usually leads to diminishing returns in performance.
- Unfortunately, Oracle VARRAY does not accept a variable as its initial size.
- Oracle recommends exiting the loop via the VARRAY.COUNT instead of CURSOR%NOTFOUND.
- Oracle 10g by default (plsql_optimize_level=2) optimizes cursor for-loops to speeds comparable to fetch bulk collect with limit of 100 and with less code. If you do not need to tune the bulk collect limit size, the cursor for-loop is a good compromise between fast code creation/maintainability and runtime performance.
The above example using cursor for-loops
DECLARE
CURSOR hr_employees_cur IS
SELECT first_name, last_name, hire_date, salary
FROM hr.employees;
BEGIN
FOR hr_employees_rec IN hr_employees_cur
LOOP
IF ( hr_employees_rec.salary > 10000 AND
hr_employees_rec.hire_date > TO_DATE('15-04-1999', 'DD-MM-YYYY') )
THEN
sys.dbms_output.put_line(
'New employee over salary limit: ' ||
hr_employees_rec.first_name ||
' ' ||
hr_employees_rec.last_name);
ELSIF ( hr_employees_rec.salary < 2400 AND
hr_employees_rec.hire_date > TO_DATE('15-04-1999', 'DD-MM-YYYY') )
THEN
sys.dbms_output.put_line(
'New employee under salary limit: ' ||
hr_employees_rec.first_name ||
' ' ||
hr_employees_rec.last_name);
END IF;
END LOOP;
END;
Sources:
No comments:
Post a Comment