Oracle 23c: Managing Flashback Database Logs Outside the Fast Recovery Area
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Learn more about why Eclipsys has been certified as a Great Place to Work in Canada, Best Workplaces in Ontario and Technology, and named Canada’s Top SME Employer for 3 years!
Learn more!In this blog, I’ll test executing UPDATE and DELETE commands with direct joins using the HR schema.
Prerequisites:
Use-Case: Increase the salaries of the Finance department by 5%.
1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.
SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary FROM employees e, departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
2. Writing UPDATE in pre-23c Oracle database using sub-query in the WHERE clause.
UPDATE employees e SET e.salary=e.salary*1.05 WHERE e.department_id in ( SELECT department_id FROM departments WHERE department_name='Finance');
3. Write UPDATE in Oracle 23c database using direct join then query the tables.
When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access the EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.
explain plan for UPDATE employees e SET e.salary=e.salary*1.05 FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
UPDATE employees e SET e.salary=e.salary*1.05 FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
Use-Case: delete the employees of the Finance department.
1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.
SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary FROM employees e, departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
2. Writing DELETE in pre-23c Oracle database using sub-query in the WHERE clause.
DELETE employees e WHERE e.department_id in ( SELECT department_id FROM departments WHERE department_name='Finance');
3. Writing DELETE in Oracle 23c database using direct join then query the tables.
When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access the EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.
DELETE employees e FROM departments d WHERE e.department_id=d.department_id AND d.department_name='Finance';
Overview: In previous Database releases, you could store flashback database logs only in the fast recovery area Oracle 23c Database introduces the ... Read More
Overview: Oracle 23c Database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the ... Read More