Home / datingwifes / Updating plsql

Updating plsql

The performance improvement of using the ROWID is illustrated in the following example.The example retrieves each of the 25,000 employee records, calculates a new salary for each employee, and then updates the employees’ salary.The actual salary calculation is not shown in this example.The first PL/SQL code segment shows the timing results with the UPDATE using the EMPLOYEE_ID column, which has a unique index on the column: The following output shows the timing of two executions of the preceding code segment: In the following procedure, the same functionality is maintained while changing the UPDATE to perform the UPDATE based on the ROWID.The ROWID variable can improve PL/SQL programs that retrieve records from the database, perform manipulation on the column values, and then complete with an UPDATE to the retrieved record.

Such as in life, in technology the only guarantee is that there are no guarantees.With the addition of the use of the FORALL statement, this greatly reduces the amount of context switching between PL/SQL and SQL by sending DML statements from PL/SQL to SQL in batches rather than one at a time, resulting in dramatic performance improvements over row-by-row processing. Incidentally, the above query returns 999,000 rows.The first thing we’ll need to do is write a SQL query that returns all of the data we need to update every row in the EMPLOYEES table that has a matching record(s) in the the NEW_SALARY_INFO table, keeping in mind that we need the maximum salary. DECLARE 2 CURSOR c_new_salary_info IS 3 SELECT e.employee_id employee_id, 4 MAX(nsi.salary) new_salary 5 FROM employees e, 6 new_salary_info nsi 7 WHERE e.employee_id = nsi.employee_id 8 GROUP BY e.employee_id; 9 10 TYPE t_new_salary_info IS TABLE OF c_new_salary_info%rowtype 11 INDEX BY PLS_INTEGER; 12 lt_new_salary_info t_new_salary_info; 13 14 v_rowcount PLS_INTEGER := 0; 15 BEGIN 16 OPEN c_new_salary_info; 17 18 LOOP 19 FETCH c_new_salary_info BULK COLLECT 20 INTO lt_new_salary_info LIMIT 1000; 21 22 EXIT WHEN lt_new_salary_info.SQL courses meet the most demanding needs of the business world for advanced education in a cost-effective manner.SQL courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.This involves adding the ROWID in the SELECT statement and changing the UPDATE predicate clause.The following output shows the timing of two executions of the preceding code segment: As evidenced from the timings, execution is faster using the ROWID.If no record exists in the NEW_SALARY_INFO table for an employee, that employee’s data should remain the same. We’ll just use an UPDATE statement to modify the rows in the EMPLOYEES table: UPDATE employees e SET employee_salary = ( SELECT MAX(salary) FROM new_salary_info nsi WHERE nsi.employee_id = e.employee_id ) WHERE EXISTS ( SELECT NULL FROM new_salary_info nsi2 WHERE nsi2.employee_id = e.employee_id ); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Temp Spc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 999K| 17M| | 2772M (2)| 9| | 1 | UPDATE | EMPLOYEES | | | | | | |* 2 | HASH JOIN SEMI | | 999K| 17M| 23M| 7887 (1)| | | 3 | TABLE ACCESS FULL| EMPLOYEES | 1000K| 12M| | 690 (2)| | | 4 | TABLE ACCESS FULL| NEW_SALARY_INFO| 3996K| 19M| | 2764 (2)| | | 5 | SORT AGGREGATE | | 1 | 13 | | | | |* 6 | TABLE ACCESS FULL| NEW_SALARY_INFO| 4 | 52 | | 2772 (2)| | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NSI2"."EMPLOYEE_ID"="E"."EMPLOYEE_ID") 6 - filter("NSI"."EMPLOYEE_ID"=: B1) Hmm, that doesn’t look very promising at all. Well, SQL is pretty awesome, so there must be another way to do this. UPDATE ( SELECT e.employee_id, e.employee_salary, NVL(( SELECT MAX(nsi.salary) FROM new_salary_info nsi WHERE e.employee_id = nsi.employee_id ), e.employee_salary) AS new_sal FROM employees e ) SET employee_salary = new_sal; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1000K| 12M| 2772M (2)|9 | | 1 | UPDATE | EMPLOYEES | | | | | | | 2 | TABLE ACCESS FULL | EMPLOYEES | 1000K| 12M| 690 (2)| | | 3 | SORT AGGREGATE | | 1 | 13 | | | |* 4 | TABLE ACCESS FULL| NEW_SALARY_INFO | 4 | 52 | 2772 (2)| | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("NSI"."EMPLOYEE_ID"=: B1)MERGE INTO employees e USING ( SELECT e.rowid AS e_rowid, MAX(nsi.salary) AS new_sal FROM employees e, new_salary_info nsi WHERE e.employee_id = nsi.employee_id GROUP BY e.rowid ) new_salaries ON (e.rowid = new_salaries.e_rowid) WHEN MATCHED THEN UPDATE SET e.employee_salary = new_salaries.new_sal; Curses! It turns out there is a VPD security policy on the EMPLOYEES table that prevents us from using the MERGE. All of our attempts at using SQL have been stymied. By making use of bulk processing in PL/SQL we can perform our task rather quickly and with minimal code.The Oracle bulk collect tells the SQL engine to fetch multiple rows at once and store them in a collection defined by the developer. Now we can turn that query into a PL/SQL cursor, process the results in bulk, and perform the required update using a FORALL statement.If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using PL/SQL and SQL both are incredibly powerful languages that allow us to do some pretty amazing things with data.If you’ve been around the Oracle community for some time you’ve probably realized, either through your own experience or by reading articles, if you are able to accomplish a task using a single SQL statement then that is preferred over using procedural PL/SQL code that would run slower.

96 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*