CODEWITHSUNDEEP
sqloracle-databaseplsql
user2647888
user2647888

Reputation: 721

How to use a bulk collect to replace this cursor for loop

DECLARE 
L_DEPT_ID employees_test_1.department_id%type;
L_DEPT_NAME VARCHAR(255) := '&2';
columns_updated VARCHAR(255) := 'My check is hopes';

CURSOR CHECK_1 
IS
SELECT first_name, last_name, job_id, salary, manager_id, department_id, COMMENTS_RESTORE
from employees_test_1
where salary > 1000
and department_id=L_DEPT_ID;

BEGIN

SELECT department_id
INTO L_DEPT_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME=L_DEPT_NAME;



FOR C1 IN CHECK_1
LOOP
UPDATE employees_test_1 BRD
SET BRD.COMMENTS_RESTORE=columns_updated
WHERE BRD.first_name=C1.first_name
AND BRD.salary=C1.salary
AND BRD.last_name=C1.last_name;
COMMIT;
END LOOP;
END;

Hi I'm updating only one column in this query. But it is taking a lot of time. Can you please suggest where i can tune-this query.

Upvotes: 0

Views: 376

Answers (1)

guildsbounty
guildsbounty

Reputation: 3361

You don't need bulk collect here, just a subquery with a JOIN.

UPDATE employees_test_1 BRD
SET BRD.COMMENTS_RESTORE=columns_updated 
WHERE EXISTS (
    SELECT 1
    FROM employees_test_1 ET1
    INNER JOIN DEPARTMENTS D ON ET1.department_id = ET1.department_id
    WHERE D.DEPARTMENT_NAME = L_DEPT_NAME
    AND ET1.first_name = BRD.first_name
    AND ET1.salary = BRD.salary
    AND ET1.last_name = BRD.last_name
)

You are still doing a heap of string compares, and have a self-referencing sub-query, so this is going to be a time-intensive operation...but this should be a bit quicker than a cursor.

EDIT: Adding in the alternate way of doing this, via BULK operations. This assumes you have a Primary Key column in that employees_test_1 table

DECLARE
TYPE employee_block IS RECORD (first_name VARCHAR2(50), salary NUMBER, last_name VARCHAR2(50);
TYPE employee_ids_t IS TABLE OF employee_block
l_employees   employee_ids_t;

SELECT et.first_name, et.salary, et.last_name
BULK COLLECT INTO l_employees
FROM employees_test_1 et
INNER JOIN DEPARTMENTS D ON D.department_id = et.department_id
WHERE D.DEPARTMENT_NAME = L_DEPT_NAME
AND salary > 1000;

FORALL indx IN 1..l_employees.COUNT
UPDATE employees_test_1 BRD
SET BRD.COMMENTS_RESTORE = columns_updated
WHERE BRD.first_name = l_employees(indx).first_name
AND BRD.salary = l_employees(indx).salary
AND BRD.last_name = l_employees(indx).last_name;

Haven't tested this, so the syntax may be a touch off...here's a good reference to help you out. http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Upvotes: 3

Related Questions